I have a list of hosts on a lookup around 40 hosts. For the list of hosts I want to check the list of sourcetypes like below
search 1 :-
host="host1" | stats count by sourcetype
search 2 :-
host="host2" | stats count by sourcetype
and so on upto search 40 which contains the host 40 which is a long process and time consuming one. Instead is there any way to use all the hosts from the lookup tables in a query and display the available sourcetypes for each host?
The lookup file name is ABCD.csv
Also I dont want the stats count I just want the list of sourcetypes for each host like below
host sourcetypes
host1 sourcetype_1
host2 sourcetype2a
sourcetype2b
and so on.
@pavanae
try this,
index=* | inputlookup ABCD.csv host_name as host OUTPUTNEW ip | stats values(sourcetype) by host ip
i hope this helps.
Like this:
| tstats values(sourcetype) WHERE index=* AND [|inputlookup ABCD.csv | table YourHostFieldHere | rename YourHostFieldHere AS host] BY host
Thanks for the response @woodcock but it throws an error as below
Error in 'TsidxStats': WHERE clause is not an exact query
What is the first line of your ABCD.csv
file?
ABCD.csv lookup contains only the hostname, ip_address with the values as below
host_name ip
sajdhd.edf.com 10.32.43.23
HDFGV 11.22.33.44
So I have tried the below query
| tstats values(sourcetype) WHERE index=* AND [|inputlookup ABCD.csv | eval host=lower(host_name) | eval host=mvindex(split(host,"."),0) | table host ] BY host
OK, then this:
| tstats values(sourcetype) WHERE index=* AND [|inputlookup ABCD.csv | table ip | rename ip AS host | appendpipe [|inputlookup ABCD.csv | table host_name | rename host_name AS host ]] BY host
still the same. looks like tstats isn't working on our splunk.
You are getting an error still? The same one? What does "not working" mean exactly?
Yes @woodcock. Still the same error which is below
"error in 'TsidxStats': WHERE clause is not an exact query"
I am using the following query and getting the above error not sure why
| tstats values(sourcetype) WHERE index=* AND [|inputlookup ABCD.csv | eval host_name=lower(host_name) | eval host_name=mvindex(split(host_name,"."),0) | table host_name | rename host_name AS host ] BY host