topic Re: More Efficient Query Dedup to Stats Distinct Count in Splunk Search

More Efficient Query Dedup to Stats Distinct Count

IRHM73 — Thu, 13 Aug 2015 07:06:20 GMT

Hi, I wonder whether someone could help me please.

I'm using the search below to successfully produce a pie chart with desired results.

tags.transactionName = "Send Email Alert"  auditType="TxSucceeded"  | eval shortForm='detail.formId'."  " | eval shortForm =  substr(shortForm, 1, 6) | sort 0  detail.messageId  | dedup detail.messageId  | chart count by shortForm  | eval pieSlice=shortForm  + " " + count  | fields pieSlice, count

Because this search takes a while to load I'm looking at making this more efficient by removing the dedup element of search, so I've changed this to:

tags.transactionName = "Send Email Alert"  auditType="TxSucceeded"  | eval shortForm='detail.formId'."  " | eval shortForm =  substr(shortForm, 1, 6) | sort 0  detail.messageId  | **stats dc(detail.messageId)**  | chart count by shortForm  | eval pieSlice=shortForm  + " " + count  | fields pieSlice, count

The problem is, is that this no longer produces any results so I've clearly done something wrong.

I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.

Many thanks and kindest regards

Chris

Re: More Efficient Query Dedup to Stats Distinct Count

tom_frotscher — Thu, 13 Aug 2015 08:23:09 GMT

Hi,

if you run your search up to this point:

 tags.transactionName = "Send Email Alert"  auditType="TxSucceeded"  | eval shortForm='detail.formId'."  " | eval shortForm =  substr(shortForm, 1, 6) | sort 0  detail.messageId  | **stats dc(detail.messageId)**

you will see that after the statd dc there is only one field "dc(detail.messageId)" left. Thats why the rest of your search:

| chart count by shortForm  | eval pieSlice=shortForm  + " " + count  | fields pieSlice, count

produces no result. Because it uses fields like count and shortForm. But these fields are no longer existent.

Greetings

Tom

Re: More Efficient Query Dedup to Stats Distinct Count

IRHM73 — Thu, 13 Aug 2015 08:30:36 GMT

Hi Tom, thank you very much for taking the time to reply to my post and for the guidance.

Many thanks and kind regards

Chris

Re: More Efficient Query Dedup to Stats Distinct Count

tom_frotscher — Thu, 13 Aug 2015 08:48:35 GMT

Hi,

i would try something like this:

 tags.transactionName = "Send Email Alert"  auditType="TxSucceeded"  | eval shortForm='detail.formId'."  " | eval shortForm =  substr(shortForm, 1, 6) | sort 0  detail.messageId  | stats dc(detail.messageId) first(shortForm) as shortForm by "detail.messageId" | chart count by shortForm  | eval pieSlice=shortForm  + " " + count  | fields pieSlice, count

to adjust your search. But verify your results.

In general if you use something like this in your search: ...| sort 0 detail.messageId | dedup detail.messageId |.... Try to think about what the search execute here. You first sort a huge list of events, just to throw out the most events with the dedup in the next step. It would ofcourse be faster if you first throw out the most events and after that, sort the now smaller list of events.

Greetings

Tom

Re: More Efficient Query Dedup to Stats Distinct Count

IRHM73 — Thu, 13 Aug 2015 09:18:08 GMT

Hi Tom, thank you for this, it works perfectly. Thank you also for the guidance on the 'sort' and 'dedup' steps and the steps that they should be introduced.

Kind Regards

Chris