Splunk Search

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

New Member

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

0 Karma

Motivator

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

New Member

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

0 Karma

Legend

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

[ stanza]
KV_MODE = json
0 Karma

New Member

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

0 Karma

Legend

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!