topic Re: Return last event appear time in Splunk Search

Return last event appear time

samlinsongguo — Fri, 28 Jul 2017 05:20:02 GMT

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

Re: Return last event appear time

sbbadri — Tue, 29 Sep 2020 15:04:20 GMT

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

Re: Return last event appear time

samlinsongguo — Fri, 28 Jul 2017 06:14:38 GMT

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

Re: Return last event appear time

niketn — Fri, 28 Jul 2017 08:06:35 GMT

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

Re: Return last event appear time

cmerriman — Fri, 28 Jul 2017 11:30:31 GMT

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

Re: Return last event appear time

samlinsongguo — Mon, 31 Jul 2017 00:20:45 GMT

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