topic Re: How to get tstats results independent of time range in Splunk Search

How to get tstats results independent of time range

gaurav_maniar — Wed, 04 Sep 2019 21:50:27 GMT

Hi All,

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.

Re: How to get tstats results independent of time range

jacobpevans — Wed, 04 Sep 2019 21:58:35 GMT

Greetings @gaurav_maniar,

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

Re: How to get tstats results independent of time range

hunderliggur — Wed, 04 Sep 2019 22:39:34 GMT

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

Re: How to get tstats results independent of time range

gaurav_maniar — Thu, 05 Sep 2019 12:07:28 GMT

If I set the time range to ALL, query takes forever to execute.
Is there any other efficient way to do it? I just need the list

Re: How to get tstats results independent of time range

jacobpevans — Thu, 05 Sep 2019 14:09:17 GMT

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

Re: How to get tstats results independent of time range

arjunpkishore5 — Mon, 04 Nov 2019 12:29:04 GMT

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.

Cheers