Splunk Search

How to find the duration between two EventCode for Windows Log.

gouravdashtcs
Loves-to-Learn

Hello Everyone,

I want to calculate the downtime for a particular server based on the difference between two EventCode which are Windows Event Logs.
Description of EventCode Below:

EventCode 1074: This EventCode is generated when the user tries to shutdown the server remotely.
EventCode 6013: This EventCode is generated when the server starts after successful reboot. Also this EventCode is generated on a daily basis at 12 AM which calculates the Server uptime in seconds.

Below is the sample sequence in which the log is pushed to splunk.

Scenario 1:
Sl No. Time EventCode
1 5/7/19 0:00 6013
2 5/7/19 10:05 6013
3 5/7/19 10:00 1074
4 5/7/19 0:00 6013
5 5/6/19 0:00 6013
6 5/5/19 0:00 6013
In the above case the user tried to reboot the server remotely which generated 1074 EventCode and after 5mins the server rebooted properly and EventCode 6013 generated. So using the transaction command we can find the duration of the downtime easily in this case.

Scenario 2:
Sl No. Time EventCode
1 5/7/19 0:00 6013
2 5/7/19 10:05 6013
3 5/7/19 10:00 1074
4 5/7/19 9:00 1074
5 5/7/19 0:00 6013
6 5/6/19 0:00 6013
7 5/5/19 0:00 6013
In this case user tried to reboot the server remotely at 9AM, but it didn't happen properly so user again tried to reboot the server at 10 AM. So in this case EventCode 1074 occured 2 times.
When I'm using the transaction commant in this case. It is grouping Sl No 4 and Sl No 2 AND it is also grouping Sl No 3 and Sl No 1, which should not be the case.

My requirement is it should take the first occurrence of EventCode 1074 and after that first occurrence of EventCode 6013 to calculate the proper downtime.. In the above case it should be Sl No 4 and Sl No 2.

Please fine the query I'm using for your reference:

index =abc sourcetype="WinEventLog:System"
|transaction host startswith="1074" endswith="6013"
|eval DurationMin = round(duration/60,2)
|stats list(_time) as Date list(DurationMin) as Dur_Min by host, DurationMin
|stats sum(DurationMin) as Total_Downtime_Duration_Min list(Date) as Downtime_Date list(Dur_Min) as Downtime_Min by host
|convert timeformat="%Y-%b-%d" ctime(Downtime_Date)
|rename host as Server_Name
|table Server_Name, Downtime_Date, Downtime_Min, Total_Downtime_Duration_Min

Appreciate your patience and response.

0 Karma
1 Solution

adonio
Ultra Champion

Hello there,

many ways to go about that one.
here is an example with streamstats
i modified the month of your first example to fit both scenarios in one search
try this search anywhere:

 | makeresults count=1
    | eval data = "1 4/7/19 0:00 6013;;;2 4/7/19 10:05 6013;;;3 4/7/19 10:00 1074;;;4 4/7/19 0:00 6013;;;5 4/6/19 0:00 6013;;;6 4/5/19 0:00 6013;;;7 5/7/19 0:00 6013;;;8 5/7/19 10:05 6013;;;9 5/7/19 10:00 1074;;;10 5/7/19 9:00 1074;;;11 5/7/19 0:00 6013;;;12 5/6/19 0:00 6013;;;13 5/5/19 0:00 6013"
    | makemv delim=";;;" data 
    | mvexpand data
    | rex field=data "(?<SN>\d+)\s(?<time>\d+/\d+/\d+\s\d+\:\d+)\s(?<EventCode>\d+)"
    | eval _time = strptime(time, "%m/%d/%y %H:%M")
    | rename COMMENT as "the above generates data below is the solution" 
    | sort 0 _time
    | streamstats current=f last(EventCode) as Previous_Code last(_time) as Previous_Time
    | where EventCode=6013 AND Previous_Code=1074
    | eval duration = _time - Previous_Time

screenshot below:

alt text

hope it helps

View solution in original post

0 Karma

adonio
Ultra Champion

Hello there,

many ways to go about that one.
here is an example with streamstats
i modified the month of your first example to fit both scenarios in one search
try this search anywhere:

 | makeresults count=1
    | eval data = "1 4/7/19 0:00 6013;;;2 4/7/19 10:05 6013;;;3 4/7/19 10:00 1074;;;4 4/7/19 0:00 6013;;;5 4/6/19 0:00 6013;;;6 4/5/19 0:00 6013;;;7 5/7/19 0:00 6013;;;8 5/7/19 10:05 6013;;;9 5/7/19 10:00 1074;;;10 5/7/19 9:00 1074;;;11 5/7/19 0:00 6013;;;12 5/6/19 0:00 6013;;;13 5/5/19 0:00 6013"
    | makemv delim=";;;" data 
    | mvexpand data
    | rex field=data "(?<SN>\d+)\s(?<time>\d+/\d+/\d+\s\d+\:\d+)\s(?<EventCode>\d+)"
    | eval _time = strptime(time, "%m/%d/%y %H:%M")
    | rename COMMENT as "the above generates data below is the solution" 
    | sort 0 _time
    | streamstats current=f last(EventCode) as Previous_Code last(_time) as Previous_Time
    | where EventCode=6013 AND Previous_Code=1074
    | eval duration = _time - Previous_Time

screenshot below:

alt text

hope it helps

0 Karma

gouravdashtcs
Loves-to-Learn

Perfect... Works for me.

It would also be great if you could let me know about the other options to achieve the result. It surely will enhance my knowledge.

0 Karma

gouravdashtcs
Loves-to-Learn

Hello @adonio

Kindly tell me some other ways to reach the conslusion.... The above example works fine but giving me few duplicates due to which the downtime duration is coming as different...

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...