How to get the count of events per value

[repo12]

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.

[niketn · Accepted solution]

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.

[repo12]

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 !

[niketn]

@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

[repo12]

Thank you ! That worked