How to convert index query in to tstats?

[NDabhi21]

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

[VatsalJagani]

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.

[NDabhi21]

Hi Vatsal,

Thanks for the your valuable response.

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

[PickleRick]

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)

[NDabhi21]

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?

[PickleRick]

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

[richgalloway]

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?

[NDabhi21]

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 .

[richgalloway]

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

[NDabhi21]

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 .

[PickleRick]

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