topic Re: How come our tstats with datamodel does not group by field? in Splunk Search

How come our tstats with datamodel does not group by field?

arkadyz1 — Wed, 09 Jan 2019 21:59:25 GMT

We have an index with quite a few index-time fields, and an accelerated datamodel that adds a calculated field there. Our objective is to group by one of the fields, find the first and the last value of some other field and compare them. Unfortunately, a usual | tstats first(length) as length1 last(length) as length2 from datamodel=ourdatamodel groupby token does not work.

Just tstats using the index but not the data model works, but it lacks that calculated field that's only in the datamodel, so it does not satisfy our needs.

I can add more precise search strings as replies to clarify things if needed.

Re: How come our tstats with datamodel does not group by field?

dflodstrom — Thu, 10 Jan 2019 03:05:17 GMT

Does your datamodel tstats search work without groupby?

Re: How come our tstats with datamodel does not group by field?

woodcock — Thu, 10 Jan 2019 03:18:34 GMT

The length and token fields are in the datamodel, right? If so, it should be:

| tstats first(Our_Datamodel.length) AS length1 last(Our_Datamodel.length) AS length2
FROM datamodel=ourdatamodel
WHERE index=*
BY Our_Datamodel.token

Re: How come our tstats with datamodel does not group by field?

arkadyz1 — Thu, 10 Jan 2019 15:07:51 GMT

Not sure - never tried it, as we need that by/groupby anyway. I think our mistake was a wrong field notation - we did not realize that datamodel fields are referenced as datamodelname.fieldname. Thanks to @woodcock 's answer below, we realized our mistake.

Re: How come our tstats with datamodel does not group by field?

arkadyz1 — Thu, 10 Jan 2019 15:57:44 GMT

Could you please clarify: if our datamodel is named "Our Datamodel" with internal name Our_Datamodel, it has an event named Event and in it are a couple of fields named length and token, what would be our search string?

Re: How come our tstats with datamodel does not group by field?

woodcock — Thu, 10 Jan 2019 16:13:51 GMT

I have updated my answer with what should probably work. If you post your Our_Datamodel.json then I can say for sure.

Re: How come our tstats with datamodel does not group by field?

woodcock — Thu, 10 Jan 2019 16:15:49 GMT

If an answer or comment has helped you, then you should UpVote it; if it solved it for you, then you should click Accept to close the question.

Re: How come our tstats with datamodel does not group by field?

arkadyz1 — Thu, 10 Jan 2019 17:57:09 GMT

OK, we figured out the exact syntax: our datamodel has an object named "Package", which has such extracted fields as "length", "width", "height" and a calculated "tot_dim" which is a sum of the three dimensions. It also has a "token" field, which for some reason is not extracted properly, so I tested the GROUPBY syntax on some other field.

All in all, it looks like this:

| tstats count first(Package.tot_dim) AS tot_dim1 last(Package.tot_dim) AS tot_dim2
from datamodel=Our_Datamodel
where index=our_index
by Package.token
| search count=2

Shall we agree that some of Splunks intricacies are somewhat underdocumented? 🙂

Oh, and we are still in 6.6.2 - though I doubt it changes much in the latest version.

Re: How come our tstats with datamodel does not group by field?

arkadyz1 — Thu, 10 Jan 2019 18:15:46 GMT

I voted up an answer by @woodcock as it gave us the right idea. Here is the syntax that works:

 | tstats count first(Package.tot_dim) AS tot_dim1 last(Package.tot_dim) AS tot_dim2
 from datamodel=Our_Datamodel
 where index=our_index
 by Package.token
 | search count=2

Now for the details: we have a datamodel named Our_Datamodel (make sure you refer to its internal name, not display name), an object named Package within it and fields named tot_dim and token. The | search count=2 addition is for illustration only - you can pipe the tstats command into many other commands.

Re: How come our tstats with datamodel does not group by field?

woodcock — Thu, 10 Jan 2019 19:01:32 GMT

Perfect. Be sure to UpVote and helpful comments and answers and the click on Accept to the best answer to close the question.