Home Documentum

DQL to query user activity

Hi All,

I need assistance with a DQL query that is not working. I'm trying to obtain information on how many users are accessing the system, and if the systems is being access i would need their account names. What i have below is giving me the error that you see. Please let me know if you need more information, thanks.

DQL:

select count(*) from dm_user u
where r_is_group=0
and user_state=0
and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
and user_name not like 'dm_%'
and user_name not like '%client%'
and date(today)-last_login_utc_time>150
go

Error:

Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: select count(*) from dm_user u
where r_is_group=0
and user_state=0
and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
and user_name not like 'dm_%'
and user_name not like '%client%'
and date(today)-last_login_utc_time>150
go"

Comments

  • Use the DATEDIFF function, e.g.

    DATEDIFF(day, last_login_utc, DATE(NOW)) > 150

  • @bacham3 Thanks for the response. I ran the query but got this error:

    Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: DATEDIFF"

    Any ideas what that means?

  • It means exactly what it says: a syntax error ! If you don't post your query, we can't help you.

  • @bacham3 I ran exactly what you gave me: DATEDIFF(day, last_login_utc, DATE(NOW)) > 150 . If i need to add more can you provide an example? thanks

  • That was not a complete query. What I gave you was supposed to be used instead of "date(today)-last_login_utc_time>150" in your original post.

  • @bacham3 oh sorry, i tried that as well. Here is the query and error.

    Query:
    select count(*) from dm_user u
    where r_is_group=0
    and user_state=0
    and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
    and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
    and user_name not like 'dm_%'
    and user_name not like '%client%'
    and DATEDIFF(day, last_login_utc, DATE(NOW)) > 150
    go

    Error:
    Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: select count(*) from dm_user u
    where r_is_group=0
    and user_state=0
    and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
    and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
    and user_name not like 'dm_%'
    and user_name not like '%client%'
    and DATEDIFF(day, last_login_utc, DATE(NOW)) > 150
    go"

  • last_login_utc should be last_login_utc_time

  • @bacham3 ok thanks, i got an error again.

    Query:
    select count(*) from dm_user u
    where r_is_group=0
    and user_state=0
    and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
    and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
    and user_name not like 'dm_%'
    and user_name not like '%client%'
    and DATEDIFF(day, last_login_utc_time, DATE(NOW)) > 150
    go

    Error:
    Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: select count(*) from dm_user u
    where r_is_group=0
    and user_state=0
    and exists (select 'x' from dm_group where group_name='etmf_pra_staff' and any i_all_users_names=u.user_name)
    and user_name not in ('dm_dbo','admindm','dmadmindev','admindmtest','migrationAdmin','PortalMan')
    and user_name not like 'dm_%'
    and user_name not like '%client%'
    and DATEDIFF(day, last_login_utc_time, DATE(NOW)) > 150
    go"

  • I tried it and it works for me. Where do you execute this query ? In idql ? In DA ? If DA, then remove the last line "go".

  • @bacham3 in DA and you're right i had to remove the word "go". I did that but all i got is a number: 43580.0 but no usernames or more information. Is it possible to get that info via this query by adding a few other things? Thanks again

  • Are you not familiar with SQL; DQL is very similar. The reason you only get a number is b/c this is what you have explicitly requested:

    select count(*) from dm_user

    If you want to get other attributes, then just use * or specify the attribute you are interested in:

    select * from dm_user

  • @bacham3 & @DCTM_Guru Thanks for the replies, the "select * from dm_user" option worked. I'm not that familiar with SQL but i have been exposed and i can run statements in "PLSQL Developer" app. When I'm in DA and run some DQL queries, i select the box to show the SQL statement for the queries that work. However; when i then go to PLSQL Dev I'm unable to run the SQL statement (i'm in the correct schema and table). Sorry, i know this led to another question, do you know if i need to add/remove attributes into the SQL statement that i copied from DA?

Sign In or Register to comment.