PIVOT vs DATAMODEL vs TSTATS

[mcronkrite]

Why do some splunk users say that the | pivot command isn't for ninjas?
Which is better then, pivot, datamodel, tstats?

[doksu]

The datamodel command does not take advantage of a datamodel's acceleration (but as mcronkrite pointed out above, it's useful for testing CIM mappings), whereas both the pivot and tstats command can use a datamodel's acceleration.

Other than the syntax, the primary difference between the pivot and tstats commands is that pivot is designed to be used only against datamodels and unlike tstats, doesn't require those datamodels to be accelerated (this is a big benefit for shipping app dashboards where you give the customer the choice of accelerating the datamodel or not - as should be done).

tstats is very useful for querying indexed fields outside the context of datamodel use (e.g. | tstats dc(host) WHERE index=* OR index=_* BY _time,index span=15m) and as seen in the example, can take an arbitrary time span aggregation.

The notion that one is better than the other misses the point. They are all useful, and depending on the use case, one may be better suited than another to a particular task. A ninja knows when it's best to use one over another.

[snoobzilla]

| tstats also has the advantage of accepting OR statements in the search so if you are using multi-select tokens they will work. I couldn't get this to work with pivot.

[mcronkrite]

One reason to stay away from the | pivot approach to querying data models is that it performs an ad-hoc acceleration request.
When you do | pivot you are asking for an ad-hoc data model acceleration to be performed. So it becomes an effective | tstats command.

One reason to use | datamodel command is that it is re-applying the search time extractions at run time, so you can test your field mappings.

So do your initial work with | datamodel to validate data , use | tstats in final dashboards to take advantage of acceleration.

One note about | pivot and | tstats , if you open a search in pivot and modify the search to how you want to save it in a dashboard. When you do a "Save to Dashboard Panel" in the WebUI you will get a dashboard panel that uses | pivot version of the query. If you instead go to the Job Inspector and scroll through (near the bottom) you can get the | tstats version instead of the same query instead.

ProTip: Copy this |tstats search instead of the | pivot that to your final dashboard.

[rsennett_splunk]

it's the "optimized search" you grab from Job Inspector. This is a brilliant Pro Tip --- and when I did it I noticed there were several iterations of the search using tstats. that's the one you want.