How to extract event data that displays as JSON?

[dhirendra761]

After uploading local file, i found event name "monitoringData" as {"deliverableType":"Manual","docType":"CSDBL","docId":"SSH","revisionDate":1532370600000,"format":"S1KD","attachmentType":null,"attachmentIssueDate":1532370600000,"acknowledgment":null,"acknowledgmentDate":null,"productionOrder":"SRDD","domain":null,","onlineAvailabilityData":null}
I need to genrate event dig deep into " monitoringData"
Required events like majorEvent,deliverableType, onlineAvailabilityData, docType and so- on.
Please suggest me. I am new in splunk.My file content below type of data:
"monitoringData":"{\"deliverableType\":\"Manual\",\"docType\":\"CSDBL\",\"docId\":\"KKH\",\"revisionDate\":1532802600000,\"format\":\"S1KD\",\"attachmentIssueDate\":1532802600000,\"acknowledgment\":null,\"acknowledgmentDate\":null,\"productionOrder\":\"SRDD\",\"domain\":null,\",\"itemId\":\"259_S1KD\",\"onlineAvailabilityData\":null,\"acksStatus\":null}","startPublicationDate":"1532686970112","jobSourceId":"IM01-SRDD","status":"IN_PROGRESS","appEnv":"dev","appProduct":"1T40"}

[sudosplunk · Accepted solution]

The spath command enables you to extract information from structured data formats, JSON. The command stores this information in one or more fields. The command also highlights the syntax in the displayed events list. More info about spath.
your_search | spath input=monitoringData

Alternatives to the spath command
If you are using autokv or index-time field extractions, the path extractions are performed for you at index time.

You do not need to explicitly use the spath command to provide a path.

If using indexed_extractions=JSON or using KV_MODE=JSON in the props.conf file, then the spath command is not necessary to explicitly use.

In your props.conf, add KV_MODE and restart splunk.

[adns]
KV_MODE=JSON

Let me know if this doesn't help and I can provide you the regular expression.

[dhirendra761]

Hi Nittala,
Thanks for answer.
yes please provide me regular expression for the same.

Appreciated if you provide me step as well. like where i need to use the regex.(my understanding, it will be used in same props.conf.)

Thanks again.

[dhirendra761]

@nittaa_surya please check https://answers.splunk.com/answers/676846/field-extraction-from-field.html
for more info

[sudosplunk]

Sure. But can you give me the exact sample data. The reason I ask is, the sample data above and the data in your screenshot doesn't match. Use 'code sample' function (the one with 101010 or use ctrl+k option) to paste text as is.

[dhirendra761]

"tasknorm":"","monitoringData":"{\"deliverableType\":\"Manual\",\"docType\":\"CSDBL\",\"acProgram\":\"PA350 XWB\",\"docId\":\"KKH\",\"revisionDate\":1532802600000,\"format\":\"S1KD\",\"entity\":null,\"customersRightStatus\":null,\"customersRightEventDate\":null,\"majorEvent\":null,\"emergency\":null,\"attachmentType\":null,\"attachmentIssueDate\":1532802600000,\"acknowledgment\":null,\"acknowledgmentDate\":null,\"productionOrder\":\"SRDD\",\"domain\":null,\"productKey\":\"#[A350]#KKH#CSDBL##[PN1234]##\",\"itemId\":\"259_S1KD\",\"onlineAvailabilityData\":null,\"acksStatus\":null}","functionalKey":"CSDBL-S1KD-A350-KKH-28-Jul-2018","startPublicationDate":"1532686970112","jobSourceId":"IM01-SRDD","status":"IN_PROGRESS","appName":"ADNS-Taskman","appEnv":"dev","appProduct":"1T40"}
{"@timestamp":"2018-07-27T15:54:13.280+05:30","@version":1

[dhirendra761]

link text

I have uploaded sample file. Please check thanks.

[dhirendra761]

https://drive.google.com/open?id=1lXOlodiRN87bF1mH_Sr4GD7qY9tR0rDW

[sudosplunk]

Unfortunately, I can't access G-drive links due to security reasons at my work place. But, I will guide you through the steps to extract fields using Interactive Field Extractor (IFX).

1. Access the field extractor: Click Extract New Fields from the bottom of the fields sidebar.
2. Select sample event: In the event list, select a sample event that has one or more values that you want to extract as fields and click next.
3. Select Method: Click Delimiters and use , as the delimiter and click next.
4. Rename fields: Click on fields that you want to rename and enter the desired field names.
5. Validate your field extraction: Review the event list table to see which events match or fail to match the field extraction.
6. Review and save: Here, name your extraction setting (ex. REPORT-custom_json) and Set Permissions to App to make this extraction available at app-level and click save.

To manage the field extractions which you just created, please navigate to "Settings -> Fields -> Field Extractions".

To have a look at the extraction in the back-end, navigate (thru CLI) to $SPLUNK_HOME/etc/apps/app_which_you_used_in_final_step/local/props.conf and transforms.conf

Please review the docs if you're struck. HTH!

[dhirendra761]

Hi Surya, I already tried before many times using Extracted new fileds option with different delimiters, but it doesn't work. It gives whole values in single field. eg :

field18

monitoringData: {"deliverableType":null,"docType":null,"acProgram":null,"docId":null,"revisionDate":null,"format":null,"entity":null,"customersRightStatus":null,"customersRightEventDate":null,"majorEvent":null,"emergency":null,"attachmentType":null,"attachmentIssueDate":null,"acknowledgment":null,"acknowledgmentDate":null,"productionOrder":null,"domain":null,"productKey":null,"itemId":"260_S1KD","onlineAvailabilityData":{"type":"SearchOnlineState","status":"Default","fromDate":1531506600000,"toDate":9223372036854775807},"acksStatus":null}    

I need to further extraction above field value.

[sudosplunk]

Hello @dhirendra761,

Here you go. Add below settings in props.conf.

To extract more fields, use/tweak the regex a little. For example, to extract onlineAvailabilityData use, EXTRACT-onlineAvailabilityData = \S+onlineAvailabilityData\\?\"?\:\\?\"?(?<onlineAvailabilityData>\w+)

[your_sourcetype]
EXTRACT-deliverableType = ^\S+deliverableType\\?\"?\:\\?\"?(?<deliverableType>\w+)
EXTRACT-docType = \S+docType\\?\"?\:\\?\"?(?<docType>\w+)
EXTRACT-docId = \S+docId\\?\"?\:\\?\"?(?<docId>\w+)