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**
| 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
| 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
Perfect, thank you!
use earliest
and latest
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