topic Re: How to use tstats where with structured data source? in Splunk Search

How to use tstats where with structured data source?

yuanliu — Fri, 23 Sep 2022 18:29:44 GMT

tstats shows an error if I include a JSON field in "where" clause. Same happens to CSV fields. For example, if my source is like

{"host": "<hostname>", "IP": "<IP address>"}

and I do a search

| tstats count where IP = 10.0.0.1

Splunk displays "When used for 'tstats' searches, the 'WHERE' clause can contain only indexed fields. Ensure all fields in the 'WHERE' clause are indexed. Properly indexed fields should appear in fields.conf."

The problem with fields.conf is that it doesn't deal with original data structure. With JSON, there is always a chance that regex will not properly capture a field. With CSV, failure rate is even higher.

Is there some way to do tstats with structured sources? (I notice that despite the warning, tstats still performs OK. But I'd rather users don't see such error message.)

Re: How to use tstats where with structured data source?

richgalloway — Fri, 23 Sep 2022 20:47:02 GMT

I think that message appears because of the minor breakers in the IP address. Try overriding that with the TERM operator.

| tstats count where TERM(IP=10.0.0.1)

Re: How to use tstats where with structured data source?

johnhuang — Fri, 23 Sep 2022 20:50:55 GMT

This sounds like a good use case for datamodels.

Re: How to use tstats where with structured data source?

yuanliu — Fri, 23 Sep 2022 23:08:24 GMT

TERM will not be usable if multiple JSON paths have a node named IP. With CSV, it's even harder, if at all possible, to define a TERM. I will explore datamodel as @johnhuang suggested.