Splunk Search

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

abake
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  ]

sundareshr
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

abake
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

sundareshr
Legend

In your events, how is _time set?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...