How do I merge values from two fields into key/value pairs in one field?

[luke222010]

We have the following sample event data:

Timestamp=2018-11-27_14:32 Hostname=xxxxx Service=xxxxx Domain=xxxx JVM=xxxsvr01 IP=xx.xx.xx.xx ResponseCodes=200-16

In this event, the 200-16 represents the last minute of data, where there have been a total of 16 occurrences of HTTP 200 codes within the data that has been ingested. We then split this data into two separate Fields; code (200 HTTP code) and codecount (16 total occurrences of 200 HTTP code).

We do this with the following search:

index=sample
| rex field=ResponseCodes "(?<f1>[^-]\d+)"
| rex field=ResponseCodes "(?<f2>(?<=-)\d+)"
| eval fields=mvzip(f1,f2)
| mvexpand fields
| rex field=fields "(?<code>\d+),(?<codecount>\d+)"
| stats sum(codecount) by code

When we run this we are presented with Statistics of the following:

code:        codecount:
200           117319
400           8
404           1
500           22

What we want to achieve is a way of:

1. Splitting off all Response Codes into their own Field, using something like:

   eval ResponseCode2xx=case(like(code, "2%"), "2xx"), ResponseCode4xx=case(like(code, "4%"), "4xx"), ResponseCode5xx=case(like(code, "5%"), "5xx")
   convert num(ResponseCode2xx), num(ResponseCode4xx), num(ResponseCode5xx)

2. Do a sum of codecount just like in the above, but instead of doing that by:

   | stats sum(codecount) by code

   Do something like:
   | stats sum(codecount) by ResponseCode2xx, ResponseCode4xx, ResponseCode5xx

The result of this would be something like the below, when you click on the individual ResponseCode2xx, ResponseCode4xx, or ResponseCode5xx Fields from within a search, you are presented with:

Values             Count
200                  117319

Is this possible?

Basically, the reason we require this is so that we can use the metric of ResponseCode2xx/ResponseCode4xx/ResponseCode5xx within ITSI after splitting by the Entity of the JVM Field within a KPI Base Search, so that we would end up with a count of 2/4/500s under each JVM under specific metrics.

[woodcock]

I still unclear about what you are trying to do but this run-anywhere demo should give you all the bits that you need to build your own solution:

| makeresults 
| eval raw="ResponseCodes=200-10:::ResponseCodes=200-11:::ResponseCodes=200-12:::ResponseCodes=201-10:::ResponseCodes=202-11:::ResponseCodes=203-12:::ResponseCodes=204-10:::ResponseCodes=205-11:::ResponseCodes=206-12:::ResponseCodes=400-40:::ResponseCodes=400-41:::ResponseCodes=400-42:::ResponseCodes=401-40:::ResponseCodes=402-41:::ResponseCodes=403-42:::ResponseCodes=404-40:::ResponseCodes=405-41:::ResponseCodes=406-42"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| kv

| rename COMMENT AS "Everything above generates sample data; everything below is your code"

| rex field=ResponseCodes "(?<ResponseCode>[^-]\d+)-(?<ResponseCount>\d+)"
| stats sum(ResponseCount) AS ResponseCount BY ResponseCode
| appendpipe [ stats sum(eval(if(like(ResponseCode, "2%"), ResponseCount, 0))) AS ResponseCode2xx ]
| appendpipe [ stats sum(eval(if(like(ResponseCode, "4%"), ResponseCount, 0))) AS ResponseCode4xx ]
| appendpipe [ stats sum(eval(if(like(ResponseCode, "5%"), ResponseCount, 0))) AS ResponseCode5xx ]
| eval ResponseCount=coalesce(ResponseCode, ResponseCode2xx, ResponseCode4xx, ResponseCode5xx)
| eval ResponseCode=case(isnotnull(ResponseCode), ResponseCode, isnotnull(ResponseCode2xx), "2xx", isnotnull(ResponseCode4xx), "4xx", isnotnull(ResponseCode5xx), "5xx")
| table ResponseCode ResponseCount

[woodcock]

If I am understanding you correctly, it should be as easy as adding this to your search:

| eval Values = code, Count = codecount

[luke222010]

Thanks for the reply @woodcock - unfortunately not. I have updated the original post with (hopefully) all the details required.

[richgalloway]

It's not clear how your desired results are different from what you have now. Would you please provide an example of your desired output?

[luke222010]

Thanks for the reply @richgalloway - I have updated the original post with (hopefully) all the detail required.