topic Re: Counting from metadata and inputcsv in Splunk Search

Counting from metadata and inputcsv

DerekKing — Tue, 11 Mar 2014 11:44:03 GMT

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.

Re: Counting from metadata and inputcsv

MuS — Tue, 11 Mar 2014 12:20:40 GMT

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

Re: Counting from metadata and inputcsv

davebrooking — Tue, 11 Mar 2014 14:02:38 GMT

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

Re: Counting from metadata and inputcsv

DerekKing — Tue, 11 Mar 2014 14:26:06 GMT

Thanks Mus - that did indeed get me started.

Re: Counting from metadata and inputcsv

DerekKing — Tue, 11 Mar 2014 14:26:39 GMT

Thanks Dave, Exactly what I was looking for,

Derek.