Archive
Highlighted

Subtotals with Sum

Motivator

Hi, I wonder whether someone can help me please.

I've written the following query:

`wso2_wmf(RequestCompleted)`detail.Context=*test "detail.method"=GET OR "detail.method"=POST tags.path="/payments" OR tags.path="/obligations" OR tags.path="/liabilities" OR tags.path="/returns" OR request.tags.path="/returns"
| replace "organisations/*" with * in detail.Context
| dedup eventId date_mday
| eval end_obligations=case('tags.path' like "%obligations%", 1), end_liabilities=case('tags.path' like "%liabilities%", 1), end_payments=case('tags.path' like "%payments%", 1), end_retrieve=case('tags.path' like "%returns/%", 1), end_submit=case('tags.path' like "%returns", 1)
| rename applicationProductionClientId as ClientID
| fillnull value=0
| eval newtime=strftime(_time,"%B")
| stats sum(end_obligations) as obligations sum(end_liabilities) as liabilities sum(end_payments) as payments sum(end_retrieve) as retrieve sum(end_submit) as submit by newtime ClientID

This produces the following output:

newtime ClientID obligations liabilities payments retrieve submit
February 1234 1 3 5 4 3

February 5675 1 10 2 2 5
January 7890 3 2 2 2 2

But I'd like to produce the following:

ClientID obligations liabilities payments retrieve submit
1234 1 3 5 4 3

5675 1 10 2 2 5
February Total 2 13 7 6 8
7890 3 2 2 2 2
January Total 3 2 2 2 2

I've tried using Streamstats, addcoltotals, and although I think I'm close I can't get this to create the desired output.

Could someone perhaps have a look at this please and offer some guidance on where I've gone wrong.

Many thanks and kind regards

Chris

0 Karma
Highlighted

Re: Subtotals with Sum

Esteemed Legend

Like this:

|makeresults | eval raw="newtime=February,ClientID=1234,obligations=1,liabilities=3,payments=5,retrieve=4,submit=3 newtime=February,ClientID=5675,obligations=1,liabilities=10,payments=2,retrieve=2,submit=5 newtime=January,ClientID=7890,obligations=3,liabilities=2,payments=2,retrieve=2,submit=2"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| table newtime ClientID obligations liabilities payments retrieve submit 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| eval newtime = newtime . " Total"
| streamstats dc(newtime) AS _serial
| multireport
[ rename newtime AS _newtime ]
[ stats sum(*) AS * first(_serial) AS _serial BY newtime
| rename newtime AS _newtime
| eval ClientID = _newtime ]
| fields - newtime
| sort 0 BY _serial ClientID

View solution in original post

Highlighted

Re: Subtotals with Sum

Motivator

Hi @woodcock.

Thank you for coming back to me with this.

Your dummy data and solution work great, but when I amend to include the 'real' field names except for _serial and there is no stats table produced.

For clarity I'm copying my original code snippet up to and inc. | eval newtime=strftime(_time,"%B")

I am then adding your solution to the end. Is this correct please?

Many thanks and regards

Chris

0 Karma
Highlighted

Re: Subtotals with Sum

Esteemed Legend

No, no, no. I see the problem. You cut off your stats which mine needs. Try this (cut and paste entire SPL as-is).

`wso2_wmf(RequestCompleted)`detail.Context=*test "detail.method"=GET OR "detail.method"=POST tags.path="/payments" OR tags.path="/obligations" OR tags.path="/liabilities" OR tags.path="/returns" OR request.tags.path="/returns"
| replace "organisations/*" with * in detail.Context
| dedup eventId date_mday
| eval end_obligations=case('tags.path' like "%obligations%", 1), end_liabilities=case('tags.path' like "%liabilities%", 1), end_payments=case('tags.path' like "%payments%", 1), end_retrieve=case('tags.path' like "%returns/%", 1), end_submit=case('tags.path' like "%returns", 1)
| rename applicationProductionClientId as ClientID
| fillnull value=0
| eval newtime=strftime(_time,"%B")
| stats sum(end_obligations) as obligations sum(end_liabilities) as liabilities sum(end_payments) as payments sum(end_retrieve) as retrieve sum(end_submit) as submit by newtime ClientID

| rename COMMENT AS "DMZ between my stuff and yours"

| eval newtime = newtime . " Total"
| streamstats dc(newtime) AS _serial
| multireport
[ rename newtime AS _newtime ]
[ stats sum(*) AS * first(_serial) AS _serial BY newtime
| rename newtime AS _newtime
| eval ClientID = _newtime ]
| fields - newtime
| sort 0 BY _serial ClientID
0 Karma
Highlighted

Re: Subtotals with Sum

Esteemed Legend

@IRHM73 Check out my last comment. I know the problem.

0 Karma
Highlighted

Re: Subtotals with Sum

Motivator

Hi @woodcock.

Thank you for coming back to me with this. So some success. The table is now visible with all the data shown which is great so thank you.

But, unfortunately there is an issue with the subtotals.

For example, if I run between 31 January and 01 February 2019 here are 10 rows of data then a February subtotal row which is show an incorrect subtotal amount.

There is then say another 10 rows then a January subtotal row again with an incorrect across all columns.

There is then another list of 10 rows without a subtotal row, so it's almost as if it's not grouping the data together properly by the newtime field.

Kindest regards

Chris

0 Karma
Highlighted

Re: Subtotals with Sum

Motivator

Hi @woodcock.

I've been working on this and have got this to work by tweaking the final few lines of the code to:

| eval newtime = newtime . " Total"
 | streamstats dc(newtime) AS _serial
 | multireport
 [ rename newtime AS _newtime ]
 [ stats sum(*) AS * first(_serial) AS _serial BY newtime
 | rename newtime AS _newtime
 | eval ClientID = _newtime ]
 | sort 0 BY _serial
 | fields - newtime

Many thanks for all your help.

Kind Regards

Chris

0 Karma