Search I spoke of in our get together

[Tyrone Vaughn]

Here's the email I got following my attendance at one of the intermediate search break out sessions.  It got a bit more than intermediate hence I asked for the search in an email.

---------------

Hello,

Thank you for attending .conf!

To calculate your best customers based on their purchases across all product lines, you have to calculate their total sales first, then sort, and then figure out how to list each of those customers' best selling products. This is a tad more difficult, but can be done in a fairly elegant way using the list and mvindex functions.

sourcetype="access_combined" action=purchase | stats sum(price) as sales by clientip,product_name | sort -sales  | stats list(product_name) as product_name, list(eval(round((sales),2))) as sales, sum(sales) as totalProductSales by clientip | sort 3 -totalProductSales | eval product_name=mvindex(product_name,0,4) | eval sales=mvindex(sales,0,4) | fields - totalProductSales 

But, the big drawback is you can't chart these values after you use the list function. So, the big challenge is how do we get the same dataset in a chartable format. Brace yourself, this is not a search string for the faint of heart!

sourcetype="access_combined" action=purchase | stats sum(price) as sales by clientip,product_name | sort clientip,-sales | eventstats sum(sales) as totalProductSales by clientip | sort -totalProductSales | streamstats count as eventCount by clientip | where eventCount <=5  | eval clientId = replace(clientip,"\.","") | delta clientId as idchange | eval counter = if(idchange!=0,1,0) | streamstats sum(counter) as totalcount | where totalcount < 3 | table clientip,product_name,sales | xyseries clientip product_name sales