How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns
index="snow_incident"
| head 1
| spath path=result{} output=x
| fields - _raw
| mvexpand x
| spath input=x
| sort x desc
| foreach x [eval outage = case(match("true",u_major_incident), "Yes", match("false",u_major_incident) AND tonumber(strptime(resolved_at,"%b-%d-%Y %I:%M:%S")) != 0, "No")]
| foreach x [eval inc_created_time = if(match("true",u_major_incident),strptime(sys_created_on,"%b-%d-%Y %I:%M:%S"),"0")]
| foreach x [eval resolved_time = if(match("false",u_major_incident),strptime(resolved_at,"%b-%d-%Y %I:%M:%S"),"0")]
| eval outage_yes = "Yes"
| eval outage_no = "No"
| stats first(inc_created_time) as created first(resolved_time) as resolved by outage,cmdb_ci
I think you are looking for the following after the stats command (provided inc_created_time and resolved_time are epoch time and not string time)
| eval duration=resolved-create
If they are string time then you need to perform strptime on them first (based on the timeformat,following assumes you date is YYYY/mm/dd HH:MM:SS. Following is an example:
| eval inc_created_time=strptime(inc_created_time,"%Y/%m/%d %H:%M:%S")
| eval inc_resolved_time=strptime(inc_resolved_time,"%Y/%m/%d %H:%M:%S")
| eval duration=resolved-create
| fieldformat inc_created_time=strftime(inc_created_time,"%Y/%m/%d %H:%M:%S")
| fieldformat inc_resolved_time=strftime(inc_resolved_time,"%Y/%m/%d %H:%M:%S")
Can't you just add this to the end
| eval duration = resolved - created
I cannot do that as I get results in 2 rows
Outage cmdb_ci created resolved
No Ardome-News 0 658565
Yes Ardome-News 768765 0
I wanted to subtract Resolved - Created which are in 2 different rows
It is unclear why you need the Outage
field. If you remove that from your stats
command, you will get the proper value. If you do require the Outage
field, then you will need to explain how you use it to mark the ends of the transactions.
| stats first(inc_created_time) as created first(resolved_time) as resolved by cmdb_ci | eval duration = resolved - created
If I remove outage from the stat then I get value for only "Yes" not "No"
As I understand your data, there is one outage per cmdb_ci that starts at created_time and ends at resolved_time. "Yes" and "No" are unnecessary if you produce a single line per outage.
Looking at your code, I wonder if half of the lines are really necessary. Run the code only up to line 7 and see if there is more than one x on the record that is present. If not, then get rid of the foreach part of the next 3 commands, just use the code inside the brackets[].