How to get a list of all hosts across all indexes if we cannot use index=* (restricted by workload rule)

[mlevsh]

Hi,

We need to find all the hosts across all the indexes , but we cannot use index=* anymore, as it's use is  restricted by workload rule.

Before the following command was used
| tstats count where index=*  by host
|fields - count

But it uses index* and now we cannot use it.
Will appreciate any ideas. 

[gcusello]

Hi @mlevsh,

the easiest way is asking to remove that rule because it isn't useful!

Anyway, you should list all the existing indexes in the WHERE condition:

| tstats count where index IN (index1,index2,index2) by index host
| fields - count

to avoid to repeat this list in every command, you could also put all these indexes in a macro or an eventtype and use it in your searches.

Ciao.

Giuseppe

[mlevsh]

@gcusello 

Hi!

Thank you for your advice!

(1) It will be kind of difficult to list all 280  indexes. We can probably decrease it to 68 by using
something like index=p*
I was wondering if there might be another alternative way to do it without listing all the indexes
in search of in macro 

(2) The rule is actually useful to us, since we had few issues with performance due to users
using index=*  , selecting big time period and searching for some "text" through all of our 280+ indexes

But just curious on why are you saying it isn't useful?

Regards,
@mlevsh 

[gcusello]

Hi @mlevsh,

maybe you should try to have a different approach in indexes creation: usually different indexes are used when there are different retention periods and/or different access grants.

Indexes are siloes in which it's possible to store data, different data are differentiated by sourcetype not by index.

So you could reduce the number of indexes: 280 indexes are very difficoult to manage and to use, why do you have so many indexes?

In other words there isn't any sense  having one sourcetype in one index.

In other words, indexes aren't database tables.

the best approach is usually to limit the time that a user can use in a search and not the indexes.

Ciao.

Giuseppe