How to extract fields in a file?

[dhirendra761]

I need to extract each filed in "monitoringdata" in file.
belo is sample of data:

{"@timestamp":"2018-07-27T16:06:28.025+05:30","@version":1,"logger_name":"ADNSMONITOR","thread_name":"priority-all-publishrevisiondownloadthread-CSDBL-S1KD-A350-HHJ-28-Jul-2018","level":"INFO","level_value":20000,"HOSTNAME":"ITEM-S66462","startTaskManDate":"1532687732198","endDate":"","start":"","error":"","attempt":"1","jobnorm":"JB1T40R011-DOWNLOAD","duration":"","stop":"","requestId":"01f965d4-d681-4f02-a349-44870765ed10","tasknorm":"","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\":\"#[A350]#HHJ#CSDBL##[PN1234]##\",\"itemId\":\"260_S1KD\",\"onlineAvailabilityData\":{\"type\":\"DownloadState\",\"status\":\"InProgress\",\"fromDate\":null,\"toDate\":null},\"acksStatus\":null}","functionalKey":"CSDBL-S1KD-A350-HHJ-28-Jul-2018","startPublicationDate":"1532687732198","jobSourceId":"IM01-SRDD","status":"IN_PROGRESS","appName":"ADNS-Taskman","appEnv":"dev","appProduct":"1T40"}

I have tried so many times. but always get monitoringdata as group of data fields.
link text
I need a new field like deliverableType, docType, acProgram and so on.

I am trying lot, but not succeed. Any help will be appreciated.

[493669 · Accepted solution]

@dhirendra761, can you try below-

...| rex max_match=0 field=monitoringdata "(?<key>\w+)\":\"?(?<value>\"?[^,\"]+)"|table key, value|eval b=mvzip(key,value)|mvexpand b| makemv b delim=","|eval key=mvindex(b, 0)| eval value=mvindex(b, 1)|table key value| transpose 0 header_field=key
 | fields - column

try this run anywhere search query-

|makeresults|eval 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\":\"#[A350]#HHJ#CSDBL##[PN1234]##\",\"itemId\":\"260_S1KD\",\"onlineAvailabilityData\":{\"type\":\"DownloadState\",\"status\":\"InProgress\",\"fromDate\":null,\"toDate\":null},\"acksStatus\":null}"| rex max_match=0 field=monitoringdata "(?<key>\w+)\":\"?(?<value>\"?[^,\"]+)"|table key, value|eval b=mvzip(key,value)|mvexpand b| makemv b delim=","|eval key=mvindex(b, 0)| eval value=mvindex(b, 1)|table key value| transpose 0 header_field=key
 | fields - column

[dhirendra761]

Hi Thanks for the suggestion.
answer1 I got result (0) . Link: https://imgur.com/a/4p3ID6v
answer2: It gives result 1 for specifc filed. What if there are many monitoringdata in one single event.

I have 16 monitoringdata in my file.

Please suggest as well.

Thanks. 🙂

[493669]

change fieldname as monitoringData and try again as field names are case sensitive

...| rex max_match=0 field=monitoringData "(?<key>\w+)\":\"?(?<value>\"?[^,\"]+)"|table key, value|eval b=mvzip(key,value)|mvexpand b| makemv b delim=","|eval key=mvindex(b, 0)| eval value=mvindex(b, 1)|table key value| transpose 0 header_field=key
  | fields - column

[MuS]

Just a little addition to this: this method will make your Splunk explode once you put a lot of events through the mvexpand.
Here is a link https://answers.splunk.com/answers/319646/how-to-write-the-regex-to-extract-data-inside-squa.html to an answer doing the same using props.conf and transforms.conf

cheers, MuS

[dhirendra761]

Hi @MuS , Hi @493669,

Thanks for the answer, just one more quick if i need to export the report for monitorData based on itemId in json then what type of search i have to write.

Like i need whole monitorData for itemId (key="itemId ") whose value is 260_S1KD

[493669]

if you made changes in props.conf and transforms.conf as suggested by @MuS then fields get extracted at search time then simply apply filter on itemId as shown below and export in JSON format-

sourcetype="logadns"|where itemId="260_S1KD"|table monitoringData

[dhirendra761]

Hi @493669 can i connect with you regarding some question on splunk.

Thanks.

[493669]

you can connect me on mandalerajesh@yahoo.in

[dhirendra761]

@493669 Thank you very much 🙂

[dhirendra761]

Thank you very much @493669 and @MuS for your support.
:)

[dhirendra761]

@493669 please post you comment in answer section so that I can mark as accepted

[dhirendra761]

Hi Rajesh @493669
Do you have any idea on my another question:
Please have a look:
https://answers.splunk.com/answers/674429/not-monitored-similer-name-local-files-on-windows.html

Thanks

[dhirendra761]

Hi @493669

In MonitorData , there are 25 keys(itemId, docType,.....)

and each key contains no of value(eg. itemId contains 100 different values) ....... can i extract each value as seprate event.
like itemId, doctype.

Please suggest as well. Thanks in advance.

[dhirendra761]

https://imgur.com/H46vjdK