.....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" ???
@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)
@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)
A sum over what field? Please be a bit more specific in what data you have and what result you're after.
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
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