topic Query to return events when all the objects of an array where a field value is equal to a certain value in Splunk Search

Query to return events when all the objects of an array where a field value is equal to a certain value

rajesh143rs — Thu, 28 Mar 2024 17:03:23 GMT

I need help with a splunk query to return events where an array of object contains certain value for a key in all the objects of an array

Event 1: { 
            list: [ 
                    {"name": "Hello", "type": "code"}, 
                    {"name": "Hello", "type": "document"}
                   ] 
         } 
Event 2: { 
            list: [ 
                    {"name": "Hello", "type": "code"}, 
                    {"name": "World", "type": "document"}
                   ] 
         } 
Event 3: { 
            list: [ 
                    {"name": "Hello", "type": "document"}, 
                    {"name": "Hello", "type": "document"}
                   ] 
         }

filters: In the list array,

the first object in an array should have "type": "code"

In all the items in the list array should have "name": "Hello"

Expected output: In the above list of events the query should return 'Event 1', where first item - list[0].type = code and list has all the items with "name": "Hello"

I tried multiple ways like search

list{}.name="Hello" This was returning the events which had atleast 1 element having name: Hello

However i was able to achieve checking for 1st filter as below

| eval conflict = mvindex(list, 0) | spath input=conflict | search type=code

If someone can help in achieving both the filters in a query that will be helpful. Thanks in advance

Re: Query to return events when all the objects of an array where a field value is equal to a certain value

yuanliu — Thu, 28 Mar 2024 18:51:33 GMT

Here is an idea: Select events in which list{}.name has one unique value "Hello", and has a value of "code" as the first element of list{}.type.

| where mvindex('list{}.type', 0) == "code" AND 'list{}.name' == "Hello" AND mvcount(mvdedup('list{}.name')) == 1

However, given that list is an array, selecting only the first element for matching may not be what the use case demands. (Work with developers to figure out what semantics array order may convey.) Here is one to select any element with value "code".

| where 'list{}.type' == "code" AND 'list{}.name' == "Hello" AND mvcount(mvdedup('list{}.name')) == 1

Here is an emulation of your mock data for you to play with and compare with real data

| makeresults | fields - _* | eval data = mvappend("{ \"list\": [ {\"name\": \"Hello\", \"type\": \"code\"}, {\"name\": \"Hello\", \"type\": \"document\"} ] }", "{ \"list\": [ {\"name\": \"Hello\", \"type\": \"code\"}, {\"name\": \"World\", \"type\": \"document\"} ] }", "{ \"list\": [ {\"name\": \"Hello\", \"type\": \"document\"}, {\"name\": \"Hello\", \"type\": \"document\"} ] }") | mvexpand data | rename data AS _raw | spath ``` data emulation above ```

With this data, output is the same for both variants

_raw	list{}.name	list{}.type
{ "list": [ {"name": "Hello", "type": "code"}, {"name": "Hello", "type": "document"} ] }	Hello Hello	code document

Re: Query to return events when all the objects of an array where a field value is equal to a certain value

rajesh143rs — Fri, 29 Mar 2024 00:59:54 GMT

Hi @yuanliu , Thanks for the response
the first query, as you have mentioned it 'Select events in which list{}.name has one unique value "Hello" '
is there a way select events in which all the objects should contain name == "Hello" instead of just one unique value?

To clarify about your query - 'given that list is an array, selecting only the first element for matching may not be what the use case demands'
I understand that it sounds weird 🙂, but our use case is about selecting events where the first object in an array/list should have

type == "code"

Re: Query to return events when all the objects of an array where a field value is equal to a certain value

yuanliu — Fri, 29 Mar 2024 02:45:55 GMT

To clarify about your query - 'given that list is an array, selecting only the first element for matching may not be what the use case demands'
I understand that it sounds weird 🙂, but our use case is about selecting events where the first object in an array/list should have
type == "code"

What I was trying to say is: Do you select this one, when type == "code" is the second element?

{ list: [ {"name": "Hello", "type": "document"}, {"name": "Hello", "type": "code"} ] }

If you want to select this kind of events as well as the other kind, only the second search will work. If you want to select an event only if its first element contains type == "code", use the first search.

the first query, as you have mentioned it 'Select events in which list{}.name has one unique value "Hello" '
is there a way select events in which all the objects should contain name == "Hello" instead of just one unique value?

This gets confusing. My rephrasing "has one unique value 'Hello'" is based on your OP statement

In all the items in the list array should have "name": "Hello"

Did I misunderstand this?

Anyway, my searches do retrieve Event 1 as expected. Is there any problem with them?

Re: Query to return events when all the objects of an array where a field value is equal to a certain value

rajesh143rs — Fri, 29 Mar 2024 03:14:36 GMT

Hi @yuanliu , Thanks a lot, your query works 🙏

Re: Query to return events when all the objects of an array where a field value is equal to a certain value

rajesh143rs — Fri, 29 Mar 2024 03:18:52 GMT

@yuanliu There was a misunderstanding from my end about the query.
Your suggested query works great. Thanks again 👍