Getting Data In

calculate Time difference in stats

hazemfarajallah
Explorer

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. 


Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

hazemfarajallah
Explorer

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 



0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Explorer

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma

hazemfarajallah
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Tags (1)
0 Karma

hazemfarajallah
Explorer

@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 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

Hi @hazemfarajallah,

Good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

hazemfarajallah
Explorer

Hi again,

How i can convert this time diff, per minute?

I have diff/60 but sounds very strange number 

Thanks

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @hazemfarajallah,

check if you have seconds or milliseconds.

Ciao,

Giuseppe

0 Karma

hazemfarajallah
Explorer

@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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Explorer

Thanks its working :). 



BR/hazem

0 Karma

hazemfarajallah
Explorer

@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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...