How to sort a table by a time field in text format

genesiusj — Mon, 30 Dec 2019 16:30:39 GMT

Hello,
Here's the problem.

Dashboard - Time picker is used to select a date range.
But this date range is not checked against _time field.
It is checked against a date field RECEIPTDATE in text format: Ex. 11/21/2019.

Thanks and God bless,
Genesius

Re: How to sort a table by a time field in text format

richgalloway — Mon, 30 Dec 2019 18:07:17 GMT

Thanks for sharing, @genesiusj . To help future readers find your solution, please edit your posting into a question and put the solution in an answer. Then accept the answer.

Re: How to sort a table by a time field in text format

genesiusj — Mon, 30 Dec 2019 18:57:20 GMT

Here's the solution.

    | 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$

The results were what I needed, sort of. The rows were not sorted according to the RECEIPTDATE field. And when I manually sort on that field/column in the dashboard, the sort order is incorrect. It is based on text and not date.
11/21/2019
12/2/2019
3/1/2019
3/11/2019
3/2/2019
etc.....

I tried sorting on the convertedRECEIPTDATE,

    | sort convertedRECEIPTDATE

but this did not work, and the column for convertedRECEIPTDATE field was empty. This did not make sense, because when I ran a separate search of this dashboard panel, convertedRECEIPTDATE is a field with valid epoch date values.

I considered adding convertedRECEIPTDATE to my stats command, before the sort and table commands. But thought this might be an issue.

| stats sum(TOTAMT) AS TOTAMT1, sum(ORIGCOSAMT) AS ORIGCOSAMT1, sum(ORIGFEEAMT) AS ORIGFEEAMT1 BY APPLICATIONNAME, RECEIPTDATE, convertedRECEIPTDATE

Does anyone know if this adds any CPU cycles or IOPS (running the same stats on identical BY clauses (RECEIPTDATE and convertedRECEIPTDATE)? However, it does work.

Here is the entire SPL for this panel.

    index=$tok_index$
        AND FAILCODE=N
        AND APPLICATIONNAME=$tok_payApp$
    | 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$
    | dedup TOKEN 
    | eval TOTAMT=coalesce(TOTALAMOUNT, RPT_ORIG_TRANS_TOTAL_AMOUNT) 
    | eval ORIGCOSAMT=coalesce(ORIG_COS_AMOUNT, RPT_ORIG_COS_AMOUNT) 
    | eval ORIGFEEAMT=coalesce(ORIG_FEE_AMOUNT,RPT_ORIG_FEE_AMOUNT) 
    | stats sum(TOTAMT) AS TOTAMT1, sum(ORIGCOSAMT) AS ORIGCOSAMT1, sum(ORIGFEEAMT) AS ORIGFEEAMT1 BY APPLICATIONNAME, RECEIPTDATE, convertedRECEIPTDATE 
    | eval TOTAMT2="$".tostring(round(TOTAMT1,2),"commas") 
    | eval ORIGCOSAMT2="$".tostring(round(ORIGCOSAMT1,2),"commas") 
    | eval ORIGFEEAMT2="$".tostring(round(ORIGFEEAMT1,2),"commas") 
    | rename APPLICATIONNAME AS "Pay Credit Card Application", RECEIPTDATE AS "Receipt Date", TOTAMT2 AS "Total Amount", ORIGCOSAMT2 AS "Original Cost Amount", ORIGFEEAMT2 AS "Transaction Fees Amount" 
    | sort convertedRECEIPTDATE
    | table "Pay Credit Card Application", "Receipt Date", "Total Amount", "Original Cost Amount", "Transaction Fees Amount"

So the moral of the story (problem) is, if you need to sort on a date field, but it is in text format,

Create a new field using eval and strptime.
Use both the new and original fields in the BY clause of your stats command.
sort your results by the new field only.
Leave the new field out of your table command.

Thanks and God bless,
Genesius

topic Re: How to sort a table by a time field in text format in Splunk Search

How to sort a table by a time field in text format

Re: How to sort a table by a time field in text format

Re: How to sort a table by a time field in text format