Index Health and Welfare Tstats count

[RobK700000]

I am trying to run a daily report that tells me all the indexes that have had 0 events in the past 24 hours. From other searches I have found the below query which seems to work fine except I want to not include certain indexes that I know will always be a 0 count. So for example if I know indexes named index1, index2 and index3 will always be 0 and want them not to show up in the report how would one accomplish that?

This is the base search without removing any indexes from results. Appreciate any advice thanks.

| tstats count where index=* by index
| append [|rest /services/data/indexes
| dedup title
| fields title
```Discard internal indexes```
| search title!="_*"
| rename title as index
| eval count=0
]
```Merge results, keeping the copy with a non-zero, if present```
| stats max(count) as count by index
| where count==0

[gcusello]

Hi @RobK700000 ,

if they are many, you could use a lookup containing the indexes exception list,

| tstats count where index=* by index
| append [
     |rest /services/data/indexes
     | dedup title
     | fields title
     ```Discard internal indexes```
     | search title!="_*" OR NOT [ | inputlookup indexes_exception_list.csv | fields title ]
     | rename title AS index
     | eval count=0
     ]
```Merge results, keeping the copy with a non-zero, if present```
| stats max(count) AS count BY index
| where count==0

otherwise (if they are few), you could add them to the subsearch:

| tstats count where index=* by index
| append [
     |rest /services/data/indexes
     | dedup title
     | fields title
     ```Discard internal indexes```
     | search title!="_*" OR NOT index IN (index1, index2, index3)
     | rename title AS index
     | eval count=0
     ]
```Merge results, keeping the copy with a non-zero, if present```
| stats max(count) AS count BY index
| where count==0

 Ciao.

Giuseppe

[PickleRick]

s/OR NOT/AND NOT/g

You want to exclude any of those indexes. If you use OR NOT and have more than one index in your list, it will always match the OR NOT condition.

[PickleRick]

You define a lookup (let's call it badindexes.csv) with a single column containing index names to filter out (let's call this column "index")

You can use this lookup to find if you want an index to be filtered out

| lookup badindexes.csv index outputnew index as match

Now you can just leave all results which do not have this field populated

| search match!=*

or

| where isnull(match)