Splunk Search

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

ramesh12345
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

renjith_nair
SplunkTrust
SplunkTrust

@ramesh12345,

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

Happy Splunking!

View solution in original post

0 Karma

somesoni2
Revered Legend

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

0 Karma

ramesh12345
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

macadminrohit
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

ramesh12345
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

somesoni2
Revered Legend

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@ramesh12345,

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

Happy Splunking!
0 Karma

ramesh12345
Explorer

Sorry i didnt get u clearly

0 Karma

ramesh12345
Explorer

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

0 Karma

renjith_nair
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?

Happy Splunking!
0 Karma

ramesh12345
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

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

renjith_nair
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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...