is it possible to get list of sourcetype by host and index irrespective of time range?
I just want the list of index, host and sourcetype for which events are available, even if there is only one event in last 6 months for any sourcetype/host/index.
The environment is very huge with 130K+ host sending data, the below query only returns the list if any event is there in selected time range,
| tstats values(sourcetype) as sourcetype where index=* by index host | outputlookup host_list.csv
If there any other faster/efficient way to get the results?
Let me know if any other details are required.
tstats is an extremely efficient command. I can not think of any quicker way to do it. I was able to run the query below in 20 seconds in our environment (note that it is running for all time):
| tstats values(sourcetype) as sourcetype where index=* earliest=0 by index host
| tstats count where index=* earliest=0 by index host sourcetype
gives you a more tabular result without the multivalue field. It is sometimes easier to manipulate depending on what you are trying to do with the data.
What is "forever"? The only way to further optimize it is by time. Do you really care about one year ago? Six months ago? One month ago?
If you need this data, this is the way to do it (I do prefer @hunderliggur's query to the OP query, but it should not give any noticeable performance benefit).
Not sure if this would be faster than tstats. Give this a try.
| rest servicesNS/-/-/data/indexes | where totalEventCount>0 | table title | map [| metadata index=$title$ type=hosts | eval index="$title$"] | stats values(host) as hosts by index delim="," | map [| metadata index=$index$ type=sourcetypes | eval index="$index$", hosts="$hosts$"] | stats values(hosts) as hosts, values(sourcetype) as sourcetypes by index | eval hosts=split(hosts,",")
Run the search for "All Time"
Considering that this is using the metadata tag, it should theoretically work faster.
Hope this helps.