Splunk Search

How to get the count of events per value

repo12
New Member
  1. I have two fields, cid Status and delivery_date. How could I get the total unique count of cids which has Status as DeliveryFailed. My data has multiple values of status as DeliveryFailed for a cid but I would like to get the count only once per cid. Data type of delivery_date is date. following is my sample data

cid=1 Status = 'Delivery failed' delivery_date = 2015-01-01
cid=1 Status = 'Delivery delayed' delivery_date = 2015-02-01
cid=1 Status = 'Delivery failed' delivery_date = 2015-03-01
cid=1 Status = 'Delivery failed' delivery_date = 2015-04-01
cid=2 Status = 'Delivery failed' delivery_date = 2015-01-01
cid=2 Status = 'Delivery failed' delivery_date = 2015-04-01

For the above data I would like to get the TotalDliveryFailedcount as only '2' (1 for cid=1 (even though it has 3 'Delivery failed' events) and 1 for cid=2 (event though it has 2 'Delivery failed' events)

  1. Also, if I have another column phonestatus which have values such as invalid and valid. How can I get get the count of custid's which has atleast one event with status as 'DeliveryFailed' and phone status as 'invalid'

cid=1 Status = 'Delivery failed' phonestatus="Invalid" delivery_date = 2015-01-01
cid=2 Status = 'Delivery failed' phonestatus="Valid" delivery_date = 2015-01-01
cid=3 Status = 'Delivery failed' phonestatus="InValid" delivery_date = 2015-01-01

I would like the output to be Total (DelvieryFailed and Invalid) =2 (i.e cid=1 and cid=3)

Any help is appreciated. Thank you.

Tags (1)
0 Karma
1 Solution

niketn
Legend

You can use disctinct_count() or in short form dc() statistical function.

 <your base search> Status="'Delivery failed'" phonestatus="Invalid"
| stats dc(cid) as TotalDliveryFailedcount 

PS: I have added phonestatus="Invalid" as per your second question. For your first question you can remove the filter.
In your question you have mentioned phonestatus as invalid and in your example as Invalie and InValid... Since, field values are not case sensitive above search should be able to find all scenarios.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

You can use disctinct_count() or in short form dc() statistical function.

 <your base search> Status="'Delivery failed'" phonestatus="Invalid"
| stats dc(cid) as TotalDliveryFailedcount 

PS: I have added phonestatus="Invalid" as per your second question. For your first question you can remove the filter.
In your question you have mentioned phonestatus as invalid and in your example as Invalie and InValid... Since, field values are not case sensitive above search should be able to find all scenarios.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

repo12
New Member

Thanks a lot for the answer.
Also, based on the data in my 1st question, How can I get the count of Cust_Id's which has Status = 'Delivery failed' and Status = 'Delivery delayed' ? Is there a way to generate a graph showing the actual count to cust_id's and cust_id's with Status = 'Delivery failed' and Status = 'Delivery delayed' (i.e to show what percent of cust_id's have Status = 'Delivery failed' and Status = 'Delivery delayed').

Thanks !

0 Karma

niketn
Legend

@repo12.... please take out Status filter from Base Search add Status as the by clause in your query

  <your base search> Status=* phonestatus="Invalid"
 | stats dc(cid) as TotalDliveryFailedcount by Status
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

repo12
New Member

Thank you ! That worked

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...