Splunk Search

How to populate a single column with dynamic field values?

ea-2023
Path Finder

Hello

I have a working dashboard where I have various fields that can be defined (field1 and field2 in the example), and some events have a field that is an extracted JSON object. I have successfully accessed various elements within the JSON object... but what I am trying to do is create ONE column called "Additional Details" where only certain elements, IF THEY EXIST, will populate in this column. 

The search below technically works, but as you can probably see, it will just add a NULL value if the specified element from field3 does not exist. Is there a way to check for other values in the JSON object, and populate those values in that single column, only if they exist?

i.e. If field3 has "Attribute Name", "Resource Name", and "ID", but many events have only one of these fields, is it possible to have the value from the field, only if it exists, populate in the "Additional Details" column?

 

index=test field1=* field2=* | spath input=field3 #(which is a json_object)# | fillnull value=NULL | eval type=if(isnotnull(element_from_field3), ElementName, NULL) | stats count values(type) as "Additional Details" by Other

 

 

Labels (3)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

There's an omission in @bowesmana's mvappend

 

| foreach "Attribute Name", "Resource Name", "ID" [ 
  | eval type=mvappend(type, if(isnotnull('<<FIELD>>'), '<<FIELD>>', null())) ]

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

@ea-2023 If your JSON field 3 has one or more of those field attributes, then after the spath you can do

| foreach "Attribute Name", "Resource Name", and "ID" [ 
  | eval type=mvappend(if(isnotnull('<<FIELD>>'), '<<FIELD>>', type) ]

which will cycle through the 3 desired fields and for any of the 3 that exist, will make a multi-value field called type with any that do exist.

 

ea-2023
Path Finder

@bowesmana just wanted to tag you for awareness, not sure how this community notifies on comment replies. 🙂

0 Karma

ea-2023
Path Finder

What is expected for <<FIELD>>? What value would I add here to get it to cycle through all 3 (or more) values extracted from the spath object?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Splunk Docs is your friend.  See foreach.  <<FIELD>> is a symbol in SPL's foreach command, not to be replaced by the user.  SPL will iterate this symbol over the field names following the command name.

ea-2023
Path Finder

I get this error whether I use <<FIELD>> or <<ITEM>>. 
Error in 'EvalCommand': The arguments to the 'mvappend' function are invalid.

| eval type=mvappend(if(isnotnull('<<ITEM>>'), '<<ITEM>>', type)) ]

0 Karma

yuanliu
SplunkTrust
SplunkTrust

There's an omission in @bowesmana's mvappend

 

| foreach "Attribute Name", "Resource Name", "ID" [ 
  | eval type=mvappend(type, if(isnotnull('<<FIELD>>'), '<<FIELD>>', null())) ]

 

ea-2023
Path Finder

Thank you @bowesmana and @yuanliu for helping with this! 

This worked, but I just had to add a ) at the end to balance the parenthesis. The values when tabled out all include "event" in addition to the targeted values, which I'm guessing is somehow coming from the top element in the array. Not a huge problem for me, but figured I'd mention it.

Results:
event
name-resource-121sg6fe

event
name-resource-387762fg

 

Sample JSON array:

event: {
     AccountId: xxxxxxxxxx
     CloudPlatform: CloudProvider
     CloudService: Service
     ResourceAttributes: {"key1": "value1", "key2": "value2", "key3": value3, "key4": [{"key": "value", "key": "value"}], "Resource Name": "name-resource-121sg6fe", etc}
   }

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

(I was just trying to clarify @bowesmana's syntax and have not related to the original question.)  It is always a good practice to illustrate sample/mock data at the beginning.  Now, the sample JSON needs further clarification in relationship to your OP.

  1. Is this snippet the field3 you referred to in OP?  If not, which one is field3?
  2. This snippet contains a key "event.ResourceAttributes.Resource Name".  I assume that this is "Resource Name" you referred to in OP.  Is this correct?
  3. Which fields correspond to "Attribute Name" and "ID" in OP?

Importantly, when illustrating structured data like JSON, make sure your illustration is compliant.  I tried to reconstruct a compliant JSON from your illustration.  This is what I come up with:

 

{"event": {
     "AccountId": "xxxxxxxxxx",
     "CloudPlatform": "CloudProvider",
     "CloudService": "Service",
     "ResourceAttributes": {"key1": "value1", "key2": "value2", "key3": "value3", "key4": [{"key": "value", "key": "value"}], "Resource Name": "name-resource-121sg6fe", "etc": "etc"}
   }
}

 

Does this truly reflect your original data structure?  If the snippet is field3, here is an emulation to check if my understanding is correct:

 

| makeresults
| eval field3 = "{\"event\": {
     \"AccountId\": \"xxxxxxxxxx\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"key2\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"Resource Name\": \"name-resource-121sg6fe\", \"etc\": \"etc\"}
   }
}"
| spath input=field3
| fields - field3 _*

 


event.AccountId
event.CloudPlatformevent.CloudServiceevent.ResourceAttributes.Resource Nameevent.ResourceAttributes.etcevent.ResourceAttributes.key1event.ResourceAttributes.key2event.ResourceAttributes.key3
event.ResourceAttributes.key4{}.key
xxxxxxxxxxCloudProviderServicename-resource-121sg6feetcvalue1value2value3
value
value

Is this close?

Also, if you have a specific output format in mind, you should illustrate what the output should look like when using this sample data.

0 Karma

ea-2023
Path Finder

Edit: I realized I may have given the wrong impression by posting my example JSON object. The intention was to extract a value from any of the 3 fields into the single column. 

1. Yes, field3 is "Resource Name". 

2. Yes

