Splunk Search

Using conditional sum after case statement

dwong2
New Member

.....search
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed")
| stats count by tile Type
| sort Type

How do you get a separate sum for "%U" and "%S" ???

Tags (1)
0 Karma
1 Solution

@dwong2, can you please try the following search with eventstats to compute Total by Type and add Total to each event for corresponding Type:

<YourBaseSearch>
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
| stats count by tile Type
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)
| sort Type

Following is a run anywhere search based on sample data provided:
PS: Commands from | makeresults till | table tile Type count generate the sample data as per the question.

| makeresults
| fields - _time
| eval data="PosterTile subscribed 2;
Tile1 subscribed 4;
Tile2 subscribed 6;
Tile3 subscribed 2;
Tile4 subscribed 2;
BannerTile unsubscribed 5;
Tile1 unsubscribed 6;
Tile2 unsubscribed 5;
Tile3 unsubscribed 4;
Tile4 unsubscribed 7"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval tile=mvindex(data,0), Type=mvindex(data,1), count=mvindex(data,2)
| table tile Type count
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

@dwong2, can you please try the following search with eventstats to compute Total by Type and add Total to each event for corresponding Type:

<YourBaseSearch>
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
| stats count by tile Type
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)
| sort Type

Following is a run anywhere search based on sample data provided:
PS: Commands from | makeresults till | table tile Type count generate the sample data as per the question.

| makeresults
| fields - _time
| eval data="PosterTile subscribed 2;
Tile1 subscribed 4;
Tile2 subscribed 6;
Tile3 subscribed 2;
Tile4 subscribed 2;
BannerTile unsubscribed 5;
Tile1 unsubscribed 6;
Tile2 unsubscribed 5;
Tile3 unsubscribed 4;
Tile4 unsubscribed 7"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval tile=mvindex(data,0), Type=mvindex(data,1), count=mvindex(data,2)
| table tile Type count
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

FrankVl
Ultra Champion

A sum over what field? Please be a bit more specific in what data you have and what result you're after.

0 Karma

knielsen
Contributor

You could use a different representation if that fits your needs, eg:

.....search
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
| chart count over tile Type
| addcoltotals

More in spirit of the original representation maybe:

.....search
 | eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
 | stats count by tile Type
 | sort Type
 | stats list(Type) as Type list(count) as Count by tile
 | eventstats sum(Count) as Total by tile
  • but there must be something more elegant. 🙂
0 Karma

dwong2
New Member

This is my output now:

tile Type count
PosterTile subscribed 2
Tile1 subscribed 4
Tile2 subscribed 6
Tile3 subscribed 2
Tile4 subscribed 2
BannerTile unsubscribed 5
Tile1 unsubscribed 6
Tile2 unsubscribed 5
Tile3 unsubscribed 4
Tile4 unsubscribed 7

Can't figure out how to sum the subscribed and unsubscribed and the calculate to get an average in percentage.
i.e. for subscribed Tile1/Total tile of subscribed only so 4/16; Tile2/Total tile of subscribed only so 6/16
for unsubscribed Tile1/Total tile of unsubscribed only so 6/27; Tile2/Total tile of unsubscribed only so 5/27

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...