computing percentage of total against a summary index

[mitchabaza]

I've created a summary index that counts transactions by customer, transaction type, and hour. I'd like to create weekly and daily roll-up totals by customer and transaction type as a percentage of total. For example

Customer TranType WeekNumber Total % of Total
Acme REF 37 14,423 29%
Acme ACT 37 33,241 66%
Acme DEB 37 2,342 5%
Acme REF 38 9,871 40%
Acme ACT 38 12,389 50%
Acme DEB 38 2,311 9%
Mitsara Industries REF 37 12,312 16%
Mitsara Industries ACT 37 23,121 30%
Mitsara Industries DEB 37 41,231 54%
Mitsara Industries REF 38 53,411 54%
Mitsara Industries ACT 38 31,341 32%
Mitsara Industries DEB 38 13,451 14%

Here's the search I have so far. I'm having trouble figuring out how to add a total by week and customer (excluding tran type) to use for the % of Total calculation:

index=summary search_name="Tran Count By Customer"|   table CustomerName psrsvd_gc _time  TransactionType |eval week=strftime(_time, "%U")|rename psrsvd_gc as TransCount |stats sum(TransCount) by TransactionType, week, CustomerName

Here's some sample summary index data:

10/07/2015 00:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="ba02683a-f886-4d37-93b1-c2609be937e6", CustomerName="Acme", TransactionType=REF, psrsvd_gc=36, psrsvd_v=1
10/07/2015 01:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="ba02683a-f886-4d37-93b1-c2609be937e6", CustomerName="Acme", TransactionType=ACT, psrsvd_gc=196, psrsvd_v=1
10/07/2015 02:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="ba02683a-f886-4d37-93b1-c2609be937e6", CustomerName="Acme", TransactionType=DEB, psrsvd_gc=2231, psrsvd_v=1
10/07/2015 00:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="87be9e53-9014-404d-b231-e29d4ab13766", CustomerName="Mistara Industries", TransactionType=REF, psrsvd_gc=321, psrsvd_v=1
10/07/2015 01:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="87be9e53-9014-404d-b231-e29d4ab13766", CustomerName="Mistara Industries", TransactionType=ACT, psrsvd_gc=231, psrsvd_v=1
10/07/2015 02:00:00 -0500, search_name="Tran Count By Customer", search_now=1444280400.000, info_min_time=1444194000.000, info_max_time=1444280400.000, info_search_time=1444280427.755, CustomerGuid="87be9e53-9014-404d-b231-e29d4ab13766", CustomerName="Mistara Industries", TransactionType=DEB, psrsvd_gc=654, psrsvd_v=1

[mitchabaza · Accepted solution]

The key to forming this query was replacing the stats command with eventstats. As I learned, once you issue stats the original data is "gone" and no longer queryable. eventstats performs aggregation without losing the original, raw data

index=summary search_name="Tran Count By Customer" |   table CustomerName psrsvd_gc _time  TranType date|eval week=strftime(_time, "%U")|rename psrsvd_gc as TranCount |eventstats sum(TranCount) as WeeklyTypeTotal by TranCountType, week, CustomerName|eventstats sum(TranCount) AS WeeklyTotal by week, CustomerName|stats values(WeeklyTotal) as TotalTransactions values(WeeklyTypeTotal) as TotalForType by week, TranType|eval PercentageOfTotal=TotalForType /TotalTransactions*100|eval PercentageOfTotal = round(PercentageOfTotal, 1)|chart values(PercentageOfTotal) over week by TranType

Output:

Week MessageType TotalTransactions TotalForType PercentageOfTotal
40 DEB 61951 8382 13.5
40 LAB 61951 7876 12.7
40 REF 61951 551 0.9
40 RAD 61951 1696 2.7
40 XFR 61951 11905 19.2
40 VISA 61951 8 0
40 FIN 61951 58 0.1
40 INT 61951 70 0.1
40 FEE 61951 31405 50.7
41 DEB 179115 26849 15
41 LAB 179115 23881 13.3
41 REF 179115 1757 1
41 RAD 179115 5084 2.8
41 XFR 179115 32249 18
41 VISA 179115 18 0
41 FIN 179115 404 0.2
41 INT 179115 309 0.2
41 FEE 179115 88564 49.4

[woodcock]

Here is how to add a total by week and customer (excluding tran type):

index=summary search_name="Tran Count By Customer" | bucket _time span=1w |stats sum(psrsvd_gc) AS TransCount BY _time CustomerName

[mitchabaza]

thanks woodcock. How would I go about adding this search to the main search clause?

[woodcock]

Just take the text from your question, add a pipe character ( | ) after it, then add my stuff after that.