How to search the list of hosts in the "Other" category for a license report?

[edwardrose]

Hello All

I was asked to provide the list of hosts that are reporting in the other category, in the licensing report. Please see the search below:

index=_internal host=ebssplunk-lm.wv.mentorg.com source=*license_usage.log type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx  | search pool="auto_generated_pool_enterprise" | timechart span=1d sum(b) AS volumeB by h fixedrange=false  | join type=outer _time [search index=_internal host=ebssplunk-lm.wv.mentorg.com source=*license_usage.log type="RolloverSummary" earliest=-30d@d  | search pool="auto_generated_pool_enterprise" | eval _time=_time - 43200 | bin _time span=1d | stats latest(poolsz) AS  "pool size" by _time] | fields - _timediff  | foreach * [eval <<FIELD>>=round('<<FIELD>>'/1024/1024/1024, 3)]

It provides the following output:

[somesoni2 · Accepted solution]

Just add "limit=0" in your timechart to avoid "OTHER" column altogether.

index=_internal host=ebssplunk-lm.wv.mentorg.com source=*license_usage.log type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx  | search pool="auto_generated_pool_enterprise" | timechart span=1d sum(b) AS volumeB by h fixedrange=false  limit=0 | join type=outer _time [search index=_internal host=ebssplunk-lm.wv.mentorg.com source=*license_usage.log type="RolloverSummary" earliest=-30d@d  | search pool="auto_generated_pool_enterprise" | eval _time=_time - 43200 | bin _time span=1d | stats latest(poolsz) AS  "pool size" by _time] | fields - _timediff  | foreach * [eval <<FIELD>>=round('<<FIELD>>'/1024/1024/1024, 3)]

Updated
This should give you the license usage for the host which were coming in OTHER columns, with host names are well

Give this a try

index=_internal host=ebssplunk-lm.wv.mentorg.com source=*license_usage.log type="Usage" pool="auto_generated_pool_enterprise"| eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d  | stats sum(b) AS volumeB by _time h | sort 0 _time volumeB | streamstats count as sno by _time | where sno>10 | timechart span=1d sum(b) AS volumeB by h fixedrange=false  limit=0 | foreach * [eval <<FIELD>>=round('<<FIELD>>'/1024/1024/1024, 3)]

If you're just interested in name of the hosts, then remove everything after the where clause in above and add this

   ....| where sno>10  | stats count by host | table host

[edwardrose]

But the request is from management to find out what hosts are included in the other category.