topic Re: How do I extract these fields and corresponding values parsing while JSON data? in Splunk Search

How do I extract these fields and corresponding values parsing while JSON data?

sarfarajsayyad — Wed, 07 Dec 2016 02:00:06 GMT

I have a following JSON input.

{
    "StartTime": {
        "@item": "1",
        "#text": "2016/11/21 09:35:25"
    },
    "Encryption": {
        "@item": "1",
        "#text": "None"
    },
    "Duration": {
        "@item": "1",
        "#text": "13"
    },
    "DisplayName": {
        "@item": "1",
        "#text": "80081"
    },
    "device_id": 1
}

I need to extract fields like below and ignore the rest (@item) from JSON.
device_id = 1
DisplayName=80081
Duration=13
Encryption=None
StartTime="2016/11/21 09:35:25"

Can it be done writing a single Regex to extract all the fields, or do I need to write separate regex for each field in TRANSFORMS.CONF ?

Any example will help.
/Sarfaraj

Re: How do I extract these fields and corresponding values parsing while JSON data?

sundareshr — Wed, 07 Dec 2016 02:31:45 GMT

If it is valid JSON, add this to your props.conf

[ stanza]
KV_MODE = json

Re: How do I extract these fields and corresponding values parsing while JSON data?

sarfarajsayyad — Wed, 07 Dec 2016 03:21:56 GMT

Dear, I have added that already.
By default its giving me columns like StartTime.@item , StartTime.#text. But i need only StartTime having value of StartTime.#text. Something like StartTime="2016/11/21 09:35:25".

Re: How do I extract these fields and corresponding values parsing while JSON data?

Richfez — Wed, 07 Dec 2016 03:28:19 GMT

If they're already parsed like that, why not just create a simple alias for them, like they explain in this answer?

Re: How do I extract these fields and corresponding values parsing while JSON data?

sundareshr — Wed, 07 Dec 2016 04:04:41 GMT

You can create aliases as suggested of you can rename the fields in your search rename *.#text AS * will rename all <>.#text to <>

Re: How do I extract these fields and corresponding values parsing while JSON data?

gokadroid — Wed, 07 Dec 2016 04:13:19 GMT

Like @Sundaresh mentions to get them extracted automatically, but here are the ways to do this in search time (brute force) if really required

Using spath:

your query to return events
| spath output=device_id path=device_id
| spath output=startTime path=StartTime.#text 
| spath output=encryption path=Encryption.#text 
| spath output=duration path=Duration.#text 
| spath output=displayName path=DisplayName.#text 
| table device_id, startTime, encryption, duration, displayName

Using rex:

your query to return events
| rex field=_raw max_match=0 "\#text\"\:\s\"(?<jsonFields>[^\"]+)"
| rex field=_raw "device_id\"\:\s*(?<device_id>[\S]+)"
| eval startTime=mvindex(jsonFields, 0)
| eval encryption=mvindex(jsonFields, 1)
| eval duration=mvindex(jsonFields, 2)
| eval displayName=mvindex(jsonFields, 3)
| table startTime, encryption, duration, displayName, device_id

Re: How do I extract these fields and corresponding values parsing while JSON data?

sarfarajsayyad — Wed, 07 Dec 2016 08:56:13 GMT

Thank you ! Is there any way to do it in index time ?