Splunk Search

How to make tstats prestats=true with values() and sum() functions?

piukr
Explorer

I've been using tstats in many queries that I run against accelerated data models, however most of the time I use it with a simple count() function in the following format:

 

| tstats prestats=true count AS count FROM datamodel=... WHERE ... BY ...
| eval ...
| lookup ...
| stats count BY ...

 

This time, I will need to add sum() and values() functions to the tstats, but it seems I am unable to get it working.

If someone could take a look at the queries and let me know what I am doing wrong, that would be great.

The following query (using prestats=false option) works perfectly and produces output (i.e. the reasonduration, sent and rcvd fields all have correct values).

 

| tstats prestats=false
      values(Traffic.reason),
      sum(Traffic.duration),
      sum(Traffic.sent),
      sum(Traffic.rcvd),
      count AS count
   FROM
      datamodel=Network_Log.Traffic
   BY
      _time span=auto
| rename
     "values(Traffic.reason)" AS reason,
     "sum(Traffic.duration)" AS duration,
     "sum(Traffic.sent)" AS sent,
     "sum(Traffic.rcvd)" AS rcvd

 

 

When I try to re-write the above query with the prestats=true option and use stats to summarize on the prestats format,  the reasonduration, sent, and rcvd fields are all null. The count field is calculated correctly and displayed in the statistics table.

 

 

| tstats prestats=true
      values(Traffic.reason),
      sum(Traffic.duration),
      sum(Traffic.sent),
      sum(Traffic.rcvd),
      count AS count
   FROM
      datamodel=Network_Log.Traffic
   BY
      _time span=auto
| rename
     "values(Traffic.reason)" AS reason,
     "sum(Traffic.duration)" AS duration,
     "sum(Traffic.sent)" AS sent,
     "sum(Traffic.rcvd)" AS rcvd
| stats values(reason) AS reason, sum(duration) AS duration, sum(sent) AS sent, sum(rcvd) AS rcvd, count by _time

 

By the way, I followed this excellent summary when I started to re-write my queries to tstats, and I think what I tried to do here is in line with the recommendations, i.e. I repeated the same functions in the stats  command that I use in tstats and used the same BY clause.

https://community.splunk.com/t5/Splunk-Search/What-exactly-are-the-rules-requirements-for-using-quot...

Regards,

Robert

Labels (1)
0 Karma
1 Solution

piukr
Explorer

It might be useful for someone who works on a similar query. I understand why my query returned no data, it all got to do with the field name as it seems rename didn't take effect on the pre-stats fields.

The correct query is the one below.

| tstats prestats=true
      values(Traffic.reason),
      sum(Traffic.duration),
      sum(Traffic.sent),
      sum(Traffic.rcvd),
      count AS count
   FROM
      datamodel=Network_Log.Traffic
   BY
      _time span=auto

| stats values(Traffic.reason) AS reason, sum(Traffic.duration) AS duration, sum(Traffic.sent) AS sent, sum(Traffic.rcvd) AS rcvd, count by _time

 

View solution in original post

piukr
Explorer

It might be useful for someone who works on a similar query. I understand why my query returned no data, it all got to do with the field name as it seems rename didn't take effect on the pre-stats fields.

The correct query is the one below.

| tstats prestats=true
      values(Traffic.reason),
      sum(Traffic.duration),
      sum(Traffic.sent),
      sum(Traffic.rcvd),
      count AS count
   FROM
      datamodel=Network_Log.Traffic
   BY
      _time span=auto

| stats values(Traffic.reason) AS reason, sum(Traffic.duration) AS duration, sum(Traffic.sent) AS sent, sum(Traffic.rcvd) AS rcvd, count by _time

 

Get Updates on the Splunk Community!

Bridging the Gap: Splunk Helps Students Move from Classroom to Career

The Splunk Community is a powerful network of users, educators, and organizations working together to tackle ...

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureThursday, March 27, 2025  |  11AM PST / 2PM EST | Register NowStep boldly ...