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?
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.
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.