Splunk Search

Parsing double nested JSON

sammagana
Loves-to-Learn

Hello,

So I am having some trouble parsing this json file to pull out the nested contents of the 'licenses'.  My current search can grab the contents of the inner json within 'features' but not the nested 'licenses' portion.

My current search looks like this:

 

 

 

index=someindex
| fields features.*.* 
| rename features.* as * 
| eval FieldList="" 
| foreach * 
[ eval FieldList=if("<<MATCHSTR>>"!="FieldList",FieldList.","."<<MATCHSTR>>","") ] 
| eval FieldList=split(FieldList,",") 
| mvexpand FieldList 
| eval Software=mvindex(split(FieldList,"."),0),Column=mvindex(split(FieldList,"."),1) 
| eval value=""
| foreach * 
[ eval value=if("<<FIELD>>"==Software.".".Column,'<<FIELD>>',value),{Column}=value ]

 

 

 

 sample json file:

"features": {
"M_TOOL": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"M_GUI": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"MT_GUI": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"M_TOOL": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"ML_GUI": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"C_SOLVTOOL_Ser": {
"licenses": [],
"num_issued": 40,
"num_used": 0,
"num_available": 40,
"parse_status": "SUCCESS",
"parse_error": null
},
"CP_SOLVTOOL_Par": {
"licenses": [],
"num_issued": 600,
"num_used": 0,
"num_available": 600,
"parse_status": "SUCCESS",
"parse_error": null
},
"CD_SOLVTOOL_Ext": {
"licenses": [],
"num_issued": 20000,
"num_used": 0,
"num_available": 20000,
"parse_status": "SUCCESS",
"parse_error": null
},
"C_SOLV_Ser": {
"licenses": [
{
"version": ,
"vendor_daemon": "mcomp",
"expiration_date": "2021-08-31",
"type": "floating",
"parse_status": "SUCCESS",
"parse_error": null
}
],
"num_issued": 40,
"num_used": 16,
"num_available": 24,
"parse_status": "SUCCESS",
"parse_error": null
}

}

Ideally I'd like to put the contents into some table to start

vendor_daemonexpiration_datetypeparse_statusparse_error
mcomp2021-08-31floatingSUCCESSnull


Thank you so much! Appreciate any and all help!

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Will something like this work?

| spath
| fields *.licenses*
| rename features.*.licenses{}.* as *.*
| fields - _raw _time
| transpose 0
| eval software=mvindex(split(column,"."),0)
| eval attribute=mvindex(split(column,"."),1)
| eval {attribute}='row 1'
| fields - column row* attribute
| stats values(*) as * by software
0 Karma

sammagana
Loves-to-Learn

Hi!,

Thanks for the help. Really appreciate it. 

It seems that with this query I am only able to return the list of software? 

The attributes don't generate in the columns.

 

Any suggestion?

 

Thank you! 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you share what you do get? Also, can you share what just the spath gives you? The more information you can give, the easier it will be to help you. Send a private message if you like.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

can you share _raw event ?

do you expect more than one license in single raw event ? because I see only one license field has values in the above event.

————————————
If this helps, give a like below.
0 Karma

sammagana
Loves-to-Learn

Yes, I do expect more then one license field in the event.

Here is my Raw Event:

"features": {
"Acceleration": { "licenses": [ { "version": "v20", "vendor_daemon": "cstd", "expiration_date": "2021-07-16", "type": "floating", "parse_status": "SUCCESS", "parse_error": null } ], "num_issued": 5, "num_used": 3, "num_available": 2, "parse_status": "SUCCESS", "parse_error": null }, "fronter": { "licenses": [], "num_issued": 5, "num_used": 0, "num_available": 5, "parse_status": "SUCCESS", "parse_error": null }, "start": { "licenses": [ { "version": "v20", "vendor_daemon": "cstd", "expiration_date": "2021-07-16", "type": "floating", "parse_status": "SUCCESS", "parse_error": null } ], "num_issued": 5, "num_used": 1, "num_available": 4, "parse_status": "SUCCESS", "parse_error": null }, "En_FSP": { "licenses": [], "num_issued": 1, "num_used": 0, "num_available": 1, "parse_status": "SUCCESS", "parse_error": null }, "OS_Linux": { "licenses": [], "num_issued": 5, "num_used": 0, "num_available": 5, "parse_status": "SUCCESS", "parse_error": null },

 

Is this what your looking for?

0 Karma

thambisetty
SplunkTrust
SplunkTrust

if licenses field is already extracted and licenses field has value like below from the above events:

{ "version": "v20", "vendor_daemon": "cstd", "expiration_date": "2021-07-16", "type": "floating", "parse_status": "SUCCESS", "parse_error": null }
{ "version": "v20", "vendor_daemon": "cstd", "expiration_date": "2021-07-16", "type": "floating", "parse_status": "SUCCESS", "parse_error": null }
 
then you can write simple props & transforms as below in search head to have fields from licenses set:

props.conf

 

 

[yoursourcetype]
REPORT-jsonextract = jsonextract

 

 

transforms.conf

 

 

[jsonextract]
SOURCE_KEY=licenses
REGEX=(?<_KEY_1>[^\"]+)\":\s+\"?(?<_VAL_1>[^(\"|\s+)?]+)

 

 

 if licenses field is not extracted already then you need to follow below to extract licenses field

props.conf

 

 

[yoursourcetype]
REPORT-a_licensesextract = a_licensesextract
REPORT-b_jsonextract = b_jsonextract

 

 

transforms.conf

 

 

[a_licensesextract]
REGEX = licenses\":\s+\[(?<licenses>[^\]]+)
FORMAT = licenses::$1
MV_ADD = true

[b_jsonextract]
SOURCE_KEY=licenses
REGEX=(?<_KEY_1>[^\"]+)\":\s+\"?(?<_VAL_1>[^(\"|\s+)?]+)

 

 

————————————
If this helps, give a like below.
0 Karma
Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...