Splunk Enterprise Security

In Splunk Enterprise Security, how do you combine two searches into one query and group on a specific field?

iomega311
Explorer

I am trying to create a query where there are two different searches that each produce a point in time for each device/host. Once done, I can then subtract one time from the other to produce a difference in time between events.

So, the scenario is that I am looking for a device where logging was stopped, then started, and I want to see the downtime between events.

The first query I have (using Splunk Enterprise Security) looks like this:

| from datamodel:"Change"."Auditing_Changes"
| where ('action'="cleared" OR 'action'="stopped") 
| stats max(_time) as "lastTime",count by "dest"

It produces something like this:

dest          lastTime          count
hostA         1550497588        1
hostB         1550482203        1

The second query looks like this:

source="WinEventLog:System" sourcetype=WinEventLog EventCode=6005
| stats max(_time) as "lastTime",count by "dest"

It produces something like this:

dest          lastTime          count
hostA         1550497641        1
hostB         1550482248        1

How do I complete both queries together so I can have each host grouped together with the "lastTime" from each search... and then subtract one from the other?

I would like the output to look something like this:

dest          lastTime1          lastTime2          count
hostA         1550497641         1550497588         1
hostB         1550482248         1550482203         1

That way, I can do an eval for the difference between "lastTime1" and "lastTime2"... and see the amount of time elapsed between the logging shutdown and logging startup.

Any help provided in helping me figure out how to put two separate queries together and grouping/binding by the host would be awesome and appreciated.

Thanks!

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

source="WinEventLog:System" sourcetype=WinEventLog EventCode=6005
| stats max(_time) as "lastTime1",count by "dest"
| append [| from datamodel:"Change"."Auditing_Changes"
| where ('action'="cleared" OR 'action'="stopped") 
| stats max(_time) as "lastTime2",count by "dest"]
| stats values(*) as * by dest 
| eval diff=lastTime1-lastTime2

View solution in original post

0 Karma

somesoni2
Revered Legend

Give this a try

source="WinEventLog:System" sourcetype=WinEventLog EventCode=6005
| stats max(_time) as "lastTime1",count by "dest"
| append [| from datamodel:"Change"."Auditing_Changes"
| where ('action'="cleared" OR 'action'="stopped") 
| stats max(_time) as "lastTime2",count by "dest"]
| stats values(*) as * by dest 
| eval diff=lastTime1-lastTime2
0 Karma

iomega311
Explorer

Thank you so much! I toyed around with this for a while and eventually just came here.
I just needed to add a table at the end and it came out perfect. Thanks again!

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Unlock What’s Next: The Splunk Cloud Platform at .conf25

In just a few days, Boston will be buzzing as the Splunk team and thousands of community members come together ...