Change search query according to time in dashboard?

[gurkiratsingh]

I want to change my search query according to the time of the day

<query>index=--- application=------ |search abc=1
</query>

So in my dashboard I want the query to change acc to the time of the day. Like for example
from 12:00am to 1:30am
|search abc=1
from 1:30am to3:00am
|search abc=2
& so on
Please help guys!!!

[woodcock]

Like this:

... | eval hourmin=strftime(now(), "%H%M")
| eval ABC=case(
   hourmin<= 130, "1"
   hourmin<= 330, "2"
   hourmin<= 530, "3",
   true(), "4")
| where abc=ABC

[wmyersas]

That won't work because you have a colon in the time formatting

And %H gives you the hour with leading 0 in 24hr format (eg 01 vs 13)

but this does:

| eval when=tonumber(ltrim(strftime(now(),"%H%M"),"0"))

(tonumber() may not be required)

[woodcock]

You are correct about the colon; I updated my answer. Leading zeroes make no difference and are fine.

[gurkiratsingh]

Hi I tried using where but still I am not getting any results.The value from the CASE is getting extracted correctly and when I use ( |search abc=7) then the results are coming but not when I use ( |search abc='filter) or (|search abc=filter) or (|where abc='filter') or (|where abc=filter).

[wmyersas · Accepted solution]

Based on you wanting this to Just Work™, take a look at using a case() statement.

Here's a sample with the two ranges done:

| makeresults
| eval h=strftime(_time,"%H"), mr=strftime(_time,"%m")
| eval filter=case((h=16 OR (h=17 AND m<31)),"1",(h=18 OR (h=17 AND m>30)),"2",1=1,"you forgot to fill-in a range")

This breaks down as the following:

• if the hour is 16 (4p), or it's 17 (5p) and less than or equal to half-past, return 1
• if the hour is 18 (6p), or it's 17 (5p) and after half-past, return 2
• the 1=1... segment is the default case: ie, if you missed a range, you'll get the 'error message'

Once you calculate your filter, use it in your sample search thusly:

<search>
| <filter logic using case statement>
| where like(abc,'filter')
<rest of search>

Adjust and extend as desired

edited to change from | search abc='filter' to | where like(abc,'filter')

[gurkiratsingh]

Hi I made the case expression as you told but when I am using it in a search it is showing no result found. Can the Returned Value From a Case Function be used in a Search? The case function that I made is working fine.

[wmyersas]

"Can the Returned Value From a Case Function be used in a Search"

Sure - so long as it's something in your data: that was why I used the example line of | search abc='filter'

Whatever the case() returns goes in the field name you've given (in my example, it's filter)

You then use single quotes around it when searching, so you get the value of the field, and not the literal text of whatever you've named the field (eg "filter")

[gurkiratsingh]

Hi even on doing the above mentioned still I am not getting any result. The case function is working perfectly fine. For example when I am doing (|search abc=7) I am getting the results but when I use it through the case function (|search abc='filter') then I am not getting any results. The value of filter is getting extracted correctly.

[wmyersas]

try | where like(abc,'filter') instead of | search abc='filter'

(I updated my answer, too)

[gurkiratsingh]

Hi this has worked correctly but now I need to do another thing. Suppose abc is a message string which contains certain numbers acc to the current time. For example:
Filename : (*****0000**)

|eval filter=case(
( (IST_time_hour=23 AND IST_time_min >= 00) OR (IST_time_hour=00 AND IST_time_min <30) ),0000
,( (IST_time_hour=00 AND IST_time_min >= 30) OR (IST_time_hour=01 AND IST_time_min <59) ),0130
,( (IST_time_hour=02 AND IST_time_min >= 00) OR (IST_time_hour=03 AND IST_time_min <30) ),0300
,( (IST_time_hour=03 AND IST_time_min >= 30) OR (IST_time_hour=04 AND IST_time_min <59) ),0430
,( (IST_time_hour=05 AND IST_time_min >= 00) OR (IST_time_hour=06 AND IST_time_min <30) ),0600
,( (IST_time_hour=06 AND IST_time_min >= 30) OR (IST_time_hour=07 AND IST_time_min <59) ),0730
,( (IST_time_hour=08 AND IST_time_min >= 00) OR (IST_time_hour=09 AND IST_time_min <30) ),0900
,( (IST_time_hour=09 AND IST_time_min >= 30) OR (IST_time_hour=10 AND IST_time_min <59) ),1030
,( (IST_time_hour=11 AND IST_time_min >= 00) OR (IST_time_hour=12 AND IST_time_min <30) ),1200
,( (IST_time_hour=12 AND IST_time_min >= 30) OR (IST_time_hour=13 AND IST_time_min <59) ),1330
,( (IST_time_hour=14 AND IST_time_min >= 00) OR (IST_time_hour=15 AND IST_time_min <30) ),1500
,( (IST_time_hour=15 AND IST_time_min >= 30) OR (IST_time_hour=16 AND IST_time_min <59) ),1630
,( (IST_time_hour=17 AND IST_time_min >= 00) OR (IST_time_hour=18 AND IST_time_min <30) ),1800
,( (IST_time_hour=18 AND IST_time_min >= 30) OR (IST_time_hour=19 AND IST_time_min <59) ),1930
,( (IST_time_hour=20 AND IST_time_min >= 00) OR (IST_time_hour=21 AND IST_time_min <30) ),2100
,( (IST_time_hour=21 AND IST_time_min >= 30) OR (IST_time_hour=22 AND IST_time_min <59) ),2230
)
|where LIKE('Filename','filter')
Now I need this where to search whether the filter value lies in the Filename. Again really thanks for your help!!!
What I have tried and it didnt work
|where LIKE('message.FileName','*filter')
|where LIKE('message.FileName','%filter%')

[wmyersas]

Use match:

| makeresults
| eval fname="0000blahblah.blah"
| eval filter="0000"
| eval match=if(match(fname,filter),1,0)
| table fname filter match

[gurkiratsingh]

Thanks guys now my query is resolved:-))
Could you also help me a little more:
(https://answers.splunk.com/answers/814817/splunk-search-basic-queries.html)

[woodcock]

Try my answer.

[gurkiratsingh]

Hi this works perfectly now. Thanks a lot :-)) Btw can you also help me on another thing:
(https://answers.splunk.com/answers/810952/how-to-set-time-to-search.html)

[wmyersas]

with a dropdown or set of radio buttons, this would be pretty straightforward

are you wanting the search to "automagic itself", or to have user-selectable options?

[gurkiratsingh]

Hi I want it to change automatically with no use of dropdown & no user selectable option.