Subtracts two time field showing null result

[Manasi25]

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 !

[scelikok]

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

[Manasi25]

Hello

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

Please advise further!

[ITWhisperer]

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

[Manasi25]

Hello @ITWhisperer ,

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

PFA. please help !

[ITWhisperer · Accepted solution]

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

[Manasi25]

Hi @ITWhisperer 

This helps and result is good. Thank you !

[isoutamo]

Hi

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