Extract JSON Data

[mchennam]

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":[
      
   ]
}

[ITWhisperer]

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

[mchennam]

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

[ITWhisperer · Accepted solution]

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]*\}"