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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...