topic Re: How to include timestamp of most recent event in a comparison between current day and a prior period in Splunk Search

How to include timestamp of most recent event in a comparison between current day and a prior period

djhowie — Sat, 15 Sep 2018 14:04:46 GMT

I have a search that compares the number of events for the current day, for a given combination of fields, to the daily average over the prior two weeks. The search is pretty involved and is included below for reference. The search works as intended. There may be ways to simplify the search and I'm open to such feedback, but simplifying the search is not my question. My question is...

How can I add the timestamp of the most recent occurrence for the combination of fields?

I've included two screenshots. The first screenshot shows the current results and the second screenshot illustrates the desired results.

Any advice is appreciated. Thanks

sourcetype=xxxxxxxx earliest=@w6-14d latest=@w6
| eval Prior_or_Current="Prior"
| append [search sourcetype=xxxxxxxx earliest=@d
| eval Prior_or_Current="Current"]
| eval dayOfWeek=strftime(_time, "%A")
| eval hourOfDay=strftime(_time, "%H")
| search NOT ((dayOfWeek="Friday" AND hourOfDay>=20) OR (dayOfWeek="saturday" AND hourOfDay<5) OR (dayOfWeek="sunday" AND (hourOfDay>=0 AND hourOfDay<5)))
| eval Event_Date=strftime(_time, "%F")
| rex "msg\=\'(?<msg_first22char>(.{22}))"
| eval Combined_Key=err_transaction_id.",".err_program.",".msg_first22char
| stats count by Event_Date Combined_Key Prior_or_Current
| chart avg(count) over Combined_Key by Prior_or_Current
| eval Prior=if(Prior>"",Prior,0)
| eval Current=if(Current>"",Current,0)
| eval Percent_Change=(Current-Prior)/Prior*100
| eval Percent_Change=if(Percent_Change>"",Percent_Change,if(Prior>0,-100,100))
| eval Percent_Change=round(Percent_Change,0), Prior=round(Prior,0), Current=round(Current,0)
| rename Prior as Prior_Period_Daily_Avg Current as Current_Day_Count
| eval Sort_To_The_Top=if((Prior_Period_Daily_Avg=0 OR (Percent_Change>=50 AND Prior_Period_Daily_Avg>=200) OR (Percent_Change>=100 AND Prior_Period_Daily_Avg>=50) OR (Percent_Change>=500 AND Prior_Period_Daily_Avg>=10) OR Percent_Change>=1000), 0, if(Percent_Change>-1,1,2))
| search Sort_To_The_Top=0
| rex field=Combined_Key "(?<err_transaction_id>[\w|\W]+),(?<err_program>[\w|\W]+),(?<msg_first22chars>[\w|\W]+)"
| rex mode=sed field="err_transaction_id" "s/\'//g" 
| rex mode=sed field="err_program" "s/\'//g"
| sort Sort_To_The_Top -Current_Day_Count -Percent_Change -Prior_Period_Daily_Avg err_transaction_id err_program msg_first22chars
| table err_transaction_id err_program msg_first22chars Prior_Period_Daily_Avg Current_Day_Count Percent_Change

Current Results

Desired Results

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

DEAD_BEEF — Sun, 16 Sep 2018 02:21:58 GMT

Try this

| stats latest(_time) AS Latest_Occurrence
| table Latest_Occurrence

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

djhowie — Sun, 16 Sep 2018 13:56:10 GMT

Thanks for the reply. I understand that the stats command can be used to compute the latest timestamp. The problem is figuring out how to work it into the overall search. I tried inserting the stats, along with tabling all fields referenced in the subsequent portions of the search, but it doesn't work. I get zero results. See lines 9 and 10. Thanks

sourcetype=xxxxxxxx earliest=@w6-14d latest=@w6
| eval Prior_or_Current="Prior"
| append [search sourcetype=xxxxxxxx earliest=@d
| eval Prior_or_Current="Current"]
| eval dayOfWeek=strftime(_time, "%A")
| eval hourOfDay=strftime(_time, "%H")
| search NOT ((dayOfWeek="Friday" AND hourOfDay>=20) OR (dayOfWeek="saturday" AND hourOfDay<5) OR (dayOfWeek="sunday" AND (hourOfDay>=0 AND hourOfDay<5)))
| eval Event_Date=strftime(_time, "%F")
| stats latest(_time) AS Latest_Occurrence
| table err_transaction_id err_program msg Prior_or_Current Event_Date Latest_Occurrence
| rex "msg\=\'(?<msg_first22char>(.{22}))"
| eval Combined_Key=err_transaction_id.",".err_program.",".msg_first22char
| stats count by Event_Date Combined_Key Prior_or_Current
| chart avg(count) over Combined_Key by Prior_or_Current
| eval Prior=if(Prior>"",Prior,0)
| eval Current=if(Current>"",Current,0)
| eval Percent_Change=(Current-Prior)/Prior*100
| eval Percent_Change=if(Percent_Change>"",Percent_Change,if(Prior>0,-100,100))
| eval Percent_Change=round(Percent_Change,0), Prior=round(Prior,0), Current=round(Current,0)
| rename Prior as Prior_Period_Daily_Avg Current as Current_Day_Count
| eval Sort_To_The_Top=if((Prior_Period_Daily_Avg=0 OR (Percent_Change>=50 AND Prior_Period_Daily_Avg>=200) OR (Percent_Change>=100 AND Prior_Period_Daily_Avg>=50) OR (Percent_Change>=500 AND Prior_Period_Daily_Avg>=10) OR Percent_Change>=1000), 0, if(Percent_Change>-1,1,2))
| search Sort_To_The_Top=0
| rex field=Combined_Key "(?<err_transaction_id>[\w|\W]+),(?<err_program>[\w|\W]+),(?<msg_first22chars>[\w|\W]+)"
| rex mode=sed field="err_transaction_id" "s/\'//g" 
| rex mode=sed field="err_program" "s/\'//g"
| sort Sort_To_The_Top -Current_Day_Count -Percent_Change -Prior_Period_Daily_Avg err_transaction_id err_program msg_first22chars
| table err_transaction_id err_program msg_first22chars Prior_Period_Daily_Avg Current_Day_Count Latest_Occurrence Percent_Change

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

DEAD_BEEF — Sun, 16 Sep 2018 21:18:31 GMT

What about deleting line 9 and then just including _time in your table?

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

djhowie — Mon, 17 Sep 2018 00:43:07 GMT

I have not tried that, but I don't expect that will work. The search in question does not output every event. The search computes the daily count of events, based upon a combination of selected fields, to the daily average over the prior two weeks. The intent is to include the latest timestamp for the combination of fields.

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

DEAD_BEEF — Mon, 17 Sep 2018 13:22:09 GMT

When you say the latest timestamp for the combination of fields, there must be some timestamp within the log that holds those fields. Could you eval the _time of each of those fields, compare them, and then return the latest one? It doesn't sound pretty but it may be an option.

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

djhowie — Mon, 17 Sep 2018 14:48:19 GMT

I understand how to get the latest time. The problem is I cannot figure out how to incorporate it into the overall search to achieve the desired results without breaking the existing functionality.

Re: How to include timestamp of most recent event in a comparison between current day and a prior period

yshen — Thu, 20 Aug 2020 05:48:34 GMT

How can I make Latest_Occurrence value readable. Currently, it's of the following value, for example,

1597896470

I'd like it to be for example,

2020-08-19 22:40:37

Thanks!