Splunk Search

How to get tstats results independent of time range

gaurav_maniar
Builder

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.

0 Karma

arjunpkishore5
Motivator

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

0 Karma

jacobpevans
Motivator

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
Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.

gaurav_maniar
Builder

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

0 Karma

jacobpevans
Motivator

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

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma

hunderliggur
Path Finder
 | 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.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...