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

[piukr]

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 reason,  duration, 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 reason,  duration, 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

[piukr · Accepted solution]

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