topic Re: How to extract dynamic key from nested json? in Splunk Search

How to extract dynamic key from nested json?

directtv999 — Mon, 14 Nov 2022 14:40:33 GMT

sample json:

Hosts: { [-]
Nodepool1: { [-]
Cluster: xyz1
Accountid: idxyz
Nodepool3: { [-]
Cluster: xyz1
Accountid: idxyz
Nodepool5: { [-]
Cluster: xyz1
Accountid: idxyz

am trying below query but it display list of servers but missing few servers randomly, please correct the query if am missing something.

index=index1 | eval cluster="" | foreach hosts.*.cluster [| eval cluster=isnotnull('<<FIELD>>'),'<<FIELD>>,cluster)] | table cluster

Re: How to extract dynamic key from nested json?

directtv999 — Mon, 14 Nov 2022 18:20:33 GMT

ltt

Re: How to extract dynamic key from nested json?

yuanliu — Tue, 15 Nov 2022 10:45:37 GMT

@directtv999 It is important to illustrate data using raw, conformant format (anonymize as needed); these Splunk formatted forms are difficult for others to parse. In fact, the illustrated form is not even correct in Splunk highlighted format because they are not enclosed properly.

Additionally, it is impossible to "correct" anything unless we know what is "incorrect". You should explain what you are trying to do with your sample code, and use samples to illustrate what exactly are "missing".

Further more, your illustrated sample SPL will not be "missing a few servers randomly." It will not give any result because your sample data contains field names like Hosts.*.Cluster (capital H and C) but your code is iterating over hosts.*.cluster (lower case h and c). Even if you correct for capitalization, it will produce an error "Fields cannot be assigned a boolean result."

The point is, when you post a question, you must accurately reflect the format of your data, illustrate accurate syntax of your attempted SPL, describe expected results - using mockup if necessary, illustrate output from sample SPL, then explain why the output does not meet your expectation. It is unfair to expect volunteers in this forum to be mind readers.

This said, I am willing to give mind reading a try. If I guess the bracket closures correctly, then reverse engineer your illustrated data, it looks something like:

{"Hosts": { "Nodepool1": { "Cluster": "xyz1", "Accountid": "idxyz" }, "Nodepool3": { "Cluster": "xyz1", "Accountid": "idxyz" }, "Nodepool5": { "Cluster": "xyz1", "Accountid": "idxyz" } } }

Then, I try to speculate the correct form of your sample SPL,

| eval cluster="" | foreach Hosts.*.Cluster [| eval cluster=if(isnotnull('<<FIELD>>'), '<<FIELD>>', cluster)]

The problem, then, is that cluster is single valued; with each iteration, you only get the current <<FIELD>> value. By the end of iteration, cluster will always be the last <<FIELD>> value. Try this instead.

| eval cluster="" | foreach Hosts.*.Cluster [| eval cluster = mvappend(cluster, if(isnotnull('<<FIELD>>'), '<<FIELD>>', cluster))] | table cluster

Out of the corrected raw event, the above should give

cluster

xyz1

Re: How to extract dynamic key from nested json?

ITWhisperer — Tue, 15 Nov 2022 11:02:57 GMT

I support everything @yuanliu has said about providing clear and complete questions.

Having said that, if I understood correctly, you want the host names, i.e. the dynamic key, not the contents of the Cluster field? (Am I mind-reading? 😀)

If so, try something like this

| eval hosts="" | foreach Hosts.*.* [| eval hosts = mvdedup(mvappend(hosts, if(isnotnull('<<FIELD>>'), "<<MATCHSEG1>>", hosts)))] | table hosts

Re: How to extract dynamic key from nested json?

directtv999 — Tue, 15 Nov 2022 19:28:24 GMT

Thanks for the reply @ITWhisperer I need cluster names in a table not hosts

cluster

xyz1

xyz2

xyz3

Re: How to extract dynamic key from nested json?

directtv999 — Tue, 15 Nov 2022 19:52:53 GMT

Appreciate your response @yuanliu will try to be more clear from next time. I tried your query and I can see the missing clusters working as expected and listing all the clusters for each event.

I want only unique values from all events for ex below table I tried dedup but no luck

cluster

xyz1

xyz2

xyz3

xyz4

your query is giving below output

cluster

xyz1

xyz2

xyz3

xyz2

xyz3

xyz4

xyz1

xyz2

xyz4

xyz1

Re: How to extract dynamic key from nested json?

yuanliu — Thu, 17 Nov 2022 07:42:57 GMT

Have you considered values function?

| eval cluster="" | foreach Hosts.*.Cluster [| eval cluster = mvappend(cluster, if(isnotnull('<<FIELD>>'), '<<FIELD>>', cluster))] | stats values(cluster) as cluster

Re: How to extract dynamic key from nested json?

ITWhisperer — Thu, 17 Nov 2022 08:45:27 GMT

You could use mvdedup (as I suggested in my otherwise incorrect solution)

| eval cluster="" | foreach Hosts.*.Cluster [| eval cluster = mvdedup(mvappend(cluster, if(isnotnull('<<FIELD>>'), '<<FIELD>>', cluster)))] | table cluster