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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...