topic Re: Time difference where values are at random place in a file. in Splunk Search

Time difference where values are at random place in a file.

arjitgoswami — Tue, 23 May 2017 12:02:32 GMT

Hi All,

I need to search for time taken since a value popped up in the logs. The problem here is that this value is at random places in the logs.

So could anybody please suggest how to calculate the time till when these values were present? For instance in this example we need to search for value 'xyz' and this should give me 30 mins.

Appreciate your help.

Thanks,
Arjit.

Re: Time difference where values are at random place in a file.

adonio — Tue, 23 May 2017 13:38:06 GMT

hello there,
do you mean 30 seconds?
how would you know your first event: 22/7/2017 07:05:00 ............. ...........................................XYZ.... is not part of an older chain of XYZ?
how do you determine the time span?
i mean you can basicly search for:

index = blah XYZ | stats earliest(_time) as start, latest(_time) as stop  
| eval duration = stop-start

duration here in seconds is the gap in time between these events

Re: Time difference where values are at random place in a file.

arjitgoswami — Tue, 29 Sep 2020 14:14:00 GMT

Thanks Adonio! The issue is when we need to create a table for various such names in the logs.

Just to elaborate the situation here, we have a web application log and we need to see time taken by each Quote ID created. so the logs are something like this :

22/7/2017 07:05:00 ............. ...........................................XYZ....
22/7/2017 07:05:10 ............. ...........................XYZ....................
22/7/2017 07:05:20 ............. ...........................XYZ....................
22/7/2017 07:05:30 ............. ....................XYZ............................
22/7/2017 07:05:40 ............. ...........................................ABC....
22/7/2017 07:05:50 ............. ...........................ABC...................
22/7/2017 07:06:00 ............. ...........................ABC....................

and I need output in a table like :

Quote ID Time Taken
XYZ 30 Sec
ABC 20 Sec

If I would have had Quote ID in a particular position or in field called QuoteID, I would have used regular expression and my query would have been :

But now these values are scattered one. Could you please advise what can be done here?

Thanks for your help.

Kind regards,
Arjit.

Re: Time difference where values are at random place in a file.

woodcock — Tue, 23 May 2017 14:40:26 GMT

Like this:

Your Base Search Here
| eval QuoteID = "XYZ"
| streamstats count(eval(NOT match(_raw, QuoteID))) AS sessionID
| eval sessionID = if(match(_raw, QuoteID), sessionID, null())
| stats range(_time) AS TimeTaken BY sessionID
| table QuoteID TimeTaken

You can do this for a series of QuoteID values by extending with map. This checks only for sequential events (without gaps).

Re: Time difference where values are at random place in a file.

adonio — Tue, 23 May 2017 15:20:22 GMT

thanks for elaborating on your question, look at @woodcock advice / answer below

Re: Time difference where values are at random place in a file.

woodcock — Tue, 23 May 2017 15:43:25 GMT

For a group of pre-known QuoteID values, you can do it like this:

| makeresults 
| eval QuoteID="ABC,XYZ,etc" 
| makemv delim="," QuoteID
| map search="index=YourIndexHere sourcetype=YourSourcetypeHere *$QuoteID$*
    | stats count range(_time) AS TimeTaken 
    | eval QuoteID=\"$QuoteID$\""
| table QuoteID TimeTaken

This is limited by default to 10 values of QuoteID but that can be extended.

Re: Time difference where values are at random place in a file.

arjitgoswami — Wed, 24 May 2017 04:06:34 GMT

Thanks Woodcock and adonio for your response. But query "For a group of pre-known QuoteID values" is not giving my any results. It looks like map function itself is not working for my splunk. Could you please help?

Thanks and regards,
Arjit Goswami

Re: Time difference where values are at random place in a file.

arjitgoswami — Thu, 25 May 2017 06:09:28 GMT

@woodcock @adonio Hi Woodcock & Adonio, Just a gentle remainder on this. I gave all my quotes in QuoteID (2 of them for testing) and found that given map query is not working. It looks like some issue with map query. Could you please help ?

Thanks
Arjit

Re: Time difference where values are at random place in a file.

woodcock — Thu, 25 May 2017 19:42:31 GMT

What is your base search (The part that begins with index= ); the one that works for just one value and does not use map?

Re: Time difference where values are at random place in a file.

arjitgoswami — Fri, 26 May 2017 08:22:49 GMT

@woodcock : Hi Woodcock | Acknowledging your response here. Sorry was travelling so couldn't respond you yesterday. I am not sure whether my understanding is correct or not, but based on @niketnilay response on one other query, I tried to amend the given query and it started working fine. What I have done is that I have added count function and I have added one more "Search' command (based on map documentation) to our map query.

Please find the updated query below:

I somehow feel that Stats(values) and table functions in base search doesn't work with Map command. Only count function in base search is sending single-valued result to map command.

Thanks a lot for your help. Much appreciated !!!

Kind regards,
Arjit Goswami