Counting from metadata and inputcsv

[DerekKing]

Hi,

I have a use case whereby I would like to report how many assets I am monitoring in splunk, as a percentage of the total.

To get the number of reporting hosts I use the search;
| metadata type=hosts index=* | stats count AS reporting_hosts

To get the total number of assets I have, I use a lookup to my asset file
| inputlookup assetinventory | stats count AS total_assets

Both of these work fine in isolation, but what I cant figure out is how I can use both searches at the same time, and return a stacked chart (or even just a percentage single value would be fine).

Regards
Derek.

[davebrooking]

Hi Derek

As both searches return a single value, you could use appendcols to associate the results from both queries, so something like

| metadata type=hosts index=* 
| stats count AS reporting_hosts 
| appendcols [| inputlookup assetinventory | stats count AS total_assets] 
| eval perc=reporting_hosts/total_assets*100

Dave

[DerekKing]

Thanks Dave, Exactly what I was looking for,

Derek.

[MuS]

Hi DereckKing,

you could do something like this:

| metadata type=hosts index=* | stats count(host) as myCount | eval key="foo" | lookup assetinventory key as key OUTPUT value AS newCount | eval myPerc=myCount*100/newCount | table myCount myPerc

I did setup a lookup based on the following dummy lookup file:

key,value
foo,30

this is needed to do some dummy lookup and will get back the total number of your assetinventory. The lookup file can be updated on a nightly base by running a saved search which will search all reporting hosts and updates the lookup with outputlookup

There is probably some other way to achieve this, but this will get you started.

cheers, MuS

[DerekKing]

Thanks Mus - that did indeed get me started.