topic Re: How to populate a single column with dynamic field values? in Splunk Search

How to populate a single column with dynamic field values?

ea-2023 — Thu, 15 Feb 2024 18:40:11 GMT

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

Re: How to populate a single column with dynamic field values?

ITWhisperer — Thu, 15 Feb 2024 19:06:01 GMT

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

Re: How to populate a single column with dynamic field values?

bowesmana — Thu, 15 Feb 2024 19:54:02 GMT

@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.

Re: How to populate a single column with dynamic field values?

ea-2023 — Thu, 15 Feb 2024 20:11:40 GMT

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?

Re: How to populate a single column with dynamic field values?

ea-2023 — Thu, 15 Feb 2024 21:01:37 GMT

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

Re: How to populate a single column with dynamic field values?

yuanliu — Thu, 15 Feb 2024 21:53:15 GMT

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.

Re: How to populate a single column with dynamic field values?

ea-2023 — Thu, 15 Feb 2024 22:39:33 GMT

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)) ]

Re: How to populate a single column with dynamic field values?

yuanliu — Fri, 16 Feb 2024 23:58:14 GMT

There's an omission in @bowesmana's mvappend

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

Re: How to populate a single column with dynamic field values?

ea-2023 — Fri, 16 Feb 2024 15:01:39 GMT

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} }

Re: How to populate a single column with dynamic field values?

yuanliu — Sat, 17 Feb 2024 00:28:27 GMT

(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.

Is this snippet the field3 you referred to in OP? If not, which one is field3?
This snippet contains a key "event.ResourceAttributes.Resource Name". I assume that this is "Resource Name" you referred to in OP. Is this correct?
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.CloudPlatform	event.CloudService	event.ResourceAttributes.Resource Name	event.ResourceAttributes.etc	event.ResourceAttributes.key1	event.ResourceAttributes.key2	event.ResourceAttributes.key3	event.ResourceAttributes.key4{}.key
xxxxxxxxxx	CloudProvider	Service	name-resource-121sg6fe	etc	value1	value2	value3	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.

Re: How to populate a single column with dynamic field values?

ea-2023 — Mon, 19 Feb 2024 20:40:31 GMT

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.AccountId	event.CloudPlatform	event.CloudService	Additional Details
xxxxxxxxxx	CloudProvider	Service	name-resource-121sg6fe ID-12132423fgrg other-named-attribute

Re: How to populate a single column with dynamic field values?

yuanliu — Wed, 21 Feb 2024 07:13:11 GMT

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.AccountId	event.CloudPlatform	event.CloudService	Additional Details
xxxxxxxxxx1	CloudProvider	Service	name-resource-121sg6fe {}
xxxxxxxxxx2	CloudProvider	Service	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.AccountId	event.CloudPlatform	event.CloudService	Additional Details
xxxxxxxxxx1	CloudProvider	Service	{"Name":null,"Resource Name":"name-resource-121sg6fe","ID":null} {"Name":null,"Resource Name":null,"ID":null}
xxxxxxxxxx2	CloudProvider	Service	{"Name":"value1","Resource Name":null,"ID":"value3"} {"Name":null,"Resource Name":"name-resource-121sg6fe","ID":"value2"}