topic Re: How to find the difference or delta in the two time fields for the same record? in Getting Data In

How to find the difference or delta in the two time fields for the same record?

Souletting — Fri, 28 Apr 2023 16:03:18 GMT

I am trying to find a way to produce a column in a table to show the difference between the recieved_time and the remediation_time. Currently the Diff_in_Time field and TotalDiff_in_Time fields return empty. Any

index=someapplication sourcetype=some_log subject="*" "folder_locations{}"="*" from_address="*" remediation_timestamp="*" received_time="*" recipient_address="*" to_addresses="*" | eval rectime=received_time | eval remtime=remediation_timestamp | eval Diff_in_Time=strptime(rectime, "%Y-%m-%d %H:%M:%S.%3N")-strptime(remtime, "%Y-%m-%d %H:%M:%S.%3N") | eventstats sum(Diff_in_Time) as TotalDiff_in_Time | table rectime remtime Diff_in_Time TotalDiff_in_Time

Re: How to find the difference or delta in the two time fields for the same record?

richgalloway — Fri, 28 Apr 2023 17:22:47 GMT

That query looks right. Can you share some sample events so we can verify the SPL is correct for the data?

Re: How to find the difference or delta in the two time fields for the same record?

seemanshu — Fri, 28 Apr 2023 18:06:12 GMT

Hi @Souletting ,

Here, in the following search, I am taking an example by makeresult command, for verifying the above search,

| makeresults | eval rec_time="05/Feb/2023:03:12:03" | eval rem_time="05/Feb/2023:03:13:03" | eval Diff_in_Time=strptime(rem_time,"%d/%b/%Y:%H:%M:%S") - strptime(rec_time,"%d/%b/%Y:%H:%M:%S") | eventstats sum(Diff_in_Time) as TotalDiff_in_Time | table rec_time rem_time Diff_in_Time TotalDiff_in_Time

The output of the above search is as follows,

So, the search seems to be working fine, the issue seems to be in the time-format used in your search.

Kindly upvote if found useful.

Happy Splunking!

Re: How to find the difference or delta in the two time fields for the same record?

Souletting — Tue, 09 May 2023 14:23:44 GMT

What you say makes sense but the rec_time and rem_time aren't static results. They will be different for each row. Is there a variable that can be used to pull those in and see the difference of time in each row.

Re: How to find the difference or delta in the two time fields for the same record?

Souletting — Tue, 09 May 2023 14:29:25 GMT

This is a sample of what I am looking at. I would like to have a column to show the difference in time between the received time and remediatated time. When I try to use the syntax above I only get a blank column.

Re: How to find the difference or delta in the two time fields for the same record?

seemanshu — Tue, 09 May 2023 14:35:18 GMT

Hi @Souletting ,

The real data is suppose to be different with new rows, but here just to check the search, we have used the example of sample values for rec_time and rem_time.
Also, this helps in verifying that if it works for the sample format of data, then it will work for the real data as well.

Hope this explains!

Re: How to find the difference or delta in the two time fields for the same record?

seemanshu — Tue, 09 May 2023 14:42:47 GMT

Hi @Souletting ,

Thanks for sharing the sample data format.
Please use the following search, as per the shared data format,

index=someapplication sourcetype=some_log subject="*" "folder_locations{}"="*" from_address="*" remediation_timestamp="*" received_time="*" recipient_address="*" to_addresses="*" | eval rectime=received_time | eval remtime=remediation_timestamp | eval Diff_in_Time=strptime(remtime,"%Y-%m-%d %H:%M:%S") - strptime(rectime,"%Y-%m-%d %H:%M:%S") | eventstats sum(Diff_in_Time) as TotalDiff_in_Time | table rectime remtime Diff_in_Time TotalDiff_in_Time

Kindly support the answer, if found helpful.

Re: How to find the difference or delta in the two time fields for the same record?

Souletting — Tue, 09 May 2023 17:16:24 GMT

Thank you that worked.