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.
... View more
