topic Re: In a table powered by a stats count search, can you help me display status codes as column headings? in Splunk Search

In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Mon, 11 Feb 2019 09:25:34 GMT

Hi,

I wonder whether someone can help me please.

I've put together the following query...

w2_wmf(RequestCompleted)`request.detail.Context="*test"
| dedup eventId
| rename request.ClientId as ClientID
| stats count(eval('detail.statusCode')) as "All", sum(eval('detail.statusCode'<=303)) as "Successful", sum(eval('detail.statusCode'>303)) as "Unsuccessful" by ClientID

...which outputs as follows:

ClientID All Successful Unsuccessful

1111111 3 2 1

What I'd like to do is to expand this and then show all the status codes as column headings after the "Unsuccessful" column heading with the respective counts, but for the life of me, I can't find the solution.

Could someone possible look into this and offer some guidance on how I may be able to do this?

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Mon, 11 Feb 2019 09:51:57 GMT

Like this:

w2_wmf(RequestCompleted)`request.detail.Context="*test"
| dedup eventId
| rename request.ClientID as ClientID detail.statusCode AS statusCode
| eval statusCode = "bugfix" . statusCode
| chart count BY ClientID statusCode 
| addtotals fieldname="All" 
| eval Successful = 0 
| foreach 1* 2* 300 301 302 303 
    [ eval Successful = Successful + coalesce('<<FIELD>>', 0) ] 
| eval Unsuccessful = All - Successful 
| table ClientID All Successful Unsuccessful *

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Mon, 11 Feb 2019 10:04:38 GMT

Hi @woodcock. Thank you for this.

The columns are as I wanted which is great, but the totals of the "Successful" is showing the same figure for all ClientID's which is incorrect and unfortunately the "All" and "Unsuccessful" columns are blank.

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Mon, 11 Feb 2019 10:23:45 GMT

Quite correct, line #5 was wrong. I have edited and fixed it. Try now. That is what I get for doing SPL in my head.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Mon, 11 Feb 2019 10:41:26 GMT

Hi @woodcock. Sorry I thought this had worked.

But Unfortunately the "Successful" and "Unsuccessful", although they have figures in, they are incorrect.

The issue appears to be the "Successful" column which then renders the "Unsuccessful" column incorrect when the calculation takes place.

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Tue, 12 Feb 2019 00:09:40 GMT

Are you absolutely sure about that. Take a look at the logic. It is bulletproof. How are you validating it?

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Tue, 12 Feb 2019 07:26:52 GMT

Hi @woodcock. Thank you for coming back to me with this.

Yes I've re-run the code again, and split it down line by line yesterday afternoon.

I know that that it's the "Successful" column that's incorrect because although I have 53 individual Client ID's, they all show 1206 in the "Successful" column which is incorrect.

In addition, when you look at the the first ClientID, the "All" column shows 45 separate events, yet the "Successful is shown as 1206.

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Tue, 12 Feb 2019 07:32:11 GMT

OK, that did it. I had an error and instead of adding for example, the value of field 301, it was actually adding 301. I edited it again and fixed it. It will work now.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Tue, 12 Feb 2019 08:20:43 GMT

Hi @woodcock. Thank you for coming back to me so quickly with this,

Unfortunately the "Successful" and "Unsuccessful" are now blank.

It's certainly a tricky one is this. I've been working on it for a couple of days now.

Kind Regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Tue, 12 Feb 2019 08:53:34 GMT

OK, I actually tested it this time and something about the fields beginning with numbers is breaking the foreach. This will definitely work.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Tue, 12 Feb 2019 09:59:29 GMT

Hi @woodcock. Thank you for this.

I'm running this through the default "Search & Reporting", but I'm still receiving the blank columns as before. Not sure whether it helps, but I'm using V6.5.7 .

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Tue, 12 Feb 2019 10:05:49 GMT

ARGH. Only have the bugfix part got merged into the updated answer. Try this final update.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Tue, 12 Feb 2019 10:19:08 GMT

Hi @woodcock. Thank you for this.

Unfortunately though, still getting blank columns I'm afraid.

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Wed, 13 Feb 2019 03:03:05 GMT

I found another typo and fixed it. Does it work now?

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Mon, 18 Feb 2019 07:37:57 GMT

Hi @woodcock.

I'm so sorry to have taken so long to come back to you. I had to take some days away. I've tried the query and unfortunately the columns are blank as before.

The issue seems to be around here:

| foreach bugfix1* bugfix2* bugfix300 bugfix301 bugfix302 bugfix303
[ eval Successful = Successful + '<>' ]

Because I've stripped back the code and up to this point the Successful column does show the correct value of zero.

Many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

bowesmana — Wed, 27 Feb 2019 01:29:15 GMT

Dummy encoding should do the trick

w2_wmf(RequestCompleted)`request.detail.Context="*test"
 | dedup eventId
 | rename request.ClientId as ClientID, detail.statusCode as Status
 | eval X_{Status}=1
 | stats count as Total sum(X_*) as X_* by ClientID
 | rename X_* as *

That will give you the total for each client id and then a column for each status code with the number of occurrences of each code.

Does that work for you? The 'dummy encoding' technique of eval {FIELDNAME}=1 will create new event columns for all values found in that column, so is useful for when the data variants are limited

To give an example of how this works using the standard Splunk access logs, if you have access to the _internal index, then you can do this

index=_internal sourcetype=*access
| eval X_{status}=1
| stats count as Total sum(X_*) as X_* by source, user
| rename X_* as *

which would give you something along the lines of

source  user    Total   200 201 204 303 404
/opt/splunk/var/log/splunk/splunkd_access.log   -   3       3            
/opt/splunk/var/log/splunk/splunkd_access.log   admin   70  70               
/opt/splunk/var/log/splunk/splunkd_access.log   splunk-system-user  1358    1328    27  2       1
/opt/splunk/var/log/splunk/splunkd_ui_access.log    -   63  62          1    
/opt/splunk/var/log/splunk/splunkd_ui_access.log    admin   1186    1158    25          3
/opt/splunk/var/log/splunk/web_access.log   -   3   3                
/opt/splunk/var/log/splunk/web_access.log   admin   11  11

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

bowesmana — Wed, 27 Feb 2019 01:46:37 GMT

If you still want the successful/unsuccessful numbers in there as well, then leave in your sum(eval...) stats items, i.e.

| stats count as Total sum(eval(Status<=303)) as "Successful", sum(eval(Status>303)) as "Unsuccessful" sum(X_*) as X_* by ClientID

or should the 303 really be <400 and >=400 - unless you're sure you'll only return up to 303, but there are more in the 300 range above 303.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Sun, 03 Mar 2019 02:39:45 GMT

OK, I found one more but and fixed the original answer. It should work now.

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

IRHM73 — Sun, 03 Mar 2019 13:56:58 GMT

Hi @woodcock. Thank you for coming back to me with this.

All the columns now work except for the Successful column. All figures shown are zero, but there are figures in the successful code columns.

Once again, many thanks and kind regards

Chris

Re: In a table powered by a stats count search, can you help me display status codes as column headings?

woodcock — Sun, 03 Mar 2019 14:52:42 GMT

So does it work or not?