topic Re: How to use streamstats to display the last current result? in Splunk Search

How to use streamstats to display the last current result?

desslerlee — Thu, 25 Jan 2018 10:05:21 GMT

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!

Re: How to use streamstats to display the last current result?

mayurr98 — Thu, 25 Jan 2018 10:38:28 GMT

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.

Re: How to use streamstats to display the last current result?

somesoni2 — Thu, 25 Jan 2018 21:47:55 GMT

Do you want to add an extra row for each user with latest date (max date +1) and latest (max + 1) PaymentNumber?

Re: How to use streamstats to display the last current result?

DalJeanis — Fri, 26 Jan 2018 00:53:10 GMT

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)