topic Re: Create a dashboard with timepicker for a field with date/time in Splunk Search

Create a dashboard with timepicker for a field with date/time

ssaenger — Thu, 02 Nov 2023 16:06:53 GMT

Hi All,

I have a search query that allows me to pull results from an index summary.
One of the fields is a time/date field.

The data is pull from a database and is a schedule so the time in this field is not the indexed field.

I would like to search on the time field and have the below query which allows me to do this.

However i would like to move this into a dashboard and have a timepicker.

Is this possible to do this?

I need to have a time picker to grab the correct index summary data, then again for the field.

| eval eventday=strftime(eventday,"%Y-%m-%d")

| eval eventday1=strptime(eventday,"%Y-%m-%d")
| eval min_Date=strptime("2023-10-11","%Y-%m-%d")
| eval max_Date=strptime("2023-10-14","%Y-%m-%d")
| where (eventday1 >= min_Date AND eventday1 < max_Date)

| eval record=substr(CODE, -14, 1)
| eval record=case(record==1,"YES", record==0,"NO")

| stats
count(eval(record="YES")) as events_record
count(record) as events
by NAME
| eval percentage_record=(events/events_record)*100
| fillnull value=0 percentage_record
| search percentage_record<100
| sort +percentage_record -events

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Thu, 02 Nov 2023 16:20:09 GMT

Assuming your timepicker is called timepicker and you want to use sTime to filter your events, try something like this

index=summary sourcetype=prod source=service DESCR="Central Extra" | dedup SI_START,NAME,DESCR | eval sTime=strptime(SI_START,"%Y-%m-%d %H:%M:%S") | where relative_time(now(),$timepicker.earliest$) <= sTime AND relative_time(now(),$timepicker.latest$) > sTime

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Thu, 02 Nov 2023 16:38:58 GMT

Hey ITWhisperer
as my results come from an index summary, would i need to have a separate timepicker to get the index summary date?

or can i also use timepicker=_time?

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Thu, 02 Nov 2023 16:41:57 GMT

I am not sure what you mean, you can use the same timepicker if you want, it depends on how you want your dashboard to work.

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Mon, 06 Nov 2023 13:41:31 GMT

Hi,

I have tried your solution and for relative time and i dont get any results if i use it within a dash board, I only get the earliest_time when selecting Last xxxx eg last 30days.
I wanted to see what it looked like so ran

I have tried to use, https://www.youtube.com/watch?v=OzEb7Q-fuXs&t=649s, however i have found that the dashboard does not like the following eval statement;

I get the reult of NaN

the %z in the strptime is now not valid!!

<progress> <eval token="toearliest">strptime("$job.earliestTime$","%Y-%m-%dT%H:%M:%S.%3N+%z")</eval> <eval token="tolatest">strptime("$job.latestTime$","%Y-%m-%dT%H:%M:%S.%3N+%Z")</eval> <eval token="tokgap"></eval>(($job.latestTime$-$job.earliestTime$)/86400) <set token="jobearliest">$job.earliestTime$</set> <set token="joblatest">$job.latestTime$</set> </progress>

Has anybody else encountered this issue and found a solution?

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Mon, 06 Nov 2023 14:08:52 GMT

Which version of Splunk are you using?

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Mon, 06 Nov 2023 14:15:34 GMT

Splunk Enterprise

Version:8.2.6

Build:a6fe1ee8894b

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Mon, 06 Nov 2023 14:48:38 GMT

If I recall correctly, I had issues with earlier versions of Splunk, however, I am unable to reproduce any errors at the moment. Please share your dashboard source code in a code block </>

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Mon, 06 Nov 2023 14:58:29 GMT

This is some experimenting I have done following the youtube video above.
There is some other work as i would like to get the gap between any date ranges to be used in an average calculation, once i have this timepicker issue sorted.

