Splunk Search

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

arkadyz1
Builder

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.

0 Karma
1 Solution

arkadyz1
Builder

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.

View solution in original post

0 Karma

arkadyz1
Builder

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.

0 Karma

woodcock
Esteemed Legend

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
Builder

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?

0 Karma

arkadyz1
Builder

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

dflodstrom
Builder

Does your datamodel tstats search work without groupby?

0 Karma

arkadyz1
Builder

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...