topic Re: calculate Time difference in stats in Getting Data In

calculate Time difference in stats

hazemfarajallah — Mon, 25 Jan 2021 10:29:26 GMT

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.

Re: calculate Time difference in stats

gcusello — Mon, 25 Jan 2021 10:53:20 GMT

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

Re: calculate Time difference in stats

hazemfarajallah — Mon, 25 Jan 2021 11:25:19 GMT

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

Re: calculate Time difference in stats

gcusello — Mon, 25 Jan 2021 11:43:05 GMT

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

Re: calculate Time difference in stats

hazemfarajallah — Mon, 25 Jan 2021 11:59:25 GMT

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

Re: calculate Time difference in stats

gcusello — Mon, 25 Jan 2021 12:10:18 GMT

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

Re: calculate Time difference in stats

hazemfarajallah — Mon, 25 Jan 2021 12:39:08 GMT

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

Re: calculate Time difference in stats

gcusello — Mon, 25 Jan 2021 13:46:34 GMT

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

Re: calculate Time difference in stats

hazemfarajallah — Mon, 25 Jan 2021 14:03:49 GMT

@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

Re: calculate Time difference in stats

gcusello — Mon, 25 Jan 2021 14:57:10 GMT

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).

Ciao.

Giuseppe

Re: calculate Time difference in stats

hazemfarajallah — Tue, 26 Jan 2021 14:03:42 GMT

@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

Re: calculate Time difference in stats

gcusello — Tue, 26 Jan 2021 14:04:41 GMT

Hi @hazemfarajallah,

Good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: calculate Time difference in stats

hazemfarajallah — Tue, 26 Jan 2021 15:01:34 GMT

Hi again,

How i can convert this time diff, per minute?

I have diff/60 but sounds very strange number

Thanks

Re: calculate Time difference in stats

gcusello — Tue, 26 Jan 2021 15:10:52 GMT

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

Re: calculate Time difference in stats

gcusello — Tue, 26 Jan 2021 15:20:13 GMT

Hi @hazemfarajallah,

check if you have seconds or milliseconds.

Ciao,

Giuseppe

Re: calculate Time difference in stats

hazemfarajallah — Tue, 26 Jan 2021 17:03:41 GMT

@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

Re: calculate Time difference in stats

gcusello — Tue, 26 Jan 2021 17:11:25 GMT

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

Re: calculate Time difference in stats

hazemfarajallah — Tue, 26 Jan 2021 19:18:47 GMT

Thanks its working :).

BR/hazem