How to extract dynamic key from nested json?

[directtv999]

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

[ITWhisperer]

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

[directtv999]

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

cluster

xyz1 xyz2 xyz3

[yuanliu]

@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 xyz1 xyz1

[directtv999]

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

[ITWhisperer]

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

[yuanliu]

Have you considered values function?

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

[directtv999]

ltt