Hi all,
I am trying to use streamstats to display an event for a particular user, their current Payment Number for this month, and the subsequent Payment number for the next event. (Do note that I sort the date in reverse order).
| sort -TransactDate
| streamstats current=f window=1 last("Payment Number") as NextPaymentNumber
| table Username, "TransactDate","Payment Number", NextPaymentNumber
I almost get the result I want as below:
Username TransactDate Payment Number NextPaymentNumber
Adam 2/2/2017 2 3
Adam 2/1/2017 1 2
However, what I need is something like below:
Username TransactDate Payment Number NextPaymentNumber
Adam 2/3/2017 3 3
Adam 2/2/2017 2 3
Adam 2/1/2017 1 2
Where the record on 2/3/2017 is the latest record, and the latest and maximum Payment number is 3.
Please advice how could I achieve that? Thanks!
Try something like this
your search that gets the payments (most recent first)
| reverse
| streamstats count as payno by Username
| reverse
| streamstats current=f window=1 last(payno) as nextpayno by Username
| eval nextpayno = coalesce(nextpayno, payno)
Try something like this
your search that gets the payments (most recent first)
| reverse
| streamstats count as payno by Username
| reverse
| streamstats current=f window=1 last(payno) as nextpayno by Username
| eval nextpayno = coalesce(nextpayno, payno)
Do you want to add an extra row for each user with latest date (max date +1) and latest (max + 1) PaymentNumber?
i am getting the results with your query!
| makeresults
| eval raw="Adam2/2/201723 Adam2/1/201712 Adam2/3/201733"
| makemv raw
| mvexpand raw
| rex field=raw "(?<Username>[^\d]+)(?<TransactDate>\d\/\d\/\d{4})(?<PaymentNumber>\d)(?<NextPaymentNumber>\d)"
| fields- raw _time | sort- TransactDate | streamstats last("PaymentNumber") as NextPaymentNumber
| table Username, "TransactDate","PaymentNumber", NextPaymentNumber
can you try putting table command | table Username, "TransactDate","Payment Number", NextPaymentNumber
before streamstats
and see if you are getting a proper table with descending transactdate.