Splunk Search

How do I find events that are related to previous events

johndoe23
Engager

Hi,
I have to analyse a call-centre log. Here’s a brief description if the scenario. There’s a telephone line called ‘svc606’. This line is routed to five people using round robin. However, these people can also be called directly without using ‘svc606’. Every time ‘svc606’ is called, a log entry is made. About two seconds later a second entry is made for one of the five group members who received the call.
Here’s a simplified example of the log:
1. 10:00:00.000 LineName=’svc606’ caller=… duration=…
2. 10:00:02.010 LineName=’MrX’ caller=… duration=…
3. 10:05:20.000 LineName=’MrX’ caller=… duration=…
4. 10:10:00.000 LineName=’svc606’ caller=… duration=…
5. 10:10:01.090 LineName=’MrX’ caller=… duration=…
6. 10:12:00.999 LineName=’svc606’ caller=… duration=…
7. 10:12:01.999 LineName=’MrX’ caller=… duration=…

My search result must contain event 2, 5 and 7 because these have corelated event 2 seconds earlier. It mustn’t find event 3, because this is an independent call.
I came up with this solution:
index=tk | eval time=strftime(_time,"%Y%m_%H%M%S") | search index=tk [search index=tk LineName=svc606 | eval time=strftime(relative_time(_time, "+2s"),"%Y%m_%H%M%S") | fields time ]

Basically, this is a subsearch for ‘svc606’. I than create a time field, add a two second offset and cut of the microseconds. The same without the offset is done for the outer search. This works for the example event 2, but not for 5 and 7 due the slight time offset. (Only 1 second after formatting instead of two).
I’d like to search for a time range instead for a static value. Like
_time > (svc606_time + 1.9s) AND _time < (svc606_time + 2.1s)
But how?

Regards

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=tk 
| sort 0 _time 
| eval svc606CallTime=if(LineName="svc606",_time,null())
| filldown svc606CallTime
| where _time > svc606CallTime- 1.0) AND _time < svc606CallTime+ 2.1) 

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=tk 
| sort 0 _time 
| eval svc606CallTime=if(LineName="svc606",_time,null())
| filldown svc606CallTime
| where _time > svc606CallTime- 1.0) AND _time < svc606CallTime+ 2.1) 
0 Karma

johndoe23
Engager

Hello Somesoni2,

haven't heard of 'filldown' since now. Looks like I have to rethink some of my other searches I've done so far ea well 🙂
Your idea works perfectly. Thanks a lot. I added a second field containing the callerID. It is the same for the svc606-line and the employee-line. By this I can check if it's really the right call since there is still a chance that another independent call falls into the same time range. I also can be a bit more lazy with the outer bonds of the time range.
index=tk
| sort 0 _time
| eval svc606CallTime=if(LineName="svc606",_time,null())
| eval svc606PhoneNumber=if(LineName="sv606",PhoneNumber,null())
| filldown svc606CallTime
| filldown svc606PhoneNumber
| where _time > (svc606CallTime + 1) AND _time < (svc606CallTime + 3) AND PhoneNumber = svc606PhoneNumber

Again, thanks a lot

0 Karma

niketn
Legend

@johndoe23, filldown and fillnull are commands to take care of chart's formatting while handling null value with connect and zero values respectively. The fillnull commands let you replace with 0 by default and any anything else if you choose.

You can have multiple fields for filldown in the same search

| filldown svc606CallTime  svc606PhoneNumber
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...