Splunk Search

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

Explorer

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.

Tags (4)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

@ramesh12345,

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Explorer

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

0 Karma

Contributor

why not just have this :

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

0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

@ramesh12345,

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

View solution in original post

0 Karma

Explorer

Sorry i didnt get u clearly

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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

0 Karma