Report on Windows Security Event Log

Reporting
1

Hello,

I’m a newbie trying to wrap my head around writing these queries.

What I would like to do is run a report that returns time of an event, account name and machine name of a specific windows event id during the last 24 hours.

For example, security event log Event ID 4625 on Tues, May 2 2018 at 10:30, on computer wsk123 for user JDOE

An added bonus if I could specify a specific date I want to return results on , that would be awesome.

What I have so far is:

(time generated of it) of records whose (event id of it = 4625 AND now - time generated of it < 1*day AND description of it contains “An account failed to log on.”) of security event log

Which will list all the event times in the last 24 hours.

How can I add to the query for additional like username and machine name from the Event description “Subject: Account Name:” and “Network Information: Workstation Name:”

Ideal output would be:
Tue, 01 May 2018 23:06:05 -0700, JDOE, WSK123

Thanks in advanced for any help!

2

You can try this,

(time generated of it, user sid of it) of records whose (event id of it = 4625 AND now - time generated of it < 1*day AND description of it contains “An account failed to log on.”) of security event log

This will get you the account name as well.

As far as reporting, I created an analysis to return the results. I then use a filter to key off the results. Then report then is set to run when Send email/store archive only when report has changed is selected.

Hope this helps.

3

I gave it a try but received an error:

Q: (time generated of it, user sid of it) of records whose (event id of it = 4625 AND now - time generated of it < 1*day AND description of it contains “An account failed to log on.”) of security event log
E: Singular expression refers to nonexistent object.

4

Is it possible to use the xml of function to search and return results of the Event Log XML?

For example, pull the data for:

  • jdoe
    WSK123

5

Try this;

Q: (time generated of it, user sid of it) of records whose (event id of it = 4625 AND now - time generated of it < 1*day AND source of it contains “Microsoft Windows security auditing”) of security event log

6

It doesn’t really produce any results , just the time to process

Q: (time generated of it, user sid of it) of records whose (event id of it = 4625 AND now - time generated of it < 1*day AND source of it contains"“Microsoft Windows security auditing") of security event log
T: 42832.687 ms

7

By the looks of it, you will want to go another way if the time to crunch relevance is really taking that long.

1 Like
8

@jhickok - Agreed, I hate dealing with event log queries because they often take too long.

Dustin, I couldn’t get the formatting exactly as you requested but could you see if this gives you any results:

q: (time generated of it, (parenthesized part 2 of it & "\" & parenthesized part 1 of it & ", " & parenthesized part 3 of it) of (matches (regex "Subject:.*Account Name:%09.+(.*)%0d%0a%09Account Domain:%09%09(.*)%0d%0a%09Logon ID:%09%09.*Logon Type:.*Workstation Name:%09(.*)%0d%0a%09Source Network Address:") of description of it)) of records whose (event id of it = 4625 and description of it contains "An account failed to log on." and now - time generated of it < 1*day) of security event log
A: ( Mon, 30 Apr 2018 07:38:53 -0700 ), ( MyPC\SomeAccount, MyMachineName )

Keep in mind if your query is not producing any results make sure you do actually have some 4625 events within the previous day on the box you are testing on.

2 Likes
9

Just noticed your note about specifying the date also. Converting the time generated to a date should take care of that. The date string (in my example “30 Apr 2018”) is in the format BigFix needs the string in to convert it to a date.

q: (time generated of it, (parenthesized part 2 of it & "\" & parenthesized part 1 of it & ", " & parenthesized part 3 of it) of (matches (regex "Subject:.*Account Name:%09.+(.*)%0d%0a%09Account Domain:%09%09(.*)%0d%0a%09Logon ID:%09%09.*Logon Type:.*Workstation Name:%09(.*)%0d%0a%09Source Network Address:") of description of it)) of records whose (event id of it = 4625 and description of it contains "An account failed to log on." and (date (local time zone) of time generated of it ) = ("30 Apr 2018" as date)) of security event log

10

Return most recent event:
Q: computer name, ((month of it as two digits & “/” & day_of_month of it as two digits & “/” & year of it as string) of dates (local time zone) of times generated of it, (time of times (local time zone) of times generated of it), concatenation of substrings separated by “%0d%0a” of (preceding text of first “%0d%0a%09” of following text of first “Account Name:%09%09” of it) of (descriptions of it)) of items 1 of it whose (time generated of item 1 of it = item 0 of it) of (maximum of times generated of records whose (event id of it = 4625 and now - time generated of it < 1*day) of it, records whose (event id of it = 4625) of it) of security event log
A: WLCOMP1, ( 05/03/2018, 06:16:31, - )
T: 126865.621 ms

Return all events:
Q: computer name, ((month of it as two digits & “/” & day_of_month of it as two digits & “/” & year of it as string) of dates (local time zone) of times generated of it, (time of times (local time zone) of times generated of it), concatenation of substrings separated by “%0d%0a” of (preceding text of first “%0d%0a%09” of following text of first “Account Name:%09%09” of it) of (descriptions of it)) of records whose (event id of it = 4625 and now - time generated of it < 1*day) of (security event log)
A: WLCOMP1, ( 05/02/2018, 17:49:26, - )
A: WLCOMP1, ( 05/02/2018, 17:49:26, - )
A: WLCOMP1, ( 05/02/2018, 21:55:02, - )
A: WLCOMP1, ( 05/02/2018, 22:05:47, - )
A: WLCOMP1, ( 05/03/2018, 01:26:15, - )
A: WLCOMP1, ( 05/03/2018, 01:26:16, - )
A: WLCOMP1, ( 05/03/2018, 04:28:55, - )
A: WLCOMP1, ( 05/03/2018, 04:28:56, - )
A: WLCOMP1, ( 05/03/2018, 06:16:31, - )
A: WLCOMP1, ( 05/03/2018, 06:16:31, - )
T: 51791.201 ms

11

Be careful with long-running queries. As @AlanM has pointed out before, by default the results from Fixlet Debugger are about 80x faster than what the client is going to do (with CPU and resource throttling engaged). So that query that takes one minute in fixlet debugger might take over an hour on the native client.

12

@livy Thank you very much!
Is it possible to pull the second Account Name entry in the event log description? The second Account Name is the username for the event.

Such that: A: WLCOMP1, JDOE, ( 05/02/2018, 17:49:26, - )

Thanks Again!

13

I believe you can just change “of first” to “of last” since there are only 2 account name entries anyways

14

@livy Thanks for the suggestion but the “Return All Events” query results ended up dumping all the contents of the event details.

15

Can you confirm this? I just copy/pasted the “return all events” into my QnA tester. you need to replace the " quotations because when I copy/paste into my QnA debugger tool it errors out until I replace the quotations.