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.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...