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

piukr — Tue, 22 Feb 2022 17:19:57 GMT

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-tstats/m-p/319801

Regards,

Robert

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

piukr — Tue, 22 Feb 2022 15:57:41 GMT

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

topic Re: How to make tstats prestats=true with values() and sum() functions in Splunk Search

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

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