Splunk Search

Trouble creating a search to return other events that happened within a time period of a subsearch

aaronnash
Engager

I'm trying to write a query that search for a users ID, shows what buildings they have accessed and who else has accessed the building in the last 60 minutes. I've got the first two parts working but I'm struggling to implement the time portion of the query. I've scoured the doco, tried evals and transactions but I'm missing something and just can't narrow my search down any further. It just keeps returning all the results in the time range I specified (24hrs).

index=security sourcetype="sec:doorlogs" 
[search Who="Person A" AND Status="Success" | fields  Status Location _time ]
| table Location Status Who _time
| sort -_time

This produces the following;

Location      Status     Who         _time
Building A  Success Person 6    2020-03-19T17:30:42.000+1000
Building A  Success Person 5    2020-03-19T15:57:01.000+1000
Building A  Success Person 4    2020-03-19T15:38:09.000+1000
Building A  Success Person 3    2020-03-19T13:49:59.000+1000
Building B  Success Person 2    2020-03-19T12:48:22.000+1000
Building A  Success Person A    2020-03-19T12:50:08.000+1000**
Building B  Success Person 2    2020-03-19T12:38:45.000+1000
Building A  Success Person 7    2020-03-19T12:36:57.000+1000
Building B  Success Person 8    2020-03-19T11:50:56.000+1000
Building B  Success Person A    2020-03-19T11:39:05.000+1000**
Building A  Success Person 9    2020-03-19T10:39:02.000+1000
Building A  Success Person 10   2020-03-19T09:30:55.000+1000
Building A  Success Person 1    2020-03-19T08:38:35.000+1000

I'm trying to achieve;

Location      Status     Who         _time
Building A  Success Person 3    2020-03-19T13:49:59.000+1000
Building A  Success Person A    2020-03-19T12:50:08.000+1000**
Building A  Success Person 7    2020-03-19T12:36:57.000+1000
Building B  Success Person 8    2020-03-19T11:50:56.000+1000
Building B  Success Person A    2020-03-19T11:39:05.000+1000**
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults 
| eval _raw=" Location      Status     Who         _time
 Building A    Success    Person 6    2020-03-19T17:30:42.000+1000
 Building A    Success    Person 5    2020-03-19T15:57:01.000+1000
 Building A    Success    Person 4    2020-03-19T15:38:09.000+1000
 Building A    Success    Person 3    2020-03-19T13:49:59.000+1000
 Building B    Success    Person 2    2020-03-19T12:48:22.000+1000
 Building A    Success    Person A    2020-03-19T12:50:08.000+1000
 Building B    Success    Person 2    2020-03-19T12:38:45.000+1000
 Building A    Success    Person 7    2020-03-19T12:36:57.000+1000
 Building B    Success    Person 8    2020-03-19T11:50:56.000+1000
 Building B    Success    Person A    2020-03-19T11:39:05.000+1000
 Building A    Success    Person 9    2020-03-19T10:39:02.000+1000
 Building A    Success    Person 10   2020-03-19T09:30:55.000+1000
 Building A    Success    Person 1    2020-03-19T08:38:35.000+1000" 
| multikv forceheader=1 
| eval _time=strptime(time,"%FT%T.%3Q%z") 
| table Location Status Who _time 
| sort _time 
| streamstats count(eval(Who="Person A")) as session by Location Who 
| eventstats min(eval(if(session=1,_time,NULL))) as earliest_t, max(eval(if(session=1,_time,NULL))) as latest_t 
| where earliest_t <= _time AND _time <= latest_t 
| table Location Status Who _time

View solution in original post

to4kawa
Ultra Champion
| makeresults 
| eval _raw=" Location      Status     Who         _time
 Building A    Success    Person 6    2020-03-19T17:30:42.000+1000
 Building A    Success    Person 5    2020-03-19T15:57:01.000+1000
 Building A    Success    Person 4    2020-03-19T15:38:09.000+1000
 Building A    Success    Person 3    2020-03-19T13:49:59.000+1000
 Building B    Success    Person 2    2020-03-19T12:48:22.000+1000
 Building A    Success    Person A    2020-03-19T12:50:08.000+1000
 Building B    Success    Person 2    2020-03-19T12:38:45.000+1000
 Building A    Success    Person 7    2020-03-19T12:36:57.000+1000
 Building B    Success    Person 8    2020-03-19T11:50:56.000+1000
 Building B    Success    Person A    2020-03-19T11:39:05.000+1000
 Building A    Success    Person 9    2020-03-19T10:39:02.000+1000
 Building A    Success    Person 10   2020-03-19T09:30:55.000+1000
 Building A    Success    Person 1    2020-03-19T08:38:35.000+1000" 
| multikv forceheader=1 
| eval _time=strptime(time,"%FT%T.%3Q%z") 
| table Location Status Who _time 
| sort _time 
| streamstats count(eval(Who="Person A")) as session by Location Who 
| eventstats min(eval(if(session=1,_time,NULL))) as earliest_t, max(eval(if(session=1,_time,NULL))) as latest_t 
| where earliest_t <= _time AND _time <= latest_t 
| table Location Status Who _time

aaronnash
Engager

Perfect, thank you!

0 Karma

to4kawa
Ultra Champion

use earliest and latest

0 Karma

aaronnash
Engager

Hi,

I've tried that but it seems to be applying it to the entire result set and not +-1hr to Person A

index=security sourcetype="sec:doorlogs" 
[search Who="Person A" AND Status="Success" 
| eval search = "_time=" . _time
| eval earliest=-h, latest=_time+h
| fields  Status Location _time ]
| table Location Status Who _time
| sort -_time
0 Karma

to4kawa
Ultra Champion
0 Karma
Get Updates on the Splunk Community!

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...