Getting Data In

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

pavanae
Builder

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.

0 Karma

sbbadri
Motivator

@pavanae

try this,

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

i hope this helps.

0 Karma

woodcock
Esteemed Legend

Like this:

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

pavanae
Builder

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

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

pavanae
Builder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

pavanae
Builder

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

pavanae
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...