Reporting

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

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)
• ### other

Tags (1)
1 Solution
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
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
Path Finder

Thank you. It worked as expected

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)

Get Updates on the Splunk Community!

#### New This Month - More Capabilities to Simplify GDI Plus Enhancements in Observability ...

The latest enhancements to the Observability Portfolio deliver more assisted onboarding and expanded content ...

#### Ingest Actions Output Announces Support for File System Destinations

Introduction As part of Splunk Enterprise 9.3, we are proud to announce the general availability of File ...

#### Autoscaling Kubernetes Workloads with Splunk

About What if you had the ability to scale your Kubernetes pods as a result of information output from Splunk? ...