calculate Time difference in stats

[hazemfarajallah]

Hello, 
I have this query 

Index = s098_prod sourcetype=SERVER_PROD
SCRIPT_ID=6SW* NOT (name="Logout" OR name="Login" OR name="Reboot") | dedup sessionnumber | eval enddatetime=if(isNull(enddatetime), "RUNNING", enddatetime)
| eval Statustext = "From ".startdatetime. " To ".enddatetime." on ".extracted_host
| stats latest(rstatus) AS "Status" latest(Statustext) as Statustext by name

I'm trying to calculate the time deference with the same grouping as Stats.
But always return Null. 

[gcusello]

Hi @hazemfarajallah,

sorry but I don't understa which difference you want to calculate: in the stats command you have only one numeric value: "Status".

Maybe the difference between "startdatetime" and "enddatetime""?

If this is your need, you have to inserta also startdatetime enddatetime in the stats command otherwise you lose this field.

index = s098_prod sourcetype=SERVER_PROD SCRIPT_ID=6SW* NOT (name="Logout" OR name="Login" OR name="Reboot") 
| dedup sessionnumber 
| stats latest(startdatetime) AS startdatetime latest(enddatetime) AS enddatetime latest(rstatus) AS Status by name
| eval 
     enddatetime=if(isNull(enddatetime), "RUNNING", enddatetime),
     Statustext = "From ".startdatetime. " To ".enddatetime." on ".extracted_host,
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime,
| table name Statustext diff

In addition, you cannot execute a diff if you have strings (like "RUNNING") so transform these fields after the calculation.

Ciao.

Giuseppe

[hazemfarajallah]

Hi, Thanks for answering, 
Yes i wanted to calculate the diff dateTime!  

you are right i must replace the string when its "running"

| eval 
     enddatetime=if(isNull(enddatetime), "RUNNING", enddatetime),
     Statustext = "From ".startdatetime. " To ".enddatetime." on ".extracted_host,
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime,
| table name Statustext diff

Now i getting  the name but still cant get the diff or Statustext   and i do believe i have a grouping issue

Here i removed the table and back to stats i can see all

| dedup sessionnumber 
| eval enddatetime=if(isNull(enddatetime), "RUNNING", enddatetime), Statustext = "From ".startdatetime. " To ".enddatetime." on ".extracted_host,     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime 
| stats latest(startdatetime) AS startdatetime latest(enddatetime) AS enddatetime latest(rstatus) AS Status, by name

Any Tips 

I dont need Stat if i use the table  but how to group them to get the diff

| stats latest(startdatetime) AS startdatetime latest(enddatetime) AS enddatetime latest(rstatus) AS Status, by name

Thanks in advance 

[gcusello]

Hi @hazemfarajallah,

you need the stats command to group your events, using table, you don't group events and calculate diff for each event.

Ciao.

Giuseppe

[hazemfarajallah]

Thanks, 

| dedup sessionnumber 
| stats latest(startdatetime) AS startdatetime latest(enddatetime) AS enddatetime latest(rstatus) AS Status by name
| eval 
     enddatetime=if(isNull(enddatetime), "RUNNING", enddatetime),  
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
| table name diff

 Here i cant group diff with stat!
and did try this as well but no luck

| stats  latest(rstatus) AS Status latest(diff) AS diff by name

BR/hazem

[gcusello]

Hi @hazemfarajallah,

what's the format of startdatetime and and enddatetime?

to execute a difference, you have to transform them in epochtime, so if they have format %d/%m&%Y %H:%M:%S, you should try using something like this:

| dedup sessionnumber 
| stats latest(startdatetime) AS startdatetime latest(enddatetime) AS enddatetime latest(rstatus) AS Status by name
| eval 
     enddatetime=if(isNull(enddatetime), "RUNNING", strptime(enddatetime),"%d/%m&%Y %H:%M:%S")), 
     startdatetime=strptime(startdatetime),"%d/%m&%Y %H:%M:%S"), 
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
| table name diff

Ciao.

Giuseppe

[hazemfarajallah]

Hello @gcusello 
not sure  but i know its a CSV file generated.

strptime

 is not working even i checked the replacement is working i check the enddatetime 

