Datamodel acceleration: datamodel vs. pivot command

[anjafischer]

Hello,

I am trying to improve the performance of some fairly complex searches within my dashboards and have come across the concept of datamodels in splunk and the possibility to accelerate them.

So, I have set up a very basic datamodel, that only contains one root node and all relevant log fields are set up as auto-etraced attributes.

Using the pivot command with the predefined and accelerated (for a month) model I have ssen impressive results...

The original search was:

system=cics type=trxPerf | timechart sum(count) sum(cputot) by region | addtotals *cputot* fieldname=totalCPUTOT

which took 100 seconds to run for all time (which is just a month at the moment).
The pivot search, however:

| pivot Test123 CICS_Root sum(count) sum(cputot) splitrow _time splitcol region | addtotals *cputot* fieldname=totalCPUTOT

takes only 4 seconds!!!

Now, what sounds great, gves me a bit of a hard time since i cannot find the description of the full pivot syntax anywhere. Within my dashboards I am using quire complex searches that contain appencols commands and stats applied to timechart and so on. Is it possible to "translate" this somehow into the pivot search language?

Also, I discovered that there is a datamodel command, but I cannot figure out how to make it work. It parses fine if I run

| datamodel Test123 CICS_Root search | timechart sum(CICS_Root.count) sum(CICS_Root.cputot) by CICS_Root.region | addtotals *cputot* fieldname=totalCPUTOT 

but it takes even longer than my original search without referring to any accelerated data models... (130 seconds)

So, if anyone could point me towards a description of the pivot syntax/commands and/or explain to me the use of the datamodel command, that help would be highly appreciated 🙂

[chandra1347215]

How to use list function with tstats command in splunk

[sfmike]

I think what you're looking for is the tstats command using the prestats flag:

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Tstats

[fabiocaldas]

Indeed tstats does a great job

[mattness]

Your question was a bit unclear about what documentation you have seen on these commands, if any. So I'll begin here: Have you referred to the official documentation of the datamodel and pivot commands?

[anjafischer]

yes, I have seen the official data model and pivot command documentation. Also, I have tried to make the appendcols command work with pivot, unfortunately without success. I‘d also like to know if it is possible to use the lookup command in combination with pivot...

I really wanted to avoid using the pivot GUI because this will end with me clicking everything and trying to figure out what to click to make a query work that I have already written in the normal spunk query language. I#d rather just try to find a "translation" from one syntax to the other...

[aneels_splunk]

The "| datamodel" command never uses acceleration, so it probably won't help you here.

The pivot search command docs are here, but they don't give all the syntax details: http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Pivot

The best way to explore what can be done with the pivot search command is to use the pivot UI to build the table you want, and then open that pivot report in search, which will give you the corresponding pivot search. The set of things possible with the pivot command is exactly the same as the set of things possible with the pivot UI.

[pj]

re the |datamodel command never using acceleration... why not? it would be so much nicer if it did. The |pivot command seems to use an entirely different syntax to the regular Splunk search syntax.