How do you create a table from the following data?

[stevepkr84]

Assuming these 3 docs, how can I create a table where I dedupe by account (I want the most recently ingested event) and display fields account, account_id, resources{].instanceId, the Tag value where Key = Name. This seems easy without trying to extract the Name tag value.

{
    "account_id": 1,
    "account": "dev",
    "resources": [
        {
            "instanceId": 123,
            "Tags": [
                {
                    "Key": "Name",
                    "Value": "Instance1"
                },
                {
                    "Key": "Owner",
                    "Value": "Dave"
                }
            ]
        },
        {
            "instanceId": 456,
            "Tags": [
                {
                    "Key": "CostCentre",
                    "Value": "ABC"
                },
                {
                    "Key": "Name",
                    "Value": "Instance2"
                }
            ]
        }
    ]
}

{
    "account_id": 1,
    "account": "dev",
    "resources": [
        {
            "instanceId": 123,
            "Tags": [
                {
                    "Key": "Name",
                    "Value": "Instance1"
                },
                {
                    "Key": "Owner",
                    "Value": "Dave"
                }
            ]
        }
    ]
}

{
    "account_id": 2,
    "account": "test",
    "resources": [
        {
            "instanceId": 789,
            "Tags": [
                {
                    "Key": "Name",
                    "Value": "Instance1"
                },
                {
                    "Key": "Owner",
                    "Value": "Bob"
                }
            ]
        }
    ]
}

This was my attempt:

| dedup account_id  | rename resources{}.Tags{}.Value AS value, resources{}.Tags{}.Key AS key, resources{}.InstanceId AS id | eval x=mvzip(key, value) | mvexpand x | eval x=split(x,",") | eval key=mvindex(x,0) | search key=Name | eval value=mvindex(x,1) | table account account_id id key value

It almost gives me the correct data, but I get each instance per account duplicated in the row for each Name tag.

Any help would be appreciated.

[kamlesh_vaghela · Accepted solution]

@stevepkr84

Can you please try below search?

YOUR_SEARCH | dedup account_id | kv 
| spath path=resources{} output=resources 
| mvexpand resources 
| eval _raw=resources 
| kv 
| rename Tags{}.Key as Tags_Key, Tags{}.Value as Tags_Value 
| eval tmp=mvzip(Tags_Key,Tags_Value) | mvexpand tmp | fields account_id,account tmp instanceId | eval key=mvindex(split(tmp,","),0), value=mvindex(split(tmp,","),1) | rename instanceId as id | table account account_id id key value

My Sample Search:

| makeresults 
| eval _raw="{     \"account_id\": 1,     \"account\": \"dev\",     \"resources\": [         {             \"instanceId\": 123,             \"Tags\": [                 {                     \"Key\": \"Name\",                     \"Value\": \"Instance1\"                 },                 {                     \"Key\": \"Owner\",                     \"Value\": \"Dave\"                 }             ]         },         {             \"instanceId\": 456,             \"Tags\": [                 {                     \"Key\": \"CostCentre\",                     \"Value\": \"ABC\"                 },                 {                     \"Key\": \"Name\",                     \"Value\": \"Instance2\"                 }             ]         }     ] } " 
| kv 
| spath path=resources{} output=resources 
| mvexpand resources 
| eval _raw=resources 
| kv 
| rename Tags{}.Key as Tags_Key, Tags{}.Value as Tags_Value 
| eval tmp=mvzip(Tags_Key,Tags_Value) | mvexpand tmp | fields account_id,account tmp instanceId | eval key=mvindex(split(tmp,","),0), value=mvindex(split(tmp,","),1) | rename instanceId as id | table account account_id id key value

Thanks

[stevepkr84]

This looks about right, thank you. The only minor issue is that account_id and account display twice per row.

[kamlesh_vaghela]

Yes, bcoz there are multiple key-value pairs with single instanceId. How do you want to display data?

[stevepkr84]

Exactly as it comes out with your query, but ideally without the duplicated account_id and account showing on each row. But this is good enough for sure so will access the answer, thanks again.

[kmaron]

What do you want your table to look like?