DQL for dm_audittrail

Hello Experts,

I have to get the total number of documents approved by one of our clients.I am using the following query to get the total number of record in a specific year but still getting 0 results.Could you please let me know what is going wrong with this query?

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro ' AND string_3 = 'MIS Director Approval' AND time_stamp like '%2012%';

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro ' AND string_3 = 'MIS Director Approval' AND time_stamp between date('01/01/2010') and date('31/12/2010');

Please reply....

Thank you

Alpa

Find more posts tagged with

Comments

Julien.Fontaine

Are you sure of your query ?

If you delete the time clause, do you get an answer ?

MuhammadAli

Try following

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro' AND string_3 = 'MIS Director Approval' AND time_stamp between date('01/01/2010', ‘mm/dd/yyyy’) and date('31/12/2010', ‘mm/dd/yyyy’);

Regards,

Muhammad Ali

Alpa

Thank you Muhammad Ali and Julien.....but it didnt work for me.

I tried following Juliens suggestion that didnt work as well.

Could you please help me correct my query?

Do i need to search on some more attributes ?

Please reply....

Thank you.

Julien.Fontaine

Are you sure of the event_name ?

Do you have audit entries with this event_name ?

Please post one example of the entry.

Alpa

Thank you for the reply Julien.I am not sure if the event name is correct and I dont have the access to the audit entries so cannot post u an example.The event name is given by my colleague who regularly works with this and I have to generate this report for some client.

Could you please help me with this?

Please reply...

Thank you

Julien.Fontaine

Try Select distinct (event_name) from dm_audittrail.

tjbun

select audited_obj_id from dm_audittrail where event_name like '%<The event name >%' and ...

Alpa

Thank you Julien...

The event_name is correct.

Am i missing anything in the other attributes?

Please reply...

Thank you.

Julien.Fontaine

I don't understand your question... Your query is correct so if you don't have any record for this kind of action.... Try to cut your query :

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro '

Result : ??

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro ' AND string_3 = 'MIS Director Approval';

Result : ??

select distinct (time_stamp) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro ' AND string_3 = 'MIS Director Approval' ;

Good luck !

ArunSelvaraj

Thank you for the reply Julien.I am not sure if the event name is correct and I dont have the access to the audit entries so cannot post u an example.The event name is given by my colleague who regularly works with this and I have to generate this report for some client.

You said you don't have access to the audit entries but still you can get the count of the entries.

Try the below query

select count(*) from dm_audittrail where event_name ='dm_signoff';

If you got "0" then there is no event occured or the event "dm_signoff" is not configured for auditing.

If you get the count then check with the where clauses of your initial query one by one.

Hope it helps

-Arun

ArunSelvaraj

Check the values you given in the where clause of the query.

select count(*) from dm_audittrail where event_name = 'dm_signoff' AND user_name = 'Frank Pignataro ' AND string_3 = 'MIS Director Approval' AND time_stamp between date('01/01/2010') and date('31/12/2010');

Here in the query user_name='Frank Pignataro ' having one space character at last. Check it properly or use where clause like user_name like 'Frank Pignataro%'

--Arun

Alpa

Thank Arun and Julien

Your queries helped me a ton.

There are no events recorded for string_3 and that specific user_name. I have not yet resolved the issue but atleast i could reply on the query result.

Thank you once again.

Julien.Fontaine

You're welcome.

Please mark the thread as answered.

Julien.Fontaine

Any news of your audittrail ?