| eval 
     enddatetime=if(isNull(enddatetime),"RUNNING", strptime(enddatetime,"%d/%m&%Y %H:%M:%S")), 
     startdatetime=strptime(startdatetime,"%d/%m&%Y %H:%M:%S"), 
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
| table name enddatetime

If i go back to status its look like this From 25.01.2021 13:30:05 To 25.01.2021 13:30:27
But not sure why striptime is not working! 

BR/hazem

[gcusello]

Hi @hazemfarajallah,

as I said, to calculate a diff, you have to transform both the dates in epochtime.

So, could you share a sample of your dates?

Ciao.

Giuseppe

[hazemfarajallah]

@gcusello  Thanks for helping .

Here is sample  event 

6SWE_EE_PROD1;25.01.2021 14:56:01;18693;25.01.2021 03:00:07;25.01.2021 03:03:51;;Completed;<inputs />;;;;;;1 - Load Purchasing Documents To Queue;HP4E148770;;

I attached a photo with the fields.

 
BR/hazem 

[gcusello · Accepted solution]

Hi @hazemfarajallah,

image isn't available, but I have the information I need: the format of your fields, so please try this search:

| eval 
     enddatetime=if(isNull(enddatetime),"RUNNING", strptime(enddatetime,"%d.%m.%Y %H:%M:%S")), 
     startdatetime=strptime(startdatetime,"%d.%m.%Y %H:%M:%S"), 
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
| table name enddatetime

If it continue to fail, please run this and show me the result: one eample of closed transaction (with both startdatetime and enddatetime) and a running transaction (with only startdatetime).

| eval 
     enddatetime=if(isNull(enddatetime),"RUNNING", strptime(enddatetime,"%d.%m.%Y %H:%M:%S")), 
     startdatetime=strptime(startdatetime,"%d.%m.%Y %H:%M:%S"), 
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
| table name enddatetime startdatetime enddatetime diff

Ciao.

Giuseppe

[gcusello]

Hi @hazemfarajallah,

Good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[hazemfarajallah]

Hi again,

How i can convert this time diff, per minute?

I have diff/60 but sounds very strange number 

Thanks

[gcusello]

Hi @hazemfarajallah,

check if you have seconds or milliseconds.

Ciao,

Giuseppe

[hazemfarajallah]

@gcusello  

its like this 4899.000000  
with milliseconds! 

The closet i got is 

 | fieldformat "timeCount" = round((timeCount / 100), 3)

 But not really working !

BR/hazem

[gcusello]

Hi @hazemfarajallah,

try adding something like this:

| eval timeCount=round(timeCount,0)

if it doesn't run, try:

| rex field=timeCount "^(?<timeCount>\d+)"

Ciao.

Giuseppe

[hazemfarajallah]

Thanks its working :). 



BR/hazem

[hazemfarajallah]

@gcusello  its works like a charm 🙂  for getting the Diff.

But i dont use stat anymore! and i sort by -enddatetime.

| dedup sessionnumber 
| eval 
    enddatetime=if(isNull(enddatetime),"RUNNING", strptime(enddatetime,"%d.%m.%Y %H:%M:%S")), 
     startdatetime=strptime(startdatetime,"%d.%m.%Y %H:%M:%S"), 
     diff=if(enddatetime="RUNNING",now(),enddatetime)-startdatetime
    | table name enddatetime startdatetime enddatetime diff rstatus extracted_host
    |sort -enddatetime

Output 

name	enddatetime	startdatetime	diff	rstatus	extracted_host
EED-SM-002-TSH-Populate Queue	1611639006.000000	1611639004.000000	2.000000	Terminated	HP4E148755
EKS-006-PDF-Main	1611662583.000000	1611662482.000000	101.000000	Completed	HP4E148784
EED-SM-002-TSH-Populate Queue	1611647467.000000	1611647437.000000	30.000000	Completed	HP4E148784
EKS-006-PDF-Populate Queue	1611662478.000000	1611662404.000000	74.000000	Completed	HP4E148784
BIT-IAM-001-KID-Report	1611650651.000000	1611650636.000000	15.000000	Completed	HP4E148784

Thanks alot for your help, 
but can you explain to me how this working without stat! 
BR/Hazem

[gcusello]

Hi @hazemfarajallah,

it works because you have bothe the timestamps (stats and end) in the same event.

If instead you have only one timestamp in each event, you have to use stats.

Anyway.

Ciao and good splunking.

Giuseppe