Splunk Search

Extract JSON Data

mchennam
Engager

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

 

 

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

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

 

View solution in original post

0 Karma

ITWhisperer
Ultra Champion

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

mchennam
Engager

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

Tags (1)
0 Karma

ITWhisperer
Ultra Champion

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

 

View solution in original post

0 Karma

Tune In & Win!

Don't miss out on your
chance to take home free
prizes by helping our players
save the Splunk Cloudom!

Dungeons & Data
Monsters: Splunk O11y
Day Editions Games
stream live:
5/4 at 6:30pm PST
5/5 at 7:00pm PST
on