Splunk Search

Can't calculate time difference

appleman
Contributor

Hello,

I want to calculate the time difference between two fields, so I tried the below query, but it didn't work.
Please correct my query.

index=service | eval Time=strptime(_time, "%Y/%m/%d %H:%M:%S") | eval LastSync=strptime(lastSync, "%Y/%m/%d %H:%M:%S") | eval diff=LastSync-Time | table _time,lastSync,diff,| sort -_time

Thank you.

0 Karma
1 Solution

sduff_splunk
Splunk Employee
Splunk Employee

You need to be a little more specific as to why it didn't work.

Can you provide some sample events, I'm sure that would be helpful.

View solution in original post

0 Karma

sduff_splunk
Splunk Employee
Splunk Employee

You need to be a little more specific as to why it didn't work.

Can you provide some sample events, I'm sure that would be helpful.

0 Karma

appleman
Contributor

Here is the sample event.

2017/04/26 22:43:29 operation_id="1493246609" unitid="123456" easid="AAAAAAAAAA" deviceid="11111@123456" firstSync="2017/03/21 23:53:41" lastSync="2017/03/22 08:54:04" status="Quarantined" type="" userAgent="sample.com" apiname="apitest" message="Matched_Standard_mail."

I want to calculate time difference between _time(2017/04/26 22:43:29 in this case) and lastSync(2017/03/22 08:54:04 in this case).
When I ran the query, the result came out to be like this. (See below)

_time lastSync diff
2017/04/26 22:43:29 2017/03/22 08:54:04

It seems like the eval calculation didn't work we,, so the "diff" field could't get a value.
In case, I changed "strptime" to "strftime", but I got the same result.
If there is other way to fix it, please let me know.

Thank you.

0 Karma

dineshraj9
Builder

You don't need to convert _time field -

index=service | eval Time=_time | eval LastSync=strptime(lastSync, "%Y/%m/%d %H:%M:%S") | eval diff=LastSync-Time | table _time,lastSync,diff,| sort -_time
0 Karma

ryhluc01
Communicator

The L for LastSync was added to your table as lowercase. What was printed was lastSync(raw data field) instead of LastSync(new eval field)

0 Karma

ryhluc01
Communicator

The new eval "Time" is also not being used or sorted. Everything is going towards the original _time.
Im not sure if thats how it was meant to be or not.

Also, I added this comment to @dineshraj9 instead of the question creator. Sorry : )

0 Karma

appleman
Contributor

Thank you for your reply.
It worked!

Thank you very much.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...