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

[arkadyz1]

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.

[arkadyz1 · Accepted solution]

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.

[woodcock]

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

[arkadyz1]

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?

[arkadyz1]

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.

[woodcock]

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

[woodcock]

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

[dflodstrom]

Does your datamodel tstats search work without groupby?

[arkadyz1]

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.

[woodcock]

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.