Splunk Search

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

sats2020
New Member

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
0 Karma

niketn
Legend

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")
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

rjthibod
Champion

Can't you just add this to the end

| eval duration = resolved - created

0 Karma

sats2020
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

rjthibod
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

sats2020
New Member

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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[].

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...