Splunk Search

How do I modify my search to get the stats count by a field in lookup?

Builder

I have a lookup file "hosts.csv" as below with multiple fields

**category**    **my_hostname** .. ... ...
A                     abc.com
B                     DEF.com

Now I have the below search that gives the total number of host count that were never reported to Splunk from a lookup table:

| metadata type=hosts | search [| inputlookup hosts.csv  | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup hosts.csv  | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host  | where recentTime=0 | stats dc(host) AS total_hosts 

Now I want to see those host count by a field"category" in a lookup file like below

(for example Assuming total dc(host) is 50)

Category  count 
A                 30
B                 20

Could anyone please suggest the modified search to get the desired result?

0 Karma

Contributor

Hi @pavanae,
Can you check if below query works for you?
index=xyz | lookup hosts.csv hosts AS my_hostname OUTPUT category AS category | stats dc(hosts) AS host BY category

0 Karma

SplunkTrust
SplunkTrust

One thing you want to be sure is that you are retaining the "Category" field when doing the inputlookup. I don't think you need the metadata to start since you are not searching Splunk data but a lookup file instead. It also looks like you add the same lookup table twice and then just dedup. Try this search:

| inputlookup hosts.csv | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host category recentTime lastTime | dedup host category | where recentTime=0 | stats count(host) AS total_hosts by category

I added the category field to the fields command since if you leave it off you will not be able to run stats against it. I added category to the dedup because if you just dedup by host and a host is included in two categories and you count by category a host may be missed if it is deduped just by host. The stats is just an overall count of hosts that fall into each category. The dc(host) should not be necessary if you dedup by host and category.

0 Karma

Builder

Thanks for the response @mdsnmss. But I used the metadata to calculate the list of hosts from the lookup table which never reported to splunk based on the last reporting time from the metadata. Your query just given me the results based on the category field for whole hosts in the lookup.

for example if I have around 1000 hosts from the lookup only around 100 hosts are never reported to splunk. I got that 100 count by using my metadata query. Now I want to categorize those 100 hosts by category field. where category is a field only available in the lookup.

0 Karma