Splunk Search

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

luke222010
Engager

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.

0 Karma

woodcock
Esteemed Legend

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

woodcock
Esteemed Legend

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

| eval Values = code, Count = codecount
0 Karma

luke222010
Engager

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.
0 Karma

luke222010
Engager

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

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 ...