topic Re: Change search query according to time in dashboard? in Dashboards & Visualizations

Change search query according to time in dashboard?

gurkiratsingh — Wed, 25 Mar 2020 10:57:15 GMT

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

Re: Change search query according to time in dashboard?

wmyersas — Wed, 25 Mar 2020 15:42:08 GMT

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?

Re: Change search query according to time in dashboard?

gurkiratsingh — Thu, 26 Mar 2020 05:13:04 GMT

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

Re: Change search query according to time in dashboard?

wmyersas — Thu, 26 Mar 2020 16:40:30 GMT

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

Re: Change search query according to time in dashboard?

gurkiratsingh — Mon, 30 Mar 2020 11:50:07 GMT

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.

Re: Change search query according to time in dashboard?

wmyersas — Mon, 30 Mar 2020 12:38:13 GMT

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

Re: Change search query according to time in dashboard?

gurkiratsingh — Tue, 31 Mar 2020 05:32:31 GMT

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.

Re: Change search query according to time in dashboard?

woodcock — Tue, 31 Mar 2020 06:13:46 GMT

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

Re: Change search query according to time in dashboard?

gurkiratsingh — Tue, 31 Mar 2020 07:13:04 GMT

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

Re: Change search query according to time in dashboard?

wmyersas — Tue, 31 Mar 2020 13:11:35 GMT

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)

Re: Change search query according to time in dashboard?

wmyersas — Tue, 31 Mar 2020 13:19:37 GMT

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

(I updated my answer, too)

Re: Change search query according to time in dashboard?

woodcock — Tue, 31 Mar 2020 13:21:43 GMT

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

Re: Change search query according to time in dashboard?

gurkiratsingh — Wed, 01 Apr 2020 06:44:16 GMT

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)

Re: Change search query according to time in dashboard?

gurkiratsingh — Wed, 30 Sep 2020 04:51:05 GMT

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

Re: Change search query according to time in dashboard?

woodcock — Wed, 01 Apr 2020 13:52:30 GMT

Try my answer.

Re: Change search query according to time in dashboard?

wmyersas — Wed, 01 Apr 2020 14:10:18 GMT

Use match:

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

Re: Change search query according to time in dashboard?

gurkiratsingh — Thu, 02 Apr 2020 10:49:36 GMT

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)

topic Re: Change search query according to time in dashboard? in Dashboards & Visualizations

Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

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

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

Re: Change search query according to time in dashboard?

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