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.
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.
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
@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
What is your base search (The part that begins with index=
); the one that works for just one value and does not use map
?
@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
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.
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).
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
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.
thanks for elaborating on your question, look at @woodcock advice / answer below