Comparing an incidents event timestamp to Splunk landing timestamp

[auzark]

I would like some help creating a report that will show the seconds diff between my event timestamp and the Splunk landing timestamp.

I have the below query which will give me the diff between _indextime  and  _time  but I would also like the seconds difference between GenerationTime (ie...2024-04-23 12:49:52)    and _indextime.

index=splunk_index  sourcetype=splunk_sourcetype
| eval tnow = now() | convert ctime(tnow)
| convert ctime(_indextime) as Index_Time
| eval secondsDifference=_indextime-_time
| table Node EventNumber GenerationTime Index_Time, _time, secondsDifference 

[auzark]

How would I incorporate an average of genSecondsDifference over a 24 hour period? for 7 days?

[auzark]

Thanks, Tejas and Rich...   Very much appreciated.

[tej57]

Hello @auzark ,

You can assign a particular field to _indextime and then use that to find the difference. The only catch here would be that _indextime would be in epoch time and hence, you'll have to convert the GenerationTime into epoch format before calculating the difference. Your query should look something like below:

index=splunk_index  sourcetype=splunk_sourcetype
| eval tnow = now() 
| eval indexTime = _indextime
| eval GenerationTime_epoch=strptime(GenerationTime,"%Y-%m-%d %H"%M:%S")
| convert ctime(tnow)
| convert ctime(_indextime) as Index_Time
| eval secondsDifference=indexTime-_time
| eval GenTimeDifferenceInSeconds = GenerationTime_epoch-indexTime
| table Node EventNumber GenerationTime Index_Time, _time, secondsDifference,GenTimeDifferenceInSeconds  

Thanks,
Tejas.

---
If the above solution helps, an upvote is appreciated!!

[richgalloway · Accepted solution]

Convert GenerationTime into epoch format, then take the difference between the result and _indextime.

index=splunk_index  sourcetype=splunk_sourcetype
| eval tnow = now() | convert ctime(tnow)
| convert ctime(_indextime) as Index_Time
| eval secondsDifference=_indextime-_time
| eval genEpoch = strptime(GenerationTime, "%Y-%m-%d %H:%M:%S")
| eval genSecondsDifference = _indextime - genEpoch
| table Node EventNumber GenerationTime Index_Time, _time, secondsDifference, genSecondsDifference

[auzark]

Hi Rich,

How would I incorporate an average of genSecondsDifference over a 24 hour period? for 7 days?