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

[trevorreed]

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.

[trevorreed]

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

[richgalloway · Accepted solution]

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