topic How do you find the count of hours between two dates? in Splunk Search

How do you find the count of hours between two dates?

ramesh12345 — Tue, 19 Feb 2019 12:11:26 GMT

Hi,

Please find the below query

index="os" sourcetype="Service"  CaseNumber=* status="Complete"  assignment_group=* |dedup CaseNumber,assignment_group| streamstats current=f last(assignment_group) as lg, last(active) as  Active by CaseNumber |eval ss=case(assignment_group!=lg AND assignment_group="Sustaining","Escalated")|eval comein=strptime(Created_ON,"%Y-%m-%d %H:%M:%S") | eval goout=strptime(Updated_ON,"%Y-%m-%d %H:%M:%S") | eval diff= round((goout - comein)/3600*24,0)|eval total_hours=diff/24|table CaseNumber,Created_ON,Updated_ON,total_hours

I want get the hours counted for escalated cases only, but I am getting counts for both escalated and resolved with the above query(assignment_group is the group).

Please help to get the correct results.

Re: How do you find the count of hours between two dates?

renjith_nair — Tue, 19 Feb 2019 14:03:39 GMT

@ramesh12345,

What about adding |where ss="Escalated" at the end of your search?

Re: How do you find the count of hours between two dates?

ramesh12345 — Tue, 19 Feb 2019 14:07:50 GMT

Sorry i didnt get u clearly

Re: How do you find the count of hours between two dates?

renjith_nair — Tue, 19 Feb 2019 14:13:15 GMT

if you only want the result of only "Escalated" cases, you can filter them by adding where condition. In your search you have field ss which is assigned with "Escalated" based on some condition. So try this,

index="os" sourcetype="Service" CaseNumber= status="Complete" assignment_group= |dedup CaseNumber,assignment_group| streamstats current=f last(assignment_group) as lg, last(active) as Active by CaseNumber |eval ss=case(assignment_group!=lg AND assignment_group="Sustaining","Escalated")|eval comein=strptime(Created_ON,"%Y-%m-%d %H:%M:%S") | eval goout=strptime(Updated_ON,"%Y-%m-%d %H:%M:%S") | eval diff= round((goout - comein)/3600*24,0)|eval total_hours=diff/24|table CaseNumber,Created_ON,Updated_ON,total_hours
|where ss="Escalated"

If this is not what you are looking for, then please provide some sample data (anonymize confidential information) from current output and expected output

Re: How do you find the count of hours between two dates?

ramesh12345 — Tue, 19 Feb 2019 14:16:26 GMT

Not displaying anydata when i given |where ss="Escalated" at the end of the query

Re: How do you find the count of hours between two dates?

renjith_nair — Tue, 19 Feb 2019 14:20:10 GMT

Do you have ss field in your end result and displaying some data? Is it possible to provide some sample output and also the expected output based on that?

Re: How do you find the count of hours between two dates?

somesoni2 — Tue, 19 Feb 2019 14:46:06 GMT

Is this complete query? You eval ss=case seems to have just one condition, is that intended?

Re: How do you find the count of hours between two dates?

ramesh12345 — Tue, 19 Feb 2019 14:58:45 GMT

It's working fine.I just modified my query and added your condition.Thank u so much for your help.

Re: How do you find the count of hours between two dates?

ramesh12345 — Tue, 29 Sep 2020 23:22:49 GMT

Hi, Ihave another issue with my query

index="os" sourcetype="Service" CaseNumber=* assignment_group=* status="Complete" active=false (Group="Connectivity" OR Group="Data") AND (Section="Local" OR Section="data") AND (Component="Power" OR Component="health")|dedup CaseNumber,assignment_group|streamstats current=f last(assignment_group) as lg,last(active) as Active by CaseNumber| eval ss=case(assignment_group!=lg AND assignment_group="Sustaining","Closed By Other",assignment_group="Sustaining" AND (isnull(Active) OR Active="true"),"Closed By Team") |timechart span=1d count by ss usenull=f

when i execute this query it is not displaying "Closed By Other" cases count.

for understanding purpose initially assignment_group="Sustaining" but if this changed to other group then it is called as escalated case.

here within assignment_group="Sustaining" group status="Complete" and active=false then it is resolved cases by Team.It is showing correct count.

But for escalation it is not displaying count for closed cases.i dont know why.Please help hoe to do this

Re: How do you find the count of hours between two dates?

somesoni2 — Tue, 19 Feb 2019 17:03:39 GMT

Check the order and condition in your eval-case. Whatever is the specific case, that condition should be put first.

Re: How do you find the count of hours between two dates?

macadminrohit — Tue, 19 Feb 2019 18:04:20 GMT

why not just have this :

assignment_group="Sustaining","Closed By Other",assignment_group="Sustaining" AND (isnull(Active) OR Active="true"),"Closed By Team")

Re: How do you find the count of hours between two dates?

richgalloway — Tue, 19 Feb 2019 19:51:11 GMT

@ramesh12345 If your problem is resolved, please accept the answer to help future readers.

Re: How do you find the count of hours between two dates?

ramesh12345 — Tue, 29 Sep 2020 23:23:19 GMT

Above condition given same result right?
Because assignment_group is same.i want first assignment_group is "Sustaining" and change in assignment_group is anything.i want that closed cases count.