How to create a table from nested JSON keys with different names?

[DenysB]

Part of my json event looks like this:

1. "certificatecache":[
2. {"type":"cacheSize","int32value":"10"},
3. {"type":"cacheInUse","int32value":"0"},
4. {"type":"certInCache","int32value":"1"},
5. {"type":"hit","int64gap":"1428335"},
6. {"type":"miss","int64gap":"79397"},
7. {"type":"health","int32value":"100"}
8. ]

I get fields certificatecache{}.type, certificatecache{}.int32value, certificatecache{}.int64gap and try to use spath, but if you notice, both of fields certificatecache{}.int32value and certificatecache{}.int64gap contain certificatecache values and it is a problem

I'd like to create a Table with certificatecache_type certificatecache_value.

Thanks a lot in advance!

[493669]

Hi @DenysB,
try coalesce function after getting 3 columns:

...| eval certificatecache_value=coalesce('certificatecache{}.int32value','certificatecache{}.int64gap')

[DenysB]

Hi!
In this case, coalesce doesn't work, because it returns the first value that is not NULL and I get only or int32value or int64gap values.

[493669]

In each event either int32value or int64gap values will be present but not both in a single event...isn't it?

[DenysB]

No, it is a key problem. I have both int32value and int64gap in a single event.

[mdsnmss]

You can try to use the rename to your advantage: | rename certificatecache{}.type as certificatecache_type, certificatecache{}.int* as certificatecache_value. Renaming can help manipulating JSON arrays easier.

[DenysB]

I got an error:
Error in 'rename' command: Wildcard mismatch: 'certificatecache{}.int*' as 'certificatecache_value'.

In this case, as I understand, I should use:
| rename certificatecache{}.type as certificatecache_type, certificatecache{}.int* as certificatecache_value*
but it doesn't make sense.

[mdsnmss]

It looks like you have to rename the JSON array first. Try: | rename certificatecache{}.* as * | rename type as certificate_type int* as certificate_value

[DenysB]

The same:
Error in 'rename' command: Wildcard mismatch: 'int*' as 'certificate_value'.

I guess it's a wrong way to use rename.

http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Rename
"You cannot use the rename command to merge multiple fields into one field because of null, or non-present, fields are brought along with the values."

[mdsnmss]

Oh, I see now. Yeah, it is trying to combine two fields into one with that. A couple of questions: Are there multiple events like this? You are trying to make a table with multiple rows for the single event, correct? Like:

certificatecache_type                certificatecache_value
cacheSize                                    10
cacheInUse                                 0
certInCache                                1
hit                                             1428335
miss                                          79397
health                                       100

[DenysB]

Yes, you are right, I am trying to make this table.