Parsing double nested JSON

[sammagana]

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_daemon	expiration_date	type	parse_status	parse_error
mcomp	2021-08-31	floating	SUCCESS	null

Thank you so much! Appreciate any and all help!

[ITWhisperer]

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

[sammagana]

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! 

[ITWhisperer]

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.

[thambisetty]

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.

[sammagana]

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?

[thambisetty]

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+)?]+)