Splunk Search

How to subtract dates in the format (%d %m %Y %H:%M:%S %Z) to find the difference?

Explorer

Dear all,

Data is indexed from a CSV file.

I am trying to calculate the amount of seconds between a couple of timestamps.

The dates are in %d %m %Y %H:%M:%S %Z format and I am trying to do it this way:

...x | eval compliance_time=strptime("first_date", "%d %m %Y %H:%M:%S %Z")- strptime("second_date", "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time)  |gauge avg(compliance_time) 1 5 10 15

I've checked current answers already, but unfortunately, I might be missing something.

first_date="21 10 2015 11:16:16 UTC"
second_date="21 10 2015 11:18:46 UTC"

but compliance_time is empty.

Let me know if you need more details.

Thank you,

0 Karma
1 Solution

Revered Legend

What you need is to enclose field name within single quotes for field names containing spaces. This is required for eval and where commands. Try something like this

...x | eval compliance_time=strptime('first_date', "%d %m %Y %H:%M:%S %Z")- strptime('second_date', "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time) |gauge avg(compliance_time) 1 5 10 15

View solution in original post

Revered Legend

What you need is to enclose field name within single quotes for field names containing spaces. This is required for eval and where commands. Try something like this

...x | eval compliance_time=strptime('first_date', "%d %m %Y %H:%M:%S %Z")- strptime('second_date', "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time) |gauge avg(compliance_time) 1 5 10 15

View solution in original post

Explorer

Hello Rich, Somesoni,

Yes, you're both right.

Renaming and single quotes would have worked.

I came across the solution yesterday after initial Rich feedback regarding removing double quotes but wasn't able to post the answer due the "rookie user" limitation to 2 posts per day.

I consider this close, sorry to bother you, thanks again.

0 Karma

Motivator

please pick one of the answers to accept--i know it's hard when they're both right! 🙂

SplunkTrust
SplunkTrust

Remove the quotation marks from the first arguments to your strptime commands.

eval compliance_time=strptime(first_date, "%d %m %Y %H:%M:%S %Z")- strptime(second_date, "%d %m %Y %H:%M:%S %Z")
---
If this reply helps you, an upvote would be appreciated.

Explorer

Hi Rich,

I need the quotations marks as the name of the field has an space, is Created At, which I have to quote it as "Created At".

Thank you,

0 Karma

SplunkTrust
SplunkTrust

I suspect strptime doesn't handle quoted field names well. Instead, it is trying to parse the literal string "first date" and not getting a time in the given format. An alternative is to rename fields before parsing them.

... | rename "first date" as first_date, "second date" as second_date | eval compliance_time=strptime(first_date, "%d %m %Y %H:%M:%S %Z")- strptime(second_date, "%d %m %Y %H:%M:%S %Z") | rename first_date as "first date", second_date as "second date" | ...
---
If this reply helps you, an upvote would be appreciated.
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!