Splunk Search

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

IRHM73
Motivator

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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 *

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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               
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

0 Karma

woodcock
Esteemed Legend

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 *
0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

IRHM73
Motivator

Hi @woodcock. Thank you for this.

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

Many thanks and kind regards

Chris

0 Karma

woodcock
Esteemed Legend

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

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

IRHM73
Motivator

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

0 Karma

woodcock
Esteemed Legend

So does it work or not?

0 Karma

IRHM73
Motivator

Hi @woodcock. No it doesn't unfortunately.

The figures in all of the successful column are shown as zero which is incorrect.

Many thanks

Chris

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...