Reporting

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

edwardrose
Contributor

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:

alt text

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Contributor

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...