How would I write a Splunk search to build a table for PASS and FAIL?

[super_edition]

Hello Everyone,

I have below query with which I am trying to build a table showing data for SUCCESS  for sum of statusCode starts with 20* and FAIL for sum of statusCode starts with 4*. 

However with the below query,

index=my_index sourcetype=openshift_logs openshift_namespace=my_ns  openshift_cluster="cluster009"
("message.statusCode"=20* OR "message.statusCode"=4*) 
| search "message.logType"=CLIENT_RES 
| search "message.url"="/shopping/carts/*"  
| timechart span=1h dc("message.tracers.id{}") as count by message.statusCode

 I am getting the table as below:

_time	200	201	400	403
2023-05-28 03:00	400	10	10	11
2023-05-28 04:00	301	99	19	0
2023-05-28 05:00	100	45	11	9

I am expecting table as something like this:

_time	success	fail
2023-05-28 03:00	410	21
2023-05-28 04:00	400	19
2023-05-28 05:00	145	20

Not sure how to change this.

[ITWhisperer · Accepted solution]

Try something like this

index=my_index sourcetype=openshift_logs openshift_namespace=my_ns  openshift_cluster="cluster009"
("message.statusCode"=20* OR "message.statusCode"=4*) 
| eval status=if('message.statusCode'>300,"fail","success")
| search "message.logType"=CLIENT_RES 
| search "message.url"="/shopping/carts/*"  
| timechart span=1h dc("message.tracers.id{}") as count by status