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

[IRHM73]

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

[woodcock]

BTW, I am testing with this mockup and it works perfectly so start with this and let me know if it works:

index=_* | rename host AS eventId, sourcetype AS request.ClientID
| eval detail.statusCode = tonumber(case(date_hour<2, "500", date_hour<4, "400", date_hour<6, "300", date_hour<8, "200", true(), "100")) + tonumber(case(date_minute<20, "1", date_minute<40, "2", true(), "3"))
| rename COMMENT AS "w2_wmf(RequestCompleted)`request.detail.Context=\"*test\" | dedup eventId"
| rename request.ClientID as ClientID detail.statusCode AS 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 *

[IRHM73]

Hi, @woodcock. Thank you very much for coming back to me with this. It works great!

May I just ask, what this line is doing:

| eval detail.statusCode = tonumber(case(date_hour<2, "500", date_hour<4, "400", date_hour<6, "300", date_hour<8, "200", true(), "100")) + tonumber(case(date_minute<20, "1", date_minute<40, "2", true(), "3"))

Many thanks and kind regards

Chris

[woodcock]

I do not have your data so that line helps me fake it. If the solution works, be sure to click Accept to close it and also UpVote any answers or comments that were valuable.

[woodcock]

OK, I simplified it even more but the previous version should have worked. The key part that you might be doing wrong is this line: [ eval Successful = Successful + coalesce('<<FIELD>>', 0) ]. You MUST use the single-quotes or it will not work right.