Dashboards & Visualizations

Suppress part of search if pulldown is not updated

redc
Builder

I have a form with two Pulldown objects (product and job title). These two things have overlap (e.g., someone could have purchased a product AND have their job title set), but there are also conditions in which there is no overlap (e.g., someone could have purchased a product and NOT have their job title set). If the Splunk user selects product X (product="X"), then it should include everyone who has purchased that product, regardless of their job title status.

The default search result that comes up when you load the form is:

index="usage" | table ReaderUserKey, PageType, PageViewed | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

(Shows all of the pages viewed, broken down by total page views and distinct readers by PageType, doesn't care about product or job title.)

If the Splunk user selects a job title but NOT a product, the search should be:

index="usage" | table ReaderUserKey, PageType, PageViewed | 
join ReaderUserKey [search index="registrations" $selectedJobTitle$ | table ReaderUserKey ] | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

If they select a product and NOT a job title, the search should be:

index="usage" | table ReaderUserKey, PageType, PageViewed | 
join ReaderUserKey [ search index="sales" $selectedProduct$ | eval ReaderUserKey=UserID | table ReaderUserKey ] | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

If they select BOTH a product and a job title, the search should be:

index="usage" | table ReaderUserKey, PageType, PageViewed | 
join ReaderUserKey [ search index="sales" $selectedProduct$ | eval ReaderUserKey=UserID | table ReaderUserKey ] | 
join ReaderUserKey [search index="registrations" $selectedJobTitle$ | table ReaderUserKey ] | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

Currently, this last search is what always runs, regardless of whether you select either job title, product, or both. That means that the results will always include ONLY people who have both purchased any product (product="*") and set their job title (jobtitle="*"). Since these two items don't always overlap, it skews the results.

Is this a case where post-processing would make the most sense? If so, how would I set that up? I haven't done anything with post-processing yet, except look at the documentation (and I'm not really sure what I'm doing after reading the documentation).

0 Karma
1 Solution

redc
Builder

I think I figured this one out.

index="usage" | eval product="" | eval jobtitle="" | 
table ReaderUserKey, PageType, PageViewed, product, jobtitle | 
join ReaderUserKey type=outer [ search index="sales" | eval ReaderUserKey=UserID | table ReaderUserKey, ProdID | stats values(product) as product by ReaderUserKey ] | 
join ReaderUserKey type=outer [search index="registrations" | table ReaderUserKey, jobtitle ] | 
search $selectedProduct$ $selectedJobTitle$ | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

This sets all of the variables that can be searched for to empty values to start with (so then jobtitle="*" can return empty values). Doing "stats values(product)" makes a multivalued field so that all of the products a given reader has purchased will always appear (and then the product search is wrapped in asterisks to allow the multivalued field to be searched, product="*123*"). This can then be done for any multivalued field that needs to be searched against.

It's kind of ugly, maybe there's a better way to do it, but this seems to work.

View solution in original post

redc
Builder

I think I figured this one out.

index="usage" | eval product="" | eval jobtitle="" | 
table ReaderUserKey, PageType, PageViewed, product, jobtitle | 
join ReaderUserKey type=outer [ search index="sales" | eval ReaderUserKey=UserID | table ReaderUserKey, ProdID | stats values(product) as product by ReaderUserKey ] | 
join ReaderUserKey type=outer [search index="registrations" | table ReaderUserKey, jobtitle ] | 
search $selectedProduct$ $selectedJobTitle$ | 
stats count(PageViewed) as PageCount, dc(ReaderUserKey) as ReaderCount by PageType

This sets all of the variables that can be searched for to empty values to start with (so then jobtitle="*" can return empty values). Doing "stats values(product)" makes a multivalued field so that all of the products a given reader has purchased will always appear (and then the product search is wrapped in asterisks to allow the multivalued field to be searched, product="*123*"). This can then be done for any multivalued field that needs to be searched against.

It's kind of ugly, maybe there's a better way to do it, but this seems to work.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...