Splunk Search

Creating a Hierarchical Search

bp2025
Engager

Hello!

I have the following query with the provided fields to track consumption data for customers.

action=load OR action=Download customer!="" publicationId="*" topic="*"
| eval Month=strftime(_time, "%b-%y")
| stats count by customer, Month, product, publicationId, topic
| streamstats count as product_rank by customer, Month
| where product_rank <= 5
| table customer, product, publicationId, topic, count, Month

However, I do not believe it is achieving what I aim for. The data is structured as follows: Products > Publication IDs within those products > Topics within those specific publication IDs. What I am trying to accomplish is find out the top 5 products per customer per month, and then for each of those 5 products find out the top 5 publicationIds within them, and then for each publicationID find out the top 5 topics within them.

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this (essentially, you need to calculate each "top 5" and eliminate the stats events for each level, before calculating the next "top 5" for the next level).

action=load OR action=Download customer!="" publicationId="*" topic="*"
| eval Month=strftime(_time, "%b-%y")
| stats count by customer, Month, product, publicationId, topic
| eventstats sum(count) as product_count by customer Month product
| sort 0 customer Month -product_count
| streamstats dc(product) as product_rank by customer, Month
| where product_rank <= 5
| eventstats sum(count) as publicationId_count by customer Month product publicationId
| sort 0 customer Month product -publicationId_count
| streamstats dc(publicationId) as publicationId_rank by customer Month product
| where publicationId_rank <= 5
| eventstats sum(count) as topic_count by customer Month product publicationId topic
| sort 0 customer Month product publicationId -topic_count
| streamstats dc(topic) as topic_rank by customer Month product publicationId
| where topic_rank <= 5
| table customer, product, publicationId, topic, count, Month

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this (essentially, you need to calculate each "top 5" and eliminate the stats events for each level, before calculating the next "top 5" for the next level).

action=load OR action=Download customer!="" publicationId="*" topic="*"
| eval Month=strftime(_time, "%b-%y")
| stats count by customer, Month, product, publicationId, topic
| eventstats sum(count) as product_count by customer Month product
| sort 0 customer Month -product_count
| streamstats dc(product) as product_rank by customer, Month
| where product_rank <= 5
| eventstats sum(count) as publicationId_count by customer Month product publicationId
| sort 0 customer Month product -publicationId_count
| streamstats dc(publicationId) as publicationId_rank by customer Month product
| where publicationId_rank <= 5
| eventstats sum(count) as topic_count by customer Month product publicationId topic
| sort 0 customer Month product publicationId -topic_count
| streamstats dc(topic) as topic_rank by customer Month product publicationId
| where topic_rank <= 5
| table customer, product, publicationId, topic, count, Month
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...