Dashboards & Visualizations

Change search query according to time in dashboard?

Explorer

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

0 Karma
1 Solution

Builder

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')

View solution in original post

0 Karma

Esteemed Legend

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
0 Karma

Builder

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)

0 Karma

Esteemed Legend

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

Explorer

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

0 Karma

Builder

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')

View solution in original post

0 Karma

Explorer

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.

0 Karma

Builder

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

0 Karma

Explorer

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.

0 Karma

Builder

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

(I updated my answer, too)

Explorer

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(
( (ISTtimehour=23 AND ISTtimemin >= 00) OR (ISTtimehour=00 AND ISTtimemin <30) ),0000
,( (IST_time_hour=00 AND IST_time_min >= 30) OR (ISTtimehour=01 AND ISTtimemin <59) ),0130
,( (IST_time_hour=02 AND IST_time_min >= 00) OR (ISTtimehour=03 AND ISTtimemin <30) ),0300
,( (IST_time_hour=03 AND IST_time_min >= 30) OR (ISTtimehour=04 AND ISTtimemin <59) ),0430
,( (IST_time_hour=05 AND IST_time_min >= 00) OR (ISTtimehour=06 AND ISTtimemin <30) ),0600
,( (IST_time_hour=06 AND IST_time_min >= 30) OR (ISTtimehour=07 AND ISTtimemin <59) ),0730
,( (IST_time_hour=08 AND IST_time_min >= 00) OR (ISTtimehour=09 AND ISTtimemin <30) ),0900
,( (IST_time_hour=09 AND IST_time_min >= 30) OR (ISTtimehour=10 AND ISTtimemin <59) ),1030
,( (IST_time_hour=11 AND IST_time_min >= 00) OR (ISTtimehour=12 AND ISTtimemin <30) ),1200
,( (IST_time_hour=12 AND IST_time_min >= 30) OR (ISTtimehour=13 AND ISTtimemin <59) ),1330
,( (IST_time_hour=14 AND IST_time_min >= 00) OR (ISTtimehour=15 AND ISTtimemin <30) ),1500
,( (IST_time_hour=15 AND IST_time_min >= 30) OR (ISTtimehour=16 AND ISTtimemin <59) ),1630
,( (IST_time_hour=17 AND IST_time_min >= 00) OR (ISTtimehour=18 AND ISTtimemin <30) ),1800
,( (IST_time_hour=18 AND IST_time_min >= 30) OR (ISTtimehour=19 AND ISTtimemin <59) ),1930
,( (IST_time_hour=20 AND IST_time_min >= 00) OR (ISTtimehour=21 AND ISTtimemin <30) ),2100
,( (IST_time_hour=21 AND IST_time_min >= 30) OR (ISTtimehour=22 AND ISTtimemin <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%')

0 Karma

Builder

Use match:

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

Explorer

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)

Esteemed Legend

Try my answer.

0 Karma

Explorer

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)

0 Karma

Builder

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?

0 Karma

Explorer

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

0 Karma