How do I do mathematical operations (subtraction) with two timestamps in the same event?

[hmdoan]

I've been trying to:
1) convert two date stamps into epoch (timestamp and lastmodified). The lastmodified stamp will be more recent.
2) calculate if there is greater than a 7 day difference between the two
3) then display these events.

My time stamps are all in the %d/%m/%Y %H:%M:%S format.

index=netcool_daily name="Auto Ticket" | eval starttime=strptime(timestamp,"%m/%d/%Y %H:%M:%S") | eval endtime=strptime(lastmodified,"%m/%d/%Y %H:%M:%S") | eval difference=(endtime - starttime)/86400 | search difference > 7

I get nothing returned.

[richgalloway · Accepted solution]

You say your timestamps are in "d/m/Y" format, but your strptime commands are using "m/d/Y" format. Which is the correct format?

[hmdoan]

Thanks! That was it. I had the wrong date format. Once I fixed that, the search worked great. Typo on my part.

[woodcock]

Everything looks OK so the problem has to be that either (or both) starttime or endtime is not created and that is surely because either timestamp or lastmodified does not exist. Try this search and whichever field does not exist, make it exists (probably just fix a typo):

index=netcool_daily name="Auto Ticket" | eval starttime=strptime(timestamp,"%m/%d/%Y %H:%M:%S") | eval endtime=strptime(lastmodified,"%m/%d/%Y %H:%M:%S") | table timestamp lastmodified starttime endtime

[hmdoan]

My stupid error:

The format is in "%Y/%m/%d %H:%M:%S"

[richgalloway]

So when you change your strptime commands to strptime(timestamp, "%Y/%m/%d %H:%M:%S") and strptime(lastmodified, "%Y/%m/%d %H:%M:%S") does it work?

[hmdoan]

Yes - once i changed my time formatting to be consistent, it worked. The dangers of cut-n-paste!!! 🙂

[richgalloway]

You say your timestamps are in "d/m/Y" format, but your strptime commands are using "m/d/Y" format. Which is the correct format?

[richgalloway]

So when you change your strptime commands to strptime(timestamp, "%Y/%m/%d %H:%M:%S") and strptime(lastmodified, "%Y/%m/%d %H:%M:%S") does it work?

[richgalloway]

So when you change your strptime commands to strptime(timestamp, "%Y/%m/%d %H:%M:%S") and strptime(lastmodified, "%Y/%m/%d %H:%M:%S") does it work?

[hmdoan]

Sorry. The format is Year/Month/Day H:M:S