topic Re: How to extract a specific key and its values in a table? in Getting Data In

How to extract a specific key and its values in a table?

nithyashreea — Fri, 14 Jun 2019 20:33:39 GMT

Hello I'm new to splunk search commands,
My event is like

ObjectID: 001
Properties: [ [ -]
{[-]
 Name: targetName
 Value: sample 1       
}
{
}
{
}
]

I want to display result set in table where ObjectID and targetName should be a column. Here, targetName is the key and it has multiples values such as sample1 or sample2 or sample3.
Can you please help me display the table?

Re: How to extract a specific key and its values in a table?

amitm05 — Sat, 15 Jun 2019 07:25:51 GMT

Can you mention how this event is getting processed in fields, especially the Name:targetname along with its values??

Re: How to extract a specific key and its values in a table?

niketn — Sat, 15 Jun 2019 07:44:07 GMT

@nithyashreea a valid json structure would help community experts assist you better. Please use the Code button (101010) or shortcut Ctrl+K while posting the JSON so that special characters do not escape.

Re: How to extract a specific key and its values in a table?

nithyashreea — Sat, 15 Jun 2019 16:05:19 GMT

If I add the fields in search, it is processed as
"Properties{}.Name"=targetName
"Properties{}.Value"="sample1"
There are many such Property names and values, but I have to fetch only the property with name "targetName" and its value (which is sample1 in this case).

Re: How to extract a specific key and its values in a table?

nithyashreea — Sat, 15 Jun 2019 16:20:08 GMT

Hi my raw text looks like this
ObjectID:001 | Properties{}.Name=targetName Properties{}.Name=targetClass Properties{}.Name=targetApp|
Properties{}.Value=sample1 Properties{}.Value=class1 Properties{}.Value=App1 |
Hope this gives you an idea.

Re: How to extract a specific key and its values in a table?

niketn — Sat, 15 Jun 2019 17:13:08 GMT

Sorry this is not a valid JSON. Also use code button on Splunk Answers. Since answer to your question would be dependent on the JSON structure. Based on the details provided, does it look like the following?

{
    "ObjectID":"001",
    "Properties":[{
        "name":"targetName",
        "value":"sample1"
    },
    {
        "name":"targetClass",
        "value":"class1"
    },
    {
        "name":"targetApp",
        "value":"App1"
    }]
}

Even if it did, you will have multi-valued fields. for Name and Value. Try the following partial query that generates the data as per the question (multivalued though😞

| makeresults 
| eval _raw="{
    \"ObjectID\": \"001\",
    \"Properties\":[{
        \"name\":\"targetName\",
        \"value\":\"sample1\"
        },{
        \"name\":\"targetClass\",
        \"value\":\"class1\"
        },{
        \"name\":\"targetApp\",
        \"value\":\"App1\"
        }],
}" 
| append 
    [| makeresults 
    | eval _raw="{
    \"ObjectID\": \"002\",
    \"Properties\":[{
        \"name\":\"targetName\",
        \"value\":\"sample2\"
            },{
        \"name\":\"targetClass\",
        \"value\":\"class2\"
            },{
        \"name\":\"targetApp\",
        \"value\":\"App2\"
            }],
}"] 
| spath 
| fields - _time _raw

Since it results in multi-valued field which you have not reported in your question, there are couple of more steps I have used to convert them to single value. Append the following search query to the above to get single value Properties Name and Value:

| eval zip=mvzip('Properties{}.name','Properties{}.value') 
| fields - "Properties{}.name" "Properties{}.value" 
| mvexpand zip 
| makemv zip delim="," 
| eval "Properties{}.name"=mvindex(zip,0), "Properties{}.value"=mvindex(zip,1) 
| fields - zip

Finally, through xyseries you should be able to create Fields targetApp, targetClass and targetName fields:

| xyseries ObjectID Properties.name Properties.value

Please try out the example and confirm. If it does not solve your issue, like stated before please share the JSON file sample data (mask/anonymize any sensitive information).

Following is the combined run anywhere search query:

| makeresults 
| eval _raw="{
    \"ObjectID\": \"001\",
    \"Properties\":[{
        \"name\":\"targetName\",
        \"value\":\"sample1\"
        },{
        \"name\":\"targetClass\",
        \"value\":\"class1\"
        },{
        \"name\":\"targetApp\",
        \"value\":\"App1\"
        }],
}" 
| append 
    [| makeresults 
    | eval _raw="{
    \"ObjectID\": \"002\",
    \"Properties\":[{
        \"name\":\"targetName\",
        \"value\":\"sample2\"
            },{
        \"name\":\"targetClass\",
        \"value\":\"class2\"
            },{
        \"name\":\"targetApp\",
        \"value\":\"App2\"
            }],
}"] 
| spath 
| fields - _time _raw 
| eval zip=mvzip('Properties{}.name','Properties{}.value') 
| fields - "Properties{}.name" "Properties{}.value" 
| mvexpand zip 
| makemv zip delim="," 
| eval "Properties{}.name"=mvindex(zip,0), "Properties{}.value"=mvindex(zip,1) 
| fields - zip
| xyseries ObjectID Properties.name Properties.value

Re: How to extract a specific key and its values in a table?

jnudell_2 — Sat, 15 Jun 2019 18:12:13 GMT

You can also put a backtick character

before and after the code you want to display.

Re: How to extract a specific key and its values in a table?

woodcock — Sat, 15 Jun 2019 23:13:54 GMT

I agree with the others, post an ACTUAL event in code markup and a mockup of your desired output.

Re: How to extract a specific key and its values in a table?

VatsalJagani — Sun, 16 Jun 2019 15:29:18 GMT

Hello @nithyashreea,
If you have valid JSON please check xpath command.