how to get all the available sourcetypes from a list of hosts on a lookup?

[pavanae]

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.

[sbbadri]

@pavanae

try this,

index=* | inputlookup ABCD.csv host_name as host OUTPUTNEW ip | stats values(sourcetype) by host ip

i hope this helps.

[woodcock]

Like this:

 | tstats values(sourcetype) WHERE index=* AND [|inputlookup ABCD.csv | table YourHostFieldHere | rename YourHostFieldHere AS host] BY host

[pavanae]

Thanks for the response @woodcock but it throws an error as below

Error in 'TsidxStats': WHERE clause is not an exact query

[woodcock]

What is the first line of your ABCD.csv file?

[pavanae]

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

[woodcock]

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

[pavanae]

still the same. looks like tstats isn't working on our splunk.

[woodcock]

You are getting an error still? The same one? What does "not working" mean exactly?

[pavanae]

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