Dashboards & Visualizations

Change search query according to time in dashboard?

gurkiratsingh
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

wmyersas
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

woodcock
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

wmyersas
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

woodcock
Esteemed Legend

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

gurkiratsingh
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

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

0 Karma

gurkiratsingh
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

wmyersas
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

gurkiratsingh
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

wmyersas
Builder

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

(I updated my answer, too)

gurkiratsingh
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(
( (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%')

0 Karma

wmyersas
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

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

woodcock
Esteemed Legend

Try my answer.

0 Karma

gurkiratsingh
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

wmyersas
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

gurkiratsingh
Explorer

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...