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
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 *
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
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.
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 *
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
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.
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
Are you absolutely sure about that. Take a look at the logic. It is bulletproof. How are you validating it?
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
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.
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
OK, I actually tested it this time and something about the fields beginning with numbers is breaking the foreach
. This will definitely work.
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
ARGH. Only have the bugfix
part got merged into the updated answer. Try this final update.
Hi @woodcock. Thank you for this.
Unfortunately though, still getting blank columns I'm afraid.
Many thanks and kind regards
Chris
I found another typo and fixed it. Does it work now?
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
OK, I found one more but and fixed the original answer. It should work now.
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
So does it work or not?
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