Framing query with tstats command

[RanjiRaje]

Hi, Can anyone please help me to frame the SPL script.

I have to collect the list of devices reporting in splunk along with the indexname. For that I am using tstats command.

|  tstats count where index=* by host,index 

Now the problem is, for an index the device name is under fieldname 'asset'.  To get such list from this index, I can't able to use tstats command since it works only for metafields.

I tried using stats command but it is taking very long time which is impacting the performance.

Please suggest me how should I frame the query in efficient manner for this case.

Thanks

[JohnEGones]

Just some quick points:

--fields are specific to the index they are apart of, they may not exist across indexes (though this is less problematic if your data is properly normalized.)
--have you done the queries to interrogate the data, as it exists, in your environment and identified which indexes and fields you want exist as needed (cf. PickleRick's comment about use case)?

Some other, hopefully relevant, thoughts (and which, from my experience, are sometimes useful in providing food for thought and context--especially for greener admins like me.)

To add to the other relevant responses, and more generally in working with Splunk, and this does depend on whether you are a user or an admin (and even here this may mean different things depending on your organization) and trying to craft queries about data in your environment, it matters how you are configuring the ingestion (including, depending, the related architecture, like if there is a syslog server, or some TA needed), setting up the indexes, configuring what counts as a source (a hint that there is a ton of customizability to Splunk), setting a schema for hostnames , either auto-extracted during ingestion or otherwise configured in a CONF or using a look-up.

Because there is a fairly large degree of customizability and arbitrariness in configurations (which may simply more reflect your environment (and its architecture), what your business wants/needs, etc.), what is being ingested, how it is labeled (are you specifying this, setting a schema, or just letting a TA or Splunk figure it out), whether there are standards for anything (internal to your organization or company policy etc.), it can sometimes be hard to give specific advice outside of you spelling out all of the particulars. In your case, some better sense of what is indexed, tagged and what fields are available, per index, since the fields exist inside of the index, per source, rather than necessarily being standard (which is helped by following CIM normalization best practices), will help you enormously in taking care of tasks like this.

The larger idea here is to be kind to your future self and to others who have to interact and admin your Splunk environment, follow best practices that make these tasks easier.

REFs:

https://docs.splunk.com/Documentation/CIM/5.3.2/User/UsetheCIMtonormalizedataatsearchtime

https://docs.splunk.com/Documentation/SplunkCloud/latest/Data/Getstartedwithgettingdatain

https://docs.splunk.com/Documentation/SplunkCloud/9.2.2403/Data/Aboutindexedfieldextraction

https://community.splunk.com/t5/Splunk-Enterprise-Security/How-to-use-splunk-to-create-a-CMDB-like-t...

https://splunkbase.splunk.com/

[PickleRick]

True, you can't use tstats with search-time extracted fields. There are other techniques to accelerate working with data. Which one will be appropriate for you depends on your data and use case.

[ITWhisperer]

Is there a 1-2-1 relationship between host and device name? Could you use a lookup after the tstats?

What does your stats search look like? Perhaps there may be ways to optimise it.

[RanjiRaje]

Hi, thanks for your response. devicename is the hostname.

eg: I have configured list of linux servers to send logs to splunk. the servername is shown in the field "host" which is actually the device name (server name). Similarly for windows servers, firewalls, etc

Linux servers: ab, dd, xy ( configuring those devices under index called 'linux')

windows servers: wndw1, wndw2, wndw3 (configuring those devices under index called 'windows')

Fortinet devices: frt1, frt2, frt3 (configuring those devices under index called 'fortinet')

Now, I am using this tstats query |tstats count where index=* by host,index

The result will be: 

host	index	count
ab	linux	10
dd	linux	20
xy	linux	30
wndw1	windows	10
wndw2	windows	20
wndw3	windows	30
frt1	fortinet	10
frt2	fortinet	20
frt3	fortinet	30

Now, I have another set of devices (eg: network devices - ntwk1, ntwk2, ntwk3) but the name of the device is under the field called 'asset'

My tstats query wont pick these network devices list. So need your suggestion how to include those

[isoutamo]

Hi

it's depends are those fields defined on your raw data or not. If they are there then you could try how they have presented in this conf talk. There are some other presentations how to use TERM and PREFIX on your searches.

https://conf.splunk.com/files/2023/slides/PLA1258C.pdf

Another option is create Datamodel and use it. But is it reasonable solution or not depends your real use case.

r. Ismo

[ITWhisperer]

You could create a lookup file to map the host name to the device and then use the lookup command to get the device name.

[RanjiRaje]

I actually don't have the list of devices to create a lookup file. I am taking the reporting list of devices from splunk for compliance report