topic Re: Extract JSON Data in Splunk Search

Extract JSON Data

mchennam — Fri, 09 Apr 2021 01:04:15 GMT

Need help to find a way to search JSON strings where an attributes is empty.

Ex: get all JSON data where 'tags' is empty.

Appreciate your help.

{ "configurationItemCaptureTime":"2021-04-09T00:40:20.800Z", "relationships":[ ], "configurationStateId":3456, "configurationItemVersion":"1.3", "tags":{ }, "awsAccountId":"12345678", "supplementaryConfiguration":{ }, "ARN":"arn:aws:ec2:us-east-1:4567:instance/i-0b8973444340", "configurationStateMd5Hash":"", "awsRegion":"us-east-1", "resourceId":"i-0343434345b26510", "configurationItemStatus":"ResourceDeleted", "resourceType":"AWS::EC2::Instance", "relatedEvents":[ ] }

Re: Extract JSON Data

ITWhisperer — Fri, 09 Apr 2021 09:58:48 GMT

You could use rex to edit the JSON to include a known (and previously unused) field with a known (and previously unused) value, then collect all the field names that match these strings

| makeresults | eval _raw="{ \"configurationItemCaptureTime\":\"2021-04-09T00:40:20.800Z\", \"relationships\":[ ], \"configurationStateId\":3456, \"configurationItemVersion\":\"1.3\", \"tags\":{ }, \"awsAccountId\":\"12345678\", \"supplementaryConfiguration\":{ }, \"ARN\":\"arn:aws:ec2:us-east-1:4567:instance/i-0b8973444340\", \"configurationStateMd5Hash\":\"\", \"awsRegion\":\"us-east-1\", \"resourceId\":\"i-0343434345b26510\", \"configurationItemStatus\":\"ResourceDeleted\", \"resourceType\":\"AWS::EC2::Instance\", \"relatedEvents\":[ ] }" | rex mode=sed "s/\{[^\w]*\}/{\"field\":\"not available\"}/g" | rex mode=sed "s/\[[^\w]*\]/[{\"field\":\"not available\"}]/g" | spath | foreach * [ eval emptyattributes=if("<<FIELD>>"!="emptyattributes",if('<<FIELD>>'=="not available",if(isnull(emptyattributes),mvindex(split("<<FIELD>>",".field"),0),mvappend(emptyattributes,mvindex(split("<<FIELD>>",".field"),0))),emptyattributes),emptyattributes) ] | fields emptyattributes

Re: Extract JSON Data

mchennam — Fri, 09 Apr 2021 15:22:48 GMT

Thanks for your time. I am sorry for not being clear on what i need. The log file contains json strings. I am trying to identify the rows containing empty 'tags:{}' attribute. And then show the extracted fields in a table.

The following is what i have come up with so far. Not sure if this is a efficient way to query thousand of records.

index=xxx sourcetype="aws:config" "\"tags\": {}" | table ARN, aws_account_id, awsRegion, availabilityZone, resourceId, resourceCreationTime, configurationItemStatus, configurationItemCaptureTime

ARN	aws_account_id	awsRegion	availabilityZone	resourceId	resourceCreationTime	configurationItemStatus	configurationItemCaptureTime
arn:aws:ec2:us-east-1:23233232:instance/i-02d7ce4ac0daa3a94	23232323	us-east-1	us-east-1a	i-232323232323	2021-04-09T13:00:16.000Z	ResourceDiscovered	2021-04-09T13:01:58.577Z

Re: Extract JSON Data

ITWhisperer — Fri, 09 Apr 2021 15:51:13 GMT

Apart from trawling through all your data (I am not sure you can avoid that), your search relies on the empty tags element having nothing between the braces - is this guaranteed to be always the case? If not, perhaps you could consider the regex command to filter the events with match a regular expression that copes with variations. Something like this perhaps?

| regex _raw="\"tags\":\s*\{[\s]*\}"