Report that shows login failure events occurred on the previous day grouped by source user

[GIA]

Hello, I need to generate the below report, can someone help please? thank you!!

format: .csv 

List of events: authentication failure activity, user logon failure : bad password, user logon failure: bad username,

table with subset of fields: user, date/time, VendorMsgID, account, class, process name, object, subject, logMsg) grouped by user

schedule: daily

search window: -24 hours

Expiration= 30 days

[GIA]

Thank you so much. It worked!  The only problem  I am facing now is that for some reason when I use that query(earliest=-d@d latest=@d), the "user" field shows up as a dollar sign ($) instead of the name of the user. Do you know what why?

*I was asked to group it by time but would like to know how to show the first or last time of the failed login for my own knowledge. Thanks again!

[bowesmana · Accepted solution]

As to why the user is a $ sign, that would come from how the user field is being extracted from your data.

Much will depend on the data format you're using XML or otherwise and the TA you have installed to extract Windows event log data.

If you run this search in Verbose mode

index=WinEventLog* EventID=4625 earliest=-d@d latest=@d
| head 1

You will see the raw data and fields extracted for one event and on the left hand side you will see the extracted fields. If there is only a $ sign, then that's probably because the real user is not in the data - or it's not being extracted correctly.

Look at this regarding the event log

https://learn.microsoft.com/en-us/windows/security/threat-protection/auditing/event-4625

As for the first/last login time do this

index=WinEventLog* EventID=4625 earliest=-d@d latest=@d
| stats min(_time) as FirtEvent max(_time) as LastEvent count by user, _time, action, subject, message

Look at this list of aggrgation functions you can use to get information in the stats command

https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Stats#Stats_function_options

[GIA]

Thanks a lot! This was very helpful and exactly what I needed. I appreciate you sharing the documentation links as well, been reading through it. 

[GIA]

Thank you. I need to collect and generate a syslog report that shows login failure events occurred on the previous day, grouped by source user

This is all I have been able to come up with so far:   

index=wineventlog* eventid=4625 earliest=-24 | stats count by user, _time, action, subject, message

[bowesmana]

What is your data that contains the info needed to produce this report and what have you tried so far?

[GIA]

Thank you. I need to collect and generate a syslog report that shows login failure events occurred on the previous day, grouped by source user

This is all I have been able to come up with so far:   

index=WinEventLog* EventID=4625 Earliest=-24 | stats count by user, _time, action, subject, message

[bowesmana]

Your Earliest statement is wrong, if you just want yesterday, do this

index=WinEventLog* EventID=4625 earliest=-d@d latest=@d
| stats count by user, _time, action, subject, message

which will give you a list of the failed logins - do you really want to group by _time as well or do you want to show the first or last time of the failed login?