Splunk Search

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

arjitgoswami
Explorer

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

woodcock
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

arjitgoswami
Explorer

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

arjitgoswami
Explorer

@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

woodcock
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

arjitgoswami
Explorer

@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

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

0 Karma

woodcock
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

adonio
Ultra Champion

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

arjitgoswami
Explorer

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

adonio
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...