Evaluating and converting dates in search query

ohlafl — Thu, 13 Aug 2015 14:40:42 GMT

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!

Re: Evaluating and converting dates in search query

somesoni2 — Thu, 13 Aug 2015 15:02:37 GMT

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

Re: Evaluating and converting dates in search query

ohlafl — Mon, 17 Aug 2015 11:24:44 GMT

Thank you, both solutions worked perfectly.

topic Re: Evaluating and converting dates in search query in Splunk Search

Evaluating and converting dates in search query

Re: Evaluating and converting dates in search query

Re: Evaluating and converting dates in search query