HI Everyone
I have a query will return me a table shows top users that has logon fail detail as below
query
sourcetype=WinEventLog:Security EventCode=4625
| top user_id, Description,
and return table looks like below
user_id Description count percent
user1 logonFail 121 17.741935
user2 logonFail 98 10.2544
user3 logonFail 25 6.3625
****
I want to added an column that shows the last event time of that failed log happened and now display the percent column as below
user_id Description count lastHappenTime
user1 logonFail 121 15:30
user2 logonFail 98 10:15
user3 logonFail 25 16:24
****
so this way I can tell whether the fail logon event is still happening or not. Anyone done the similar thing can give me some hit please?
Thanks in advance
Regards
Sam
Thank you for all the help!!! I got what I wanted on following query
wineventlog` sourcetype=WinEventLog:Security EventCode=4625
| fields + user_id _time Description src
| stats values(src) as "Source Computer", values(Description) as Description latest(_time) as lastHappenTime count(_time) as Total by user_id
| eval lastHappenTime=strftime(lastHappenTime,"%H:%M:%S")
| sort - "lastHappenTime"
| head 10
Thank you for all the help!!! I got what I wanted on following query
wineventlog` sourcetype=WinEventLog:Security EventCode=4625
| fields + user_id _time Description src
| stats values(src) as "Source Computer", values(Description) as Description latest(_time) as lastHappenTime count(_time) as Total by user_id
| eval lastHappenTime=strftime(lastHappenTime,"%H:%M:%S")
| sort - "lastHappenTime"
| head 10
again, a little building based on @sbbadri and @niketnilay, since you can't do a top on count, and you want the percentage:
sourcetype=WinEventLog:Security EventCode=4625 | stats count last(_time) as lastHappenTime by user_id Description|eventstats sum(count) as total|eval percent=round((count/total)*100,2)|fields user_id Description count percent lastHappenTime
sourcetype=WinEventLog:Security EventCode=4625 | stats last(_time) as lastHappenTime by user_id Description| eval lastHappenTime=strftime(lastHappenTime,"%H:%M:%S") | top user_id Description lastHappenTime
this does added the lastHappenTime column but the count is wrong (show as 1) as below
user_id Description count lastHappenTime
user1 logonFail 1 15:30
user2 logonFail 1 10:15
user3 logonFail 1 16:24
****
it make sence only one event at 1530 but I want to know when was the last event happened (in this case it is 15:30) and how many is happened in today which I expect 120 rather than 1
Regards
Sam
Just building on the @sbbadri's query, I think you just need count()
and last()
stats functions with aggregation by user_id, Description.
sourcetype=WinEventLog:Security EventCode=4625 | stats count last(_time) as lastHappenTime by user_id Description