question

Bugmesh avatar image
Bugmesh asked

Query the audit event log and return only those events that occured between 18:00 and 06:00

I have written a query to interrogate our SQLAuditLog table : SELECT Distinct [server_instance_name],[database_name],[object_name],[session_server_Principal_name],a.[action_id],b.[name] AS 'action_name',[class_type], event_time, statement FROM [SQLAudit].[dbo].[SQLAuditLogs] a Join sys.dm_audit_actions b ON a.action_id = b.action_id WHERE (ABS(CAST( (BINARY_CHECKSUM (server_instance_name, NEWID())) as int)) % 100) < 5 AND a.action_id IN ('ALCN','ALST','ALTR','AS','AUSC','CO','CR','DR','G','LGIF','RC','RF','SN','SVSD','SVSR','VDST') AND database_name NOT IN ('master') --AND a.server_instance_name In (@server_instance_name) AND datalength(statement) > 1 This pulls back a record set for specific action_id's that we are tracking on all our servers. We added a 5% randomize (returns a random set of records that represent 5% of the total total rows in the table) because the table is about 4 million rows. The above query works fine and I use it in the report generation process, however, I want to take it a step further and target those transactions that are processed between 6:00PM and 6:00AM (which constitutes "after hours". The idea being to audit privileged users or users with elevated rights that may be accessing the DB's after hours. I have tried a couple things, like creating a "time" function and using "Between" operator. I have also used a subroutine in the select statement (which appears to work, but the event time shows up as NULL. I would appreciate your assistance if at possible. I went into it thinking it was pretty straight forward but have become frustrated
audittime2014between
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Is event_time a TIME column or a DATETIME/DATETIME2 column?
0 Likes 0 ·
Bugmesh avatar image Bugmesh commented ·
It is a datetime column
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
You could look at using `DATEPART(hour, )` and using that to check for the appropriate time...
0 Likes 0 ·
Bugmesh avatar image Bugmesh commented ·
I will look at doing that and let you know
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.