Getting Data In

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

Souletting
Explorer

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
Labels (1)
0 Karma
1 Solution

seemanshu
Path Finder

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.

View solution in original post

Souletting
Explorer

Thank you that worked. 

seemanshu
Path Finder

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,

Screenshot 2023-04-28 233342.png

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!

Souletting
Explorer

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. 

0 Karma

seemanshu
Path Finder

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!

 

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.

Souletting
Explorer

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. 

 

Screenshot 2023-05-09 at 10.25.21 AM.png

0 Karma

seemanshu
Path Finder

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.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...