Splunk Search

How to create a time chart using multiple custom time fields, not _time?

Engager

I'm trying to chart two different things in the same graph using two different custom time fields.
It almost works (the graph shows up), however, the time range picker seems to be mangling things a bit. Even though eval is overwriting _time for the timechart function, the initial search is still searching based on initial _time value, and is therefore not returning accurate results. Needless to say, when I try to timechart this, it's a bit of a mess. No matter what I do, it seems that the search returns data from as far back as a year (possibly the entire data set).

index=*  | eval CloseTime=strptime('Closed Date Time',"%d/%m/%Y %I:%M:%S %p") | where CloseTime>relative_time(CloseTime, "$timepicker.earliest$") | eval _time=CloseTime | timechart count AS Closed span=1d | appendcols [ search index=* | eval CreateTime=strptime('Created Date Time',"%d/%m/%Y %I:%M:%S %p") |  where CreateTime>relative_time(CreateTime, "$timepicker.earliest$") | eval _time=CreateTime  |  timechart count AS Created span=1d  ]

Legend

In your timepicker tag, add this (only the change part)

<input type="time">
...
<change>
   <eval token="t">if(isnum($timepicker.earliest$), $timepicker.earliest$, relative_time(now(), $timepicker.earliest$)</eval>
</change>
</input>

And try this for you query

index=* 
| eval CreateTime=strptime('Created Date Time',"%d/%m/%Y %I:%M:%S %p") 
| eval CloseTime=strptime('Closed Date Time',"%d/%m/%Y %I:%M:%S %p") 
| where CreateTime>$t$ OR CloseTime>$t$
| eval closed=if(CloseTime>$t$, 1, 0)
| eval opened=if(CreateTime>$t$, 1 0) 
| bin span=1d CreateTime
| stats sum(opened) as Opened sum(closed) as Closed by CreateTime
| eval CreateTime=strftime(CreateTime,"%d/%m/%Y %I:%M:%S %p") 
0 Karma

Engager

Thanks for your help. Unfortunately using this query I still get results arbitrarily out of the date range. Regardless of what the picker is set to I get results as far back as December in the chart.
However, I've amended by original search to include the new tokens. This seems to work - mostly. The only issue I'm having now is that the tokens don't seem to update correctly - the search seems to use whatever the picker was last set to.

0 Karma

Legend

In your events, how is _time set?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!