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

[iomega311]

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!

[somesoni2 · Accepted solution]

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

[iomega311]

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!