How to find empty indexes in Splunk Cloud?

[StephenD1]

I'm trying to create an alert that looks through a given list of indexes and triggers an alert for each index showing zero results within a set timeframe.

I'm trying with the following search: 

| tstats count where index IN (index1, index2, index3, index4, index5) BY index
| where count=0

But this doesn't work because running the first line on its own only shows the indexes that are not empty and nothing, not even count=0 for the empty index. I also tried 

| tstats count where index IN (index1, index2, index3, index4, index5) BY index
| fillnull count value=0
| where count=0

But that doesn't work either.

The problem is that if "index5", for example, is showing no results, "| tstats count..." doesn't return anything, not even a null result. So something like "| fillnull" is not working at the end because there is no "index5" row to "fillnull". 

I have seen other solutions use 

| rest /services/data/indexes ...

and join or append the searches to each other but since I'm on Splunk Cloud, it doesn't work due to the error "Restricting results of the "rest" operator to the local instance because you do not have the "dispatch_rest_to_indexers" capability". 

The only working solution I have so far is to create an alert for each index I want to monitor with the following search

| tstats count where index=<MY_INDEX>
| where count=0

but I would rather have a single alert running each time with a list that I can change if I need to than multiple searches competing for a timeslot and all that.

I have considered other solutions like providing a lookup table with a list of indexes I want to search and using lookup to compare against the results but that seems too cumbersome. 

Is there a way to trigger an alert for empty indexes from a single given list on Splunk Cloud?

[jawahir007]

 .

[woodcock]

Like this:

| tstats count max(_time) AS _time WHERE index IN("_*", "*")
BY index
| eval which = coalesce(which, "data")
| append [
| rest splunk_server=local /services/data/indexes
| dedup title
| table title
| rename title AS index
| eval which = coalesce(which, "defined") ]
| stats dc(which) AS which_count values(which) AS which first(count) AS count first(_time) AS _time BY index
| search which_count=1

[StephenD1]

I got it with a variation on your solution. Below is the final search for an alert that looks for empty indexes (count=0) from a given list of known indexes ("index1", "index2", "index3", "index4", "index5", "index6", ...etc.).  

| rest splunk_server=local /services/data/indexes
| where title IN ("index1", "index2", "index3", "index4", "index5", "index6")
| table title
| rename title AS index
| join type=left index 
    [| tstats count where index IN ("index1", "index2", "index3", "index4", "index5", "index6") BY index]
| fillnull
| where count=0

That first line with rest command was the key. Thank you!

[scelikok]

Hi @StephenD1,

You can try below shorter version of  @gcusello solution;

| tstats count where index IN (_internal, index2, index3, index4, index5) BY index 
| append 
    [ makeresults 
    | eval index="index1,index2,index3,index4,index5" 
    | eval index=split(index,",") 
    | mvexpand index ] 
| stats sum(count) AS total BY index

[PickleRick]

Actually modern Splunk lets you specify data directly in the makeresults command. So you can directly append

| makeresults annotate=f format=csv data="index,count
index1,0
index2,0
...
indexn,0"
| fields - _time

[gcusello]

Hi @StephenD1 ,

you can use the solution from @richgalloway 

or (if the indexes to monitor are few) modify your search in:

| tstats count where index IN (index1, index2, index3, index4, index5) BY index
| append [
   | makeresults | eval index=index1, count=0 | fields index count) ]
| append [
   | makeresults | eval index=index2, count=0 | fields index count) ]
| append [
   | makeresults | eval index=index3, count=0 | fields index count) ]
| append [
   | makeresults | eval index=index4, count=0 | fields index count) ]
| append [
   | makeresults | eval index=index5, count=0 | fields index count) ]
| stats sum(count) AS total BY index
| where total=0

or create a lookup (called e.g. perimeter.csv) containing the list of indexes to monitor and run:

| tstats count where index IN (index1, index2, index3, index4, index5) BY index
| append [
   | inputlookup perimeter.csv | eval count=0 | fields index count) ]
| stats sum(count) AS total BY index
| where total=0

Ciao.

Giuseppe

[richgalloway · Accepted solution]

In Splunk Cloud, search heads have the same list of index names as indexers so you can use REST without sending to the indexers.

| rest splunk_server=local /services/data/indexes ...

[StephenD1]

I'm accepting this as the solution since this line was the key to creating my final search as seen below. It looks for empty indexes which can be used in an alert as "When number of results is greater than 0" and "Trigger for each result" 

| rest splunk_server=local /services/data/indexes
| where title IN ("index1", "index2", "index3", "index4", "index5", "index6")
| table title
| rename title AS index
| join type=left index 
    [| tstats count where index IN ("index1", "index2", "index3", "index4", "index5", "index6") BY index]
| fillnull
| where count=0