<form> <label>SO-testing</label> <search> <query>|makeresults</query> <earliest>$time.earliest$</earliest> <latest>$time.latest$</latest> <progress> <eval token="toearliest">strptime("$job.earliestTime$","%Y-%m-%dT%H:%M:%S.%3N+%z")</eval> <eval token="tolatest">strptime("$job.latestTime$","%Y-%m-%dT%H:%M:%S.%3N+%Z")</eval> <eval token="tokgap"></eval>(($job.latestTime$-$job.earliestTime$)/86400) <set token="jobearliest">$job.earliestTime$</set> <set token="joblatest">$job.latestTime$</set> </progress> </search> <fieldset submitButton="false"> <input type="time" token="time"> <label>timepicker</label> <default> <earliest>-48h@h</earliest> <latest>now</latest> </default> </input> </fieldset> <row> </row> <row> <panel> <table> <title>token values</title> <search> <query> | makeresults |eval timeTokenearliest="$time.earliest$" |eval timeTokenlatest="$time.latest$" |eval gap=timeTokenlatest-timeTokenearliest |eval jobearliest = "$jobearliest$" |eval joblatest = "$joblatest$" |eval toearliest ="$toearliest$" |eval tolatest ="$tolatest$" |eval gapday = tostring(gap,"duration") |eval gapday1 = gap/86400 |eval reltimeearl = relative_time(now(),"$time.earliest$") |eval reltimelate = relative_time(now(),"$time.latest$") |table _time reltimeearl reltimelate timeTokenearliest timeTokenlatest gap gapday gapday1 tokgap jobearliest joblatest toearliest tolatest </query> <earliest>$time.earliest$</earliest> <latest>$time.latest$</latest> </search> <option name="drilldown">none</option> </table> </panel> </row> </form>

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Mon, 06 Nov 2023 15:17:15 GMT

The relative_time function doesn't appear to work with the string "now" so change this line to compensate

|eval reltimelate = if("$time.latest$"=="now",now(),relative_time(now(),"$time.latest$"))

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Mon, 06 Nov 2023 15:42:03 GMT

interestingly this works if i use the Last xxx option in presets, but nothing is shown when using a between option.
The data from the token in the 'process' section does always give a time;
2023-10-02T00:00:00.000+00:00
however, i cannot convert this to Epoch...

Re: Create a dashboard with timepicker for a field with date/time

ITWhisperer — Mon, 06 Nov 2023 15:54:42 GMT

|eval reltimeearl = if(isnum($time.earliest$),$time.earliest$,relative_time(now(),"$time.earliest$")) |eval reltimelate = if(isnum($time.latest$),$time.latest$,if("$time.latest$"=="now",now(),relative_time(now(),"$time.latest$")))

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Tue, 07 Nov 2023 14:34:45 GMT

Great that works when using between - but not for Last XXX, i get this error;
Error in 'eval' command: The expression is malformed. Expected ).

Could you please explain why this works.

This is OK, as ideally i would like to only have the option on the time picker of DATE RANGE -- BETWEEN.

I think i need to make a css file for this to work - do you know if its possible to do this without css?

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Wed, 08 Nov 2023 09:58:05 GMT

HI ITWhisperer,

I have used the statement you gave and it works well and have used it to get the gap between the days as i wish to use this in an average

|eval reltimeearl = if(isnum(1698796800),1698796800,relative_time(now(),"1698796800")) |eval reltimelate = if(isnum(1699056000),1699056000,if("1699056000"=="now",now(),relative_time(now(),"1699056000"))) | where sTime>reltimeearl AND sTime<=reltimelate |eval relgap=(tonumber(reltimelate-reltimeearl)/86400) | stats sum(Percentage_Rec) as Rec1 by description key | eval Av_Rec=(Rec1/relgap) | table description Rec1

This does not work as the relgap is not processed.
Looking at the fields when in Verbose mode - i can see a count against this value.
As a test i did dedup relgap so the count=1.
However this still does not pass to the eval statement.

Can you see why?

just using a value of 3 works...

eval Av_Rec=(Rec1/3)

Re: Create a dashboard with timepicker for a field with date/time

ssaenger — Wed, 08 Nov 2023 10:57:13 GMT

solved it by adding relgap to the end of the stats command

| stats sum(Percentage_Rec) as Rec1 by description key relgap