topic How to create a time chart using multiple custom time fields, not _time? in Splunk Search

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

abake — Mon, 05 Dec 2016 06:00:26 GMT

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  ]

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

sundareshr — Mon, 05 Dec 2016 15:20:33 GMT

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

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

abake — Mon, 05 Dec 2016 22:28:05 GMT

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.

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

sundareshr — Thu, 08 Dec 2016 14:18:36 GMT

In your events, how is _time set?