Archive

How do I find events that are related to previous events

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

Revered Legend

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

Revered Legend

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

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!