How do you get the yearly count data?

[ramesh12345]

Hi,

index="os" sourcetype="Service" CaseNumber="Test-2018*" (Group="Secure" OR Group="health") AND (Section="Connectivity Problem" OR Section="Local data") AND (Component="connectivity" OR Component="data health")| dedup _time,CaseNumber| stats count by Group,status| xyseries Group,status,count| addtotals|

I want to display the total number of case statuses in the year 2018. For example in 2018, how many cases still "in progress", how many closed, how many waiting like this? But the above query is showing all status information for particular case numbers. One case number life cycle "new",Waiting","Inprogress","Closed". But this query is displaying all status for aparticular case.

How do I do this?

[FrankVl · Accepted solution]

Your question is a bit vague to me. You say your current query displays all status for particular case, while the query actually only shows the count by status and group.

But I guess you want to somewhere filter for the latest event for each case, to get the current status of that case. Doing dedup _time,CaseNumber will get you multiple entries for one case (one for each unique timestamp).

You could do this to count the number of cases for each status in each Group (note the | stats latest(status) as status by CaseNumber,Group instead of your dedup):

index="os" sourcetype="Service" CaseNumber="Test-2018*" (Group="Secure" OR Group="health") AND (Section="Connectivity Problem" OR Section="Local data") AND (Component="connectivity" OR Component="data health")
| stats latest(status) as status by CaseNumber,Group
| stats count by Group,status
| xyseries Group,status,count
| addtotals

If that is not what you're looking for, please provide some example output as you would like to see it.

[ramesh12345]

I am looking for the same thing.Thank you very much it's working fine.

[ramesh12345]

Hi,

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")|dedup _time,CaseNumber|where Created_ON=Updated_ON| eval days = (Now() - _time) /86400| eval days_ago = case(days 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")| chart count by days_ago,Group|sort days_ago

most cases have both (Group="Data/Config" OR Group="Secure").
initially (Group is "Data/Config") then after some time it change to (Group="Secure").when i count by group,it is showing initial Group name for that case.it is not showing to current group.

i want to display count by group with latest group name(i.e case that have latest group that comes under particular group not previous group)

How to do this?