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

[ssaenger]

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.

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")
| sort 0 -sTime
| eval eventday=strptime(SI_START,"%Y-%m-%d %H:%M:%S")
| bucket eventday span=1d

| 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

[ssaenger]

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

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

[ssaenger]

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)

[ssaenger]

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?

[ssaenger]

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

[ITWhisperer · Accepted solution]

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

[ssaenger]

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>

[ITWhisperer]

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

[ssaenger]

Splunk Enterprise

Version:8.2.6

Build:a6fe1ee8894b

[ITWhisperer]

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

[ssaenger]

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?

[ITWhisperer]

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.

[ITWhisperer]

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

[ssaenger]

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

| makeresults |eval timeTokenearliest="$time.earliest$" |eval timeTokenlatest="$time.latest$" |eval gap=timeTokenlatest-timeTokenearliest   
            |eval reltimeearl = relative_time(now(),"$time.earliest$")
            |eval reltimelate = relative_time(now(),"$time.latest$")
            |table _time reltimeearl reltimelate 

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?

[ITWhisperer]

Which version of Splunk are you using?