topic Include literal dataset in search query / Ensure fixed number of rows in result set in Splunk Search

Include literal dataset in search query / Ensure fixed number of rows in result set

trevorreed — Fri, 26 Apr 2024 17:25:38 GMT

Anyone know how to accomplish the Splunk equivalent of the following SQL?

SELECT * FROM (SELECT 'dev' AS env, 0 as value UNION SELECT 'beta' as env, 0 as value UNION SELECT 'prod' as env, 0 as value)

I intend to combine this arbitrary, literal dataset with another query, but I want to ensure that there are rows for 'dev', 'beta', and 'prod' whether or not Splunk is able to find any records for these environments. The reason for this is, I'm trying to create an alert that will trigger if a particular metric is NOT published often enough in Splunk for each of these environments.

Re: Include literal dataset in search query / Ensure fixed number of rows in result set

richgalloway — Fri, 26 Apr 2024 17:36:52 GMT

Finding something that is not there is not Splunk's strong suit. See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

One way to produce the equivalent to that SQL is with makeresults.

<<your search for env (dev,beta, or prod) and count>> | append [| makeresults format=csv data="env,count dev,0 beta,0 prod,0"] | stats sum(count) as count by env

Re: Include literal dataset in search query / Ensure fixed number of rows in result set

trevorreed — Fri, 26 Apr 2024 17:53:37 GMT

The makeresults command is exactly what I needed. Thank you!