Dashboards & Visualizations

How to keep a field NOT used in a stats command

genesiusj
Builder

Hello,
I'm designing a dashboard, and need a field, receiptDateRange, which is not included in either of my stats commands,

  1. | stats sum(TOTALAMOUNT) AS TOTAMT, sum(ORIG_COS_AMOUNT) AS ORIGCOSAMT, sum(ORIG_FEE_AMOUNT) AS ORIGFEEAMT BY APPLICATIONNAME, RECEIPTDATE
  2. | stats sum(TOTAMT1) AS TOTAMT2, sum(ORIGCOSAMT1) AS ORIGCOSAMT2, sum(ORIGFEEAMT1) AS ORIGFEEAMT2

but MUST be included the resulting summary table.

My time picker searches against the _time field, using RECEIPTDATE, which is a date field. Ex. 11/7/2019.
RECEIPTDATE is converted to epoch time in order for it to be evaluated against time picker tokens, $tok_time.earliest$ and $tok_time.latest$.

The eval command
| eval receiptDateRange=receiptDateStart." TO ".receiptDateEnd
is only in the code one time. I have it listed twice to indicate where I have tried and failed to get the intended results.

<my search>
:
:
| eval convertedRECEIPTDATE=strptime(RECEIPTDATE, "%m/%d/%Y")
| eval receiptDateStart=strftime("$tok_time.earliest$", "%m/%d/%Y") 
| eval receiptDateEnd=strftime("$tok_time.latest$", "%m/%d/%Y") 
| where convertedRECEIPTDATE &gt;= $tok_time.earliest$ AND convertedRECEIPTDATE &lt;= $tok_time.latest$ 

| eval receiptDateRange=receiptDateStart."  TO  ".receiptDateEnd

| dedup TOKEN 
| rename RPT_ORIG_COS_AMOUNT AS ORIGCOSAMT, RPT_ORIG_FEE_AMOUNT AS ORIGFEEAMT 

| stats sum(TOTALAMOUNT) AS TOTAMT, sum(ORIG_COS_AMOUNT) AS ORIGCOSAMT, sum(ORIG_FEE_AMOUNT) AS ORIGFEEAMT BY APPLICATIONNAME, RECEIPTDATE 

| eval TOTAMT1=round(TOTAMT,2)
| eval ORIGCOSAMT1=round(ORIGCOSAMT,2)
| eval ORIGFEEAMT1=round(ORIGFEEAMT,2)

| eval receiptDateRange=receiptDateStart."  TO  ".receiptDateEnd

| stats sum(TOTAMT1) AS TOTAMT2, sum(ORIGCOSAMT1) AS ORIGCOSAMT2, sum(ORIGFEEAMT1) AS ORIGFEEAMT2

| eval TOTAMT2="$$".tostring(TOTAMT2,"commas")
| eval ORIGCOSAMT2="$$".tostring(ORIGCOSAMT2,"commas")
| eval ORIGFEEAMT2="$$".tostring(ORIGFEEAMT2,"commas")
| eval ALLAPPS=""
| rename ALLAPPS AS "All Credit Card Applications", receiptDateRange AS "Receipt Date", TOTAMT2 AS "Total Amount", ORIGCOSAMT2 AS "Total Original Cost Amount", ORIGFEEAMT2 AS "Total Transaction Fees"
| table "All Credit Card Applications", "Receipt Date", "Total Amount", "Total Original Cost Amount", "Total Transaction Fees"

When I run this in the search app, receiptDateRange is in the field list and has the correct value. I'm just not able to get that value into my summary table.

I checked Answers for some help, but all seem to say not possible. But I'm hoping someone, perhaps a Fez, has figured a viable workaround, without sacrificing time and CPU, to resolve this.

Thanks in advance and God bless,
Genesius

0 Karma
1 Solution

jpolvino
Builder

Problem is, stats "discards" all fields that existed prior. One trick is to use the first aggregation function. For example:

| eval receiptDateRange=receiptDateStart."  TO  ".receiptDateEnd
| dedup TOKEN 
| rename RPT_ORIG_COS_AMOUNT AS ORIGCOSAMT, RPT_ORIG_FEE_AMOUNT AS ORIGFEEAMT 
| stats first(receiptDateRange) AS receiptDateRange sum(TOTALAMOUNT) AS TOTAMT, sum(ORIG_COS_AMOUNT) AS ORIGCOSAMT, sum(ORIG_FEE_AMOUNT) AS ORIGFEEAMT BY APPLICATIONNAME, RECEIPTDATE

View solution in original post

0 Karma

jpolvino
Builder

Problem is, stats "discards" all fields that existed prior. One trick is to use the first aggregation function. For example:

| eval receiptDateRange=receiptDateStart."  TO  ".receiptDateEnd
| dedup TOKEN 
| rename RPT_ORIG_COS_AMOUNT AS ORIGCOSAMT, RPT_ORIG_FEE_AMOUNT AS ORIGFEEAMT 
| stats first(receiptDateRange) AS receiptDateRange sum(TOTALAMOUNT) AS TOTAMT, sum(ORIG_COS_AMOUNT) AS ORIGCOSAMT, sum(ORIG_FEE_AMOUNT) AS ORIGFEEAMT BY APPLICATIONNAME, RECEIPTDATE
0 Karma

genesiusj
Builder

@jpolvino
That worked like a charm. 🙂

I did have to make a modification elsewhere in my code because of how Splunk handles date ranges.
If I select between Nov 6, 2019 and Nov 7, 2019 on the time picker, the receiptDateRange field will return as
11/06/2019 TO 11/08/2019

This is very misleading. Splunk is actually using Midnight 11/08/2019 (in essence NO 11/08/2019) as the lastest from the time picker..

Therefore, I changed one bit of code (besides what you gave me).

| eval receiptDateEnd=strftime($tok_time.latest$-86400, "%m/%d/%Y") 

I subtracted one days worth of seconds (86,400) from the token $tok_time.latest$. This results in
11/06/2019 TO 11/07/2019

And since receiptDateRange is only a "text field" and is not used in any calculations, I don't foresee any issues.
However, please advise if you think there might be based on the code from my original post.

Thanks and God bless,
Genesius

Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...