topic Re: How to get the field value substring? in Splunk Search

How to get the field value substring?

mcohen13 — Tue, 19 Jun 2018 08:22:33 GMT

I have a field whose values show DNS query information
for example:

[{"type":"A","**response**":"204.2.232.240","asn":"2914","asname":"N/A"}]

The field name is named "response"
I want to put in a different field all of the different response values (in bold)
this field could contain more than 1 response value so I want them all to be in a new field with "," delimiter
how do I do that?

Re: How to get the field value substring?

FrankVl — Tue, 19 Jun 2018 08:53:54 GMT

Can you also provide samples of events that contain multiple responses? Bit hard to come up with a regular expression based on just this simple example, while you need it to work also on more complex data apparently.

Re: How to get the field value substring?

mcohen13 — Tue, 19 Jun 2018 10:20:40 GMT

[{"type":"N/A","response":"N/A","asn":"N/A","asname":"N/A"}]

[{"type":"A","response":"172.20.2.105","asn":"reserved","asname":"reserved"}]
[{"type":"A","response":"172.22.36.3","asn":"reserved","asname":"reserved"}]
[{"type":"A","response":"172.28.76.33","asn":"reserved","asname":"reserved"}]

[{"type":"A","response":"204.2.232.240","asn":"2914","asname":"N/A"}]
[{"type":"A","response":"2.16.76.110","asn":"20940","asname":"N/A"}]

[{"type":"A","response":"153.254.159.149","asn":"2914","asname":"N/A"}]
[{"type":"A","response":"209.99.64.18","asn":"40034","asname":"N/A"}]

[{"type":"A","response":"64.124.235.203","asn":"6461","asname":"N/A"}]
[{"type":"A","response":"67.225.218.50","asn":"32244","asname":"N/A"}]
[{"type":"A","response":"63.237.67.237","asn":"209","asname":"qwest"}]

[{"type":"A","response":"184.84.165.252","asn":"20940","asname":"N/A"}]
[{"type":"A","response":"23.55.56.71","asn":"20940","asname":"N/A"}]
[{"type":"A","response":"96.17.148.189","asn":"20940","asname":"N/A"}]

[{"type":"A","response":"104.110.189.77","asn":"20940","asname":"N/A"}]
[{"type":"A","response":"2.16.165.55","asn":"20940","asname":"N/A"}]
[{"type":"A","response":"104.79.196.66","asn":"20940","asname":"N/A"}]

Re: How to get the field value substring?

mcohen13 — Tue, 19 Jun 2018 10:21:40 GMT

[{"type":"A","response":"52.7.102.57","asn":"14618","asname":"aws"},{"type":"A","response":"52.71.245.135","asn":"14618","asname":"aws"}]

Re: How to get the field value substring?

FrankVl — Tue, 19 Jun 2018 11:09:35 GMT

Try the following. It triggers on the { character and then skips the 2 parts after that ("type" and "A" in your examples) and then extracts the next word. It will keep matching and adding to a multivalued field. Then the mvjoin command is used to translate that multivalued field into a comma separated field as you requested.

...your base search to get to this data...
| rex field=response "\{\"\w+\":\"\w+\",\"(?<response_values>[^\"]+)" max_match=0
| eval response_values = mvjoin(response_values,",")

See https://regex101.com/r/LwxZmR/1 for confirmation that the regex works with your samples.

Re: How to get the field value substring?

mcohen13 — Tue, 19 Jun 2018 14:01:46 GMT

tried and it's not working

Re: How to get the field value substring?

FrankVl — Tue, 19 Jun 2018 14:33:19 GMT

Any errors / what does "not working" mean?

It works fine for me when I test it like this:

| makeresults 
| eval response="[{\"type\":\"A\",\"response\":\"52.7.102.57\",\"asn\":\"14618\",\"asname\":\"aws\"},{\"type\":\"A\",\"response\":\"52.71.245.135\",\"asn\":\"14618\",\"asname\":\"aws\"}]"
| rex field=response "\{\"\w+\":\"\w+\",\"(?<response_values>[^\"]+)" max_match=0
| eval response_values = mvjoin(response_values,",")

Can you share a screenshot of your attempt?

Re: How to get the field value substring?

mcohen13 — Wed, 20 Jun 2018 12:34:10 GMT

in the response_values field i get the response twice, what i want is the ip address
2018-06-20 12:33:02 [{"type":"A","response":"52.7.102.57","asn":"14618","asname":"aws"},{"type":"A","response":"52.71.245.135","asn":"14618","asname":"aws"}] response,response

Re: How to get the field value substring?

FrankVl — Wed, 20 Jun 2018 12:42:37 GMT

Well, you asked for extracting the bold "response" values. So that's what I thought you wanted. But extracting the address makes a lot more sense, try this:

...your base search to get to this data...
 | rex "\"response\":\"(?<response_values>[^\"]+)" max_match=0
 | eval response_values = mvjoin(response_values,",")

Re: How to get the field value substring?

mcohen13 — Wed, 20 Jun 2018 15:23:45 GMT

works thanks!