Reporting

Help with streamstats count with previous sum in case of no value

siva_cg
Path Finder

Hi,

I am using streamstats to calculate the rank based on cumulative count per day per category. On few days, a particular category may not appear. So, on those days, I want to have the count for that category from the  previous day. I tried all the arguments of streamstats and other commands with no success. Can someone help me on it please?

I am pasting the code of similar case but for ranking based on the cumulative points for each football match 

 

index=index
| stats sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| rename "Sold To" AS Owner
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank

 

Output

match Owner1 Owner2 Owner3
1 2 (Rank 2, Total: 10) 1 (Rank 1, Total: 15) (Total: 0)
2 1 (Rank 1, Total: 25) 3 (Rank 3, Total: 18) 2 (Rank 2, Total: 20)
3 (Total: 0) 2 (Rank 2, Total: 23) 1 (Rank 1, Total: 30)

 

Expected Output

match Owner1 Owner2 Owner3
1 2 (Rank 2, Total: 10) 1 (Rank 1, Total: 15) 3 (Rank 3, Total: 0)
2 1 (Rank 1, Total: 25) 3 (Rank 3, Total: 18) 2 (Rank 2, Total: 20)
3 2 (Rank 3, Total: 25) 3 (Rank 2, Total: 23) 1 (Rank 1, Total: 30)
Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Your first stats should be a chart command, because stats doesn't give you the null entries you are expecting. You then need to untable the chart after the fillnull to get the stats you were expecting

index=index
| chart sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| untable match Owner Points
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Your first stats should be a chart command, because stats doesn't give you the null entries you are expecting. You then need to untable the chart after the fillnull to get the stats you were expecting

index=index
| chart sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| untable match Owner Points
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank

siva_cg
Path Finder

Thank you. It worked as expected

0 Karma

PickleRick
SplunkTrust
SplunkTrust

You can either modify the intermediate results.

[...]
| streamstats count AS Rank by match
| streamstats current=f window=1 last(Rank) AS prevrank by match
| eval Rank=if(Rank=0,prevrank,Rank)
| xyseries [...]

Or you can modify the final table. But it needs a "matchable" set of fields since you can't match negatively with foreach and you can't (at least I don't know of any way to do so) check field's name in eval.

[...]
| foreach Owner*
   [ <<FIELD>>=if(<<FIELD>> > 0, <<FIELD>>, null()) ]
| filldown Owner*
| fillnull value=0 Owner*

But I'd go with the first one (the streamstats)

0 Karma
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 ...