topic Re: How to convert index query in to tstats in Alerting

How to convert index query in to tstats?

NDabhi21 — Mon, 28 Mar 2022 15:46:47 GMT

How to convert below query where summarization status is unknown .

| index="netsec_firewall" sourcetype="pan:traffic" action="allowed" app:technology="client-server" | stats first(start_time) AS start_time count by app user src_ip src_host dest_ip dest_host dest_port duration

Re: How to convert index query in to tstats

richgalloway — Sun, 27 Mar 2022 14:04:40 GMT

The query cannot be converted into a tstats query unless the action, app, user, src_ip, src_host, dest_ip, dest_host, dest_port, duration, and start_time fields are all indexed. Do you know that they are?

Re: How to convert index query in to tstats

NDabhi21 — Sun, 27 Mar 2022 14:14:39 GMT

Hi Richgalloway,

Thanks for the quick response.

Data has been indexed , Field mentioned in stats command are indexed and receiving data .

As its index query its failing for long time i.e last 24 hour .

Re: How to convert index query in to tstats

richgalloway — Sun, 27 Mar 2022 18:13:16 GMT

It's not enough for data in the named fields to be in an index. The field names themselves must be indexed. IOW, the fields must be extracted at index time rather than at search time.

To determine if a field is available for use in tstats, use this query. Values shown in the term column can be used by tstats.

| walklex index=foo | stats count by term

Re: How to convert index query in to tstats

PickleRick — Sun, 27 Mar 2022 21:34:22 GMT

It's also worth adding that indexed fields are a special case and even though in specific cases they give search/stats performance boost they have their downsides and should be used sparringly.

There are other ways of acceleration that are worth considering (accelerated reports and datamodels).

Re: How to convert index query in to tstats

VatsalJagani — Mon, 28 Mar 2022 06:03:38 GMT

I think the best option you have hear is to create data-model and use data-model acceleration and then we can help you write tstats query based on the data-model structure.

Re: How to convert index query in to tstats

NDabhi21 — Mon, 28 Mar 2022 07:47:06 GMT

Hi Vatsal,

Thanks for the your valuable response.

If possible could you please share best practice guide for create data model .

Re: How to convert index query in to tstats

PickleRick — Mon, 28 Mar 2022 07:17:55 GMT

With many kinds of data there are already datamodels defined in Common Information Model

https://splunkbase.splunk.com/app/1621/

The CIM model is heavily used and relied on in many places (especially in Enterprise Security and Security Essentials) so it's good to get to know it.

Many well-written addons provide CIM-compliance which means they do proper field aliasing and recalculations so you can easily add your indexes to CIM indexes.

The Palo Alto add-on seems to be CIM-compliant so it should be relatively easy to use it.

Now all you have to worry about is the acceleration period (there is of course always a trade-off between tstats-able period and disk usage)

Re: How to convert index query in to tstats

NDabhi21 — Mon, 28 Mar 2022 07:38:44 GMT

Hi richgalloway ,

Thanks for the your valuable response.

With this query no result .

Is any way accelerate normal index query or what change need avoid getting canceled .

Re: How to convert index query in to tstats

NDabhi21 — Mon, 28 Mar 2022 07:45:22 GMT

Hi PickleRick,

Thanks for your update.

I will try above mentioned app and let you know the update .

And Is there any way accelerate normal index query or what change need to perform avoid index query getting canceled?

Re: How to convert index query in to tstats

PickleRick — Mon, 28 Mar 2022 08:58:41 GMT

You can create a report and accelerate it so that splunk holds pre-computed summary and doesn't run the search across raw data every time you run the report.

https://docs.splunk.com/Documentation/Splunk/8.2.5/Report/Acceleratereports