Dashboards & Visualizations

How to keep a field NOT used in a stats command

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

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

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

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