Splunk Search

How to convert date differences to seconds

pipipipi
Path Finder

Hi all,

I have a lookup like this.

    caseid date
    a          19-01-01 15:54:43.934000000
    b          19-01-01 16:54:43.934000000
    c          19-01-01 17:54:43.934000000
    d          19-01-01 18:54:43.934000000
    e
    f
    g
    .
    .
    .
    .
    .

I did this command

| inputlookup test1
    | eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
    | stats min(date) as starttime max(date) as endtime by caseid
    | eval diff =endtime-starttime 
    | stats avg(diff) as average

my result is like this.(this is dummy)

average
999999.9999999

I want to get results like this.

average
3600

(this is 1 hour)
I understand I have to convert, but I don't know how to convert date differences to seconds.
Could you help me?

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

| inputlookup test1
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| stats range(date) AS diff BY caseid
| stats avg(diff) AS average
| fieldformat average = tostring(average, "duration")

View solution in original post

0 Karma

jinseong
Path Finder

hello.

|eval temp=strptime(start_time, "%Y-%m-%d %H:%M:%S.%Q")
|eval temp1=temp+duration
|eval end_time=strftime(temp1,"%Y-%m-%d %H:%M:%S.%Q")

|table start_time end_time duration
|stats avg(duration) as qwe

|eval HH=floor(qwe/3600)
|eval MM=floor((qwe-(HH*3600))/60)
|eval SS=round(qwe-(HH*3600)-(MM*60),0)

|eval avg_duration=(HH + ":" + MM + ":" +SS)
|fields avg_duration

I expressed the hour and minute by the following command. It is possible by subtracting the minute from the average time and subtracting the minute from this part.

0 Karma

to4kawa
Ultra Champion
999999.9999999 *+-/ ? = 3600

what's your formula?

0 Karma

woodcock
Esteemed Legend

Like this:

| inputlookup test1
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| stats range(date) AS diff BY caseid
| stats avg(diff) AS average
| fieldformat average = tostring(average, "duration")
0 Karma

pipipipi
Path Finder

Thank you for helping me.
When I using tostring, What is the unit of time? I think seconds, is it right?

0 Karma

woodcock
Esteemed Legend

It auto-ranges the output but keeps the actual value as seconds stored as a number (unitless).

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@pipipipi

Do you want to round?
Like by adding this in below search?

| eval average=round(average,0)

I tried your search with different data it is working.

| makeresults 
| eval _raw=" caseid date
 a          19-01-01 15:54:43.934000000
 b          19-01-01 16:54:43.934000000
 c          19-01-01 17:54:43.934000000
 d          19-01-01 18:54:43.934000000
 a          19-01-01 18:54:43.934000000
 b          19-01-01 19:54:43.934000000
 c          19-01-01 20:54:43.934000000
 d          19-01-01 21:54:43.934000000" 
| multikv forceheader=1 
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| stats min(date) as starttime max(date) as endtime by caseid 
| eval diff =endtime-starttime 
| stats avg(diff) as average | eval average=round(average,0)

Please let us know if you have different expectations.

Thanks

0 Karma

pipipipi
Path Finder

Thank you for helping me.
Sorry, my English is bad.

I want to convert 99999.9999(this is example value) to 3600.

| eval diff =endtime-starttime 
| stats avg(diff) as average

is date difference, I want to express the date difference in seconds

Thank you for helping

0 Karma

anmolpatel
Builder

As such:

| makeresults 
| eval _raw = "caseid,date
 a, 19-01-01 15:54:43.934000000
 b, 19-01-01 16:54:43.934000000
 c, 19-01-01 17:54:43.934000000
 d, 19-01-01 18:54:43.934000000" 
| multikv forceheader=1 
| eval date_new=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| eventstats min(date_new) as starttime max(date_new) as endtime count as totalevents 
| eval avg = round((endtime-starttime) / totalevents, 1)
0 Karma

pipipipi
Path Finder

Thank you for helping me.
I have about 15000 column, so,
|eval _raw........ is difficult for me.

Thank you so much.

0 Karma

anmolpatel
Builder

@pipipipi the _raw line is to demonstrate the example.

In your case, you will have the following:
| inputlookup test1
| eval date_new=strptime(date,"%y-%m-%d %H:%M:%S.%9N")
| eventstats min(date_new) as starttime max(date_new) as endtime count as totalevents
| eval avg = round((endtime-starttime) / totalevents,1)

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...