Splunk Search

How do you get the yearly count data?

ramesh12345
Explorer

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?

0 Karma
1 Solution

FrankVl
Ultra Champion

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.

View solution in original post

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

ramesh12345
Explorer

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

0 Karma

ramesh12345
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...