Splunk Search

How do you create a table from the following data?

stevepkr84
New Member

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.

Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

stevepkr84
New Member

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

stevepkr84
New Member

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.

0 Karma

kmaron
Motivator

What do you want your table to look like?

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...