topic Subtracts two time field showing null result in Getting Data In

Subtracts two time field showing null result

Manasi25 — Fri, 20 Aug 2021 05:14:18 GMT

Hi,

i have my query below, i used query from "Solved" questions on community, however its showing NULL result for me.

Query --

index=victorops sourcetype="splunk:victorops:incidents:json" "PTS"
| dedup incidentNumber
| eval startTimeFormatted=strptime(startTime,"%Y-%m-%dT%H:%M:%SZ") -18000
| eval SplunkStartTime=strftime(startTimeFormatted,"%m/%d/%y %H:%M:%S")

| eval endTimeFormatted=strptime(lastAlertTime,"%Y-%m-%dT%H:%M:%SZ") -18000
| eval SplunkEndTime=strftime(endTimeFormatted,"%m/%d/%y %H:%M:%S")

| eval MTTR = round((SplunkEndTime-SplunkStartTime)/86400)
| table incidentNumber, SplunkStartTime, routingKey, entityDisplayName, SplunkEndTime, currentPhase, MTTR

Above query showing "NULL" output to "MTTR" field.

Please advise !

Re: Subtracts two time field showing null result

isoutamo — Fri, 20 Aug 2021 06:56:40 GMT

you cannot calculate with string fields. You must use those with numeric values. In your case those are startTimeFormatted and endTimeFormatted.

If you would like to see MTTR as human readable convert it with

eval MTTR = tostring(MTTR, "duration")

after calculation.

r. Ismo

Re: Subtracts two time field showing null result

Manasi25 — Sat, 21 Aug 2021 06:08:03 GMT

Hello

Thank you !
I used this and this shows "00:00:00" result to all time spam. PFA

Please advise further!

Re: Subtracts two time field showing null result

ITWhisperer — Sat, 21 Aug 2021 08:49:22 GMT

| eval MTTR = tostring(round((endTimeFormatted-startTimeFormatted)/86400),"duration")

Re: Subtracts two time field showing null result

Manasi25 — Wed, 25 Aug 2021 00:59:48 GMT

Hello @ITWhisperer ,

I searched with your query and still getting "00:00:00" result to all rows.

PFA. please help !

Re: Subtracts two time field showing null result

ITWhisperer — Wed, 25 Aug 2021 09:34:06 GMT

| eval MTTR = tostring(endTimeFormatted-startTimeFormatted,"duration")

Re: Subtracts two time field showing null result

scelikok — Wed, 25 Aug 2021 12:46:14 GMT

Hi @Manasi25,

Since the time fields are string formatted, MTTR calculation is not possible. Please try below options;

in days; | eval MTTR =round((lastAlertTime-startTime)/86400) OR formatted as duration; | eval MTTR = tostring(lastAlertTime-startTime, "duration")

Re: Subtracts two time field showing null result

Manasi25 — Wed, 25 Aug 2021 15:04:39 GMT

Hi @ITWhisperer

This helps and result is good. Thank you !