How to create bins of values surround a single event

[dtaylor]

Good day,

I'm trying to think of how I can write a search to find a specific event and then take all the events surrounding that specific event within a time frame and group them for analysis. For example, assuming web traffic, say I write a search to find DNS traffic from a  host attempting to resolve a specific hostname. We'll go with google.com on computer myHost.mydomian.com

I find 20 events for the last 24/hrs , each with their own _time field for when myHost.mydomain.com attempted to resolve the hostname for google.com.

Now, I want to take the _time field for each of those events and grab specified fields from events within the past and following 10 seconds of that _time value. Doing this, I'd expect to create ~20 bins assuming the initial 20 events don't overlap, each with the rawdata or fields from the events immediately preceeding and following the DNS event.

The example aside, the issue I'm trying to solve currenly is this. I have 13 hosts who each attempted to resolve the the name for a domain at various times. I need to see what shared sites those 13 hosts were visiting  just before the DNS traffic. For example, maybe they were all on Facebook.com, and therefore I can draw a conclusion that Facebook.com was the one prompting the DNS traffic.

Currently, I'm tossing around in my head how I might use a subsearch, bin, or transaction command to do this, but I'm not sure, and I'm happy to take any advice from others on what kind of search I need to write.

[livehybrid]

Hi @dtaylor 

I think something like the following might work for you? Ive tried to recreate it using some old dns/pihole data I have:

In the screenshot you can see that there were 2 hosts which resolved google.com, and based on the earliest/latest of 60 seconds either side of those events, the 2 hosts also queried mask.icloud.com

This is the query I used,  the query is fairly obvious, the "src_ip" field is the client IP in this case. 
The return command returns the first <n> (100) src_ip/earliest/latest fields as an OR statement like this:

(src_ip="192.168.0.197" earliest="1726212774" latest="1726212894") OR (src_ip="192.168.0.226" earliest="1726213512" latest="1726213632")

That is fed into the main query as a subsearch, you then use stats to get a distinct count of src_ip and a count of src_ip by query to match the sources and queries, sort in descending order and your common domains should float to the top!

index=pihole [search index=pihole query=google.com 
| dedup src_ip 
| eval earliest=_time-60, latest=_time+60
| return 100 src_ip earliest latest]
| stats dc(src_ip) as hostCount, values(src_ip) as hosts by query

Please let me know how you get on and consider adding karma to this or any other answer if it has helped.
Regards

Will

[PickleRick]

That is one of the possible approaches. While it is relatively simple and straightforward you have to remember that subsearches are limited. In this case, especially if working on a big data set, you might hit time limits for the subsearch.

Another approach would be to list all events and mark events surrounding our points of interest. Unfortunately, since Splunk cannot look backwards, you'd have to do two passes over your results - one forward and one backwards, over reversed events. While this aproach doesn't have the subsearch limitations it might generate so many intermediate results you'll get other limits somewhere else.

Of course working with indexed fields or accelerated datamodels with the subsearch approach helps greatly in getting results fast.

[dtaylor]

Thank you both! I hadn't considered either one of those approaches. Subsearches have always been a pain point for me in trying to understand them, but the one above looks clear enough that I should be able to work it out. 

And you're right, either one of these approaches would start to struggle with big data sets, but fortunately, I can only see myself using it in finite circumstances working over a relatively small number of events like in the example.

[PickleRick]

Yes, with small and quick subsearch the subsearch approach should be more straightforward, readable and efficient. One caveat though - AFAIR you can't use this approach with tstats - it doesn't allow such multiple disjoint time ranges.