Splunk Search

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

New Member

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.

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............................

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.

0 Karma
1 Solution

Esteemed Legend

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.

View solution in original post

0 Karma

New Member

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

0 Karma

New Member

@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 ?

| makeresults
1. | eval QuoteID="ABC,XYZ,etc"
2. | makemv delim="," QuoteID
3. | map search="index=YourIndexHere sourcetype=YourSourcetypeHere $QuoteID$
4. | stats count range(_time) AS TimeTaken
5. | eval QuoteID=\"$QuoteID$\""
6. | table QuoteID TimeTaken

Thanks
Arjit

0 Karma

Esteemed Legend

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

0 Karma

New Member

@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:

|makeresults
|eval QuoteID = "QI-12345,QI-67859,QI-23456,QI-57689,QI-24798"
|makemv delim="," QuoteID
|stats count by QuoteID
|map search="search sourcetype=\"Pega17052017n\" $QuoteID$
|stats count range(_time) AS TimeTaken
|eval QuoteID=\"$QuoteID$\""
|table QuoteID TimeTaken

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

0 Karma

Esteemed Legend

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.

View solution in original post

0 Karma

Esteemed Legend

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).

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

New Member

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 :

sourcetype=blah QuoteID | stats earliest(_time) as start, latest(_time) as stop by QuoteID|
2. | eval duration = stop-start|
3. |table duration QuoteID

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

Thanks for your help.

Kind regards,
Arjit.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma