Splunk Search

Evaluating and converting dates in search query

ohlafl
Communicator

I have a graph that displays an average value per day over a week as columns. When clicking a specific column a line chart displays how this value averaged over that day. The value passed from the per day-average chart is the date that is clicked and it is then input as a token (in this case "Aug 10, 2015") in the following query:

...  date_mday=[ search index=os | eval theDay=substr("Aug 10, 2015", 4, 3) | return $theDay ] date_month=[search index=os | eval theMonth=case(substr("Aug 10, 2015", 1, 3) LIKE "%Jan%", "january", substr("Aug 10, 2015", 1, 3) LIKE "%Feb%", "febuary", substr("Aug 10, 2015", 1, 3) LIKE "%Mar%", "march", substr("Aug 10, 2015", 1, 3) LIKE "%Apr%", "april", substr("Aug 10, 2015", 1, 3) LIKE "%May%", "may", substr("Aug 10, 2015", 1, 3) LIKE "%Jun%", "june", substr("Aug 10, 2015", 1, 3) LIKE "%Jul%", "july", substr("Aug 10, 2015", 1, 3) LIKE "%Aug%", "august", substr("Aug 10, 2015", 1, 3) LIKE "%Sep%", "september", substr("Aug 10, 2015", 1, 3) LIKE "%Oct%", "october", substr("Aug 10, 2015", 1, 3) LIKE "%Nov%", "november", substr("Aug 10, 2015", 1, 3) LIKE "%Dec%", "december") | return $theMonth ] date_year=[ search index=os | eval theYear=substr("Aug 10, 2015", 8, 5) | return $theYear ] ...

As you can see the query evaluates the day and "converts" it to date numbers that are possible to use the search with. This works but of takes A LOT of time to execute, using a subsearch as an excuse to evaluate a "local" token is ugly and of course very time consuming. Is there any other way of doing this, either by adjusting the query or using a completely different method?

I should mention that installing any additional apps etcetera is for various reasons not an option, the solution needs to applicable with a vanilla Splunk instance. I also do not want to change the dateformat as I want to keep it as "human readable" as possible (lol).

Tricky!

0 Karma
1 Solution

somesoni2
Revered Legend

There are two options that you can try:-

1) Optimize the subsearch to return the result faster
Intead of using

 search index=os | eval theDay=substr("Aug 10, 2015", 4, 3) | return $theDay 

Use

| gentimes start=-1 | eval theDay=substr("Aug 10, 2015", 4, 3) | return $theDay 

2) Create a macro for the conversion. [Recommended]
Create a macro with following specifications

Name: getDatePart(2)
Definition: strftime(strptime("$date$","%b %d, %Y"),"%$datepart$")
Arguments: date,datepart

Usage:

 ...  date_mday=`getDatePart("Aug 10, 2015","d")` date_month=`getDatePart("Aug 10, 2015","B")` date_year=`getDatePart("Aug 10, 2015","Y")` ...

View solution in original post

somesoni2
Revered Legend

There are two options that you can try:-

1) Optimize the subsearch to return the result faster
Intead of using

 search index=os | eval theDay=substr("Aug 10, 2015", 4, 3) | return $theDay 

Use

| gentimes start=-1 | eval theDay=substr("Aug 10, 2015", 4, 3) | return $theDay 

2) Create a macro for the conversion. [Recommended]
Create a macro with following specifications

Name: getDatePart(2)
Definition: strftime(strptime("$date$","%b %d, %Y"),"%$datepart$")
Arguments: date,datepart

Usage:

 ...  date_mday=`getDatePart("Aug 10, 2015","d")` date_month=`getDatePart("Aug 10, 2015","B")` date_year=`getDatePart("Aug 10, 2015","Y")` ...

ohlafl
Communicator

Thank you, both solutions worked perfectly.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...