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

[ramesh12345]

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.

[somesoni2]

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

[ramesh12345]

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

[macadminrohit]

why not just have this :

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

[ramesh12345]

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.

[somesoni2]

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

[renjith_nair · Accepted solution]

@ramesh12345,

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

[ramesh12345]

Sorry i didnt get u clearly

[ramesh12345]

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

[renjith_nair]

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?

[ramesh12345]

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

[richgalloway]

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

[renjith_nair]

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