3. I used "Attribute Name" and "ID" as placeholder values for the real data (just sanitizing the values/fields). They were just meant to give an example. In hindsight I think I should have stuck with generic names "field1-3".

 

Output format:

event.AccountIdevent.CloudPlatformevent.CloudServiceAdditional Details
xxxxxxxxxxCloudProviderServicename-resource-121sg6fe
ID-12132423fgrg
other-named-attribute
0 Karma

yuanliu
SplunkTrust
SplunkTrust

To simplify things, I will just follow your initial clue and assume that ID and Name are also part of event.ResourceAttributes.

 

index=test field1=* field2=*

| spath input=field3
| foreach "event.ResourceAttributes.Name", "event.ResourceAttributes.Resource Name", "event.ResourceAttributes.ID" [ 
  | eval type=mvappend(type, if(isnotnull('<<FIELD>>'), '<<FIELD>>', null())) ]
| stats values(type) as "Additional Details" by event.AccountId event.CloudPlatform event.CloudService

 

If they are in some other nodes, just rewrite the foreach list.

Here is a fuller emulation that I made up based on your singular mock data point.

 

| makeresults
| eval field3 = mvappend("{\"event\": {
     \"AccountId\": \"xxxxxxxxxx2\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"ID\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"Resource Name\": \"name-resource-121sg6fe\", \"etc\": \"etc\"}
   }
}" ``` has ID, Resource Name, no Name ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx1\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"key2\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"Resource Name\": \"name-resource-121sg6fe\", \"etc\": \"etc\"}
   }
}" ``` has Resource Name, no others ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx2\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"Name\": \"value1\", \"key2\": \"value2\", \"ID\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"etc\": \"etc\"}
   }
}" ``` has ID, Name, no Resource Name ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx1\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"key2\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"etc\": \"etc\"}
   }
}" ``` has none of the three ```)
| mvexpand field3
``` the above sort of emulates
index=test field1=* field2=*
```
| eval type = json_object()
| spath input=field3
| foreach "event.ResourceAttributes.Name", "event.ResourceAttributes.Resource Name", "event.ResourceAttributes.ID" [ 
  | eval type=mvappend(type, if(isnotnull('<<FIELD>>'), '<<FIELD>>', null())) ]
| stats values(type) as "Additional Details" by event.AccountId event.CloudPlatform event.CloudService

 

What this does is to add variations to which of "Name", "Resource Name", and "ID" do or do not appear in each event.  You can play with it and compare with real data.  The output is

event.AccountIdevent.CloudPlatformevent.CloudService
Additional Details
xxxxxxxxxx1CloudProviderService
name-resource-121sg6fe
{}
xxxxxxxxxx2CloudProviderService
name-resource-121sg6fe
value1
value2
value3
{}

 

One more suggestion: @bowesmana's idea is just to use foreach.  The above format does not group the present or missing attributes in a very distinguishable manner.  An alternative to using mvappend inside the foreach subsearch is to also carry the input keys in addition to values in "Additional Details".  Using a JSON structure is one such method.

 

index=test field1=* field2=*
| eval type = json_object()
| spath input=field3
| foreach "event.ResourceAttributes.Name", "event.ResourceAttributes.Resource Name", "event.ResourceAttributes.ID" [ 
  | eval type=json_set(type, replace("<<FIELD>>", "event.ResourceAttributes.", ""), '<<FIELD>>') ]
| stats values(type) as "Additional Details" by event.AccountId event.CloudPlatform event.CloudService

 

This is a full emulation:

 

| makeresults
| eval field3 = mvappend("{\"event\": {
     \"AccountId\": \"xxxxxxxxxx2\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"ID\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"Resource Name\": \"name-resource-121sg6fe\", \"etc\": \"etc\"}
   }
}" ``` has ID, Resource Name, no Name ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx1\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"key2\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"Resource Name\": \"name-resource-121sg6fe\", \"etc\": \"etc\"}
   }
}" ``` has Resource Name, no others ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx2\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"Name\": \"value1\", \"key2\": \"value2\", \"ID\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"etc\": \"etc\"}
   }
}" ``` has ID, Name, no Resource Name ```,
"{\"event\": {
     \"AccountId\": \"xxxxxxxxxx1\",
     \"CloudPlatform\": \"CloudProvider\",
     \"CloudService\": \"Service\",
     \"ResourceAttributes\": {\"key1\": \"value1\", \"key2\": \"value2\", \"key3\": \"value3\", \"key4\": [{\"key\": \"value\", \"key\": \"value\"}], \"etc\": \"etc\"}
   }
}" ``` has none of the three ```)
| mvexpand field3
``` the above sort of emulates
index=test field1=* field2=*
```
| eval type = json_object()
| spath input=field3
| foreach "event.ResourceAttributes.Name", "event.ResourceAttributes.Resource Name", "event.ResourceAttributes.ID" [ 
  | eval type=json_set(type, replace("<<FIELD>>", "event.ResourceAttributes.", ""), '<<FIELD>>') ]
| stats values(type) as "Additional Details" by event.AccountId event.CloudPlatform event.CloudService

 

And output from this emulation.

event.AccountIdevent.CloudPlatformevent.CloudService
Additional Details
xxxxxxxxxx1CloudProviderService
{"Name":null,"Resource Name":"name-resource-121sg6fe","ID":null}
{"Name":null,"Resource Name":null,"ID":null}
xxxxxxxxxx2CloudProviderService
{"Name":"value1","Resource Name":null,"ID":"value3"}
{"Name":null,"Resource Name":"name-resource-121sg6fe","ID":"value2"}
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please can you share some sample events and a mockup of what your expect output would look like

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...