Splunk Search
Highlighted

How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

New Member

How to calculate difference between resolvedtime and inccreated_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
0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

Champion

Can't you just add this to the end

| eval duration = resolved - created

0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

New Member

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

0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

Champion

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

0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

New Member

If I remove outage from the stat then I get value for only "Yes" not "No"

0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

SplunkTrust
SplunkTrust

As I understand your data, there is one outage per cmdbci that starts at createdtime 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[].

0 Karma
Highlighted

Re: How to calculate difference between resolved_time and inc_created_time when I get stats result in 2 columns?

Legend

I think you are looking for the following after the stats command (provided inccreatedtime 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")



| eval message="Happy Splunking!!!"


0 Karma