Archive

How to get the count of events per value

New Member
  1. I have two fields, cid Status and deliverydate. 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 deliverydate is date. following is my sample data

cid=1 Status = 'Delivery failed' deliverydate = 2015-01-01
cid=1 Status = 'Delivery delayed' delivery
date = 2015-02-01
cid=1 Status = 'Delivery failed' deliverydate = 2015-03-01
cid=1 Status = 'Delivery failed' delivery
date = 2015-04-01
cid=2 Status = 'Delivery failed' deliverydate = 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" deliverydate = 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

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

New Member

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

Thanks !

0 Karma

SplunkTrust
SplunkTrust

@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

New Member

Thank you ! That worked

0 Karma