Audit Database Reporting

[ericdewerth]

I'm pretty new here so I am not sure if this correct place to ask these questions but please redirect me if it is not.

I am little rusty on my T-SQL for MS so I need a little assistance.

I'm trying to figure out a way to get the day split into 3 times, morning, noon and night using the [Audit].[COGIPF_USERLOGON].[TIME STAMP] column so I can track the logins throughout a day.  This will help me trend for the month/year so I can plan for  my capacity needs.

I am trying to get a SQL statement that will give me distinct user count/sum from using the COGIPF_USERLOGON table grouped by date or maybe even just the day of the month (06,07,08).



USERNAME   TIME STAMP
aa                Feb 06, 2010 12:10:58
aa                Feb 06, 2010 13:20:30
bb                Feb 07, 2010 12:20:45
aa                Feb 07, 2010 12:20:37



I want to see a Total of 1 distinct user on Feb 06 and a Total of 2 distinct users on Feb 07.


DISTINCT USER COUNT            DATE
1                                              Feb 06, 2010
2                                              Feb 07, 2010


I am using MSSQL 2005 for the Audit dB.

Can anyone help?

Thanks,
Eric

[jriley]

hi eric,

i put this together real quick.  we use oracle.  it appears to return accurate values per day.  probably can tweak to split out shifts within days i imagine.  in order to group by day, i had to format timestamp seemingly because in our environment, hh:mm:ss was not visible which was messing up the count. 

select to_char(cogipf_localtimestamp,'Mon-dd-yyyy') as timestamp,
count(distinct cogipf_username) as cnt
FROM cogipf_userlogon
group by to_char(cogipf_localtimestamp,'Mon-dd-yyyy')
order by to_char(cogipf_localtimestamp,'Mon-dd-yyyy') desc

hope this helps.
thanks,
joe