how to check that the value of one field is present in another field?

[mah]

Hi,

I have 2 events which are not containing same fields. 

Event A : 

{ [-]
   account_id: 1234
   description: Test
   id: efgh

}

Event B :

{ [-]

account_id: 5678
description: Dev
id: abcd
name: [ [-]
         { [-]

group_id: efgh

}
         { [-]

group_id: ijkl

}

{ [-]

group_id: mnop

}

I have a table like this : 

id	group_id
abcd	efgh ijkl mnop
efgh

I want to check if the id "efgh" is a value of "group_id" field. 

I tried this : 

| eval result=if(like(id,"%".group_id."%"),"OK","Not OK")

But it gave me "Not OK" as result.

Can you help me please ?

Thanks !

[bowesmana]

In your example it looks like the table has two rows, so you cannot use the eval to check the value of an id in row 2 against the data in another field in row 1

maybe if you could share how you are getting from your JSON data example to your table it might be easier to give an answer.

You can simply do this with your table to get the result you are after, but I suspect that may not be a solution

| makeresults
| eval _raw="id	group_id
abcd	efgh ijkl mnop
efgh	"
| multikv forceheader=1
| eval group_id=split(group_id, " ")
| table id group_id
| filldown group_id
| eval result=if(!isnull(mvfind(group_id, id)), "OK", "NotOK")

[mah]

Hi @bowesmana,

Thanks for your answer. 

You were right your search did not work. 

Here is my query : 

index="A" sourcetype="B"  sg-19c
| dedup id
| spath output=instances path=instances{}.id
| eval instances_count=mvcount(instances)
| where isnull(instances_count)
| fields account_id region name id vpc_id insight
| eval account_sg=account_id."___".id
| spath output=group_id path=rules{}.grants{}.group_id

I look for events containing "sg-19c". It gave me 2 events. Here is a sample of my json data : 

Event 1:

{ [-]
   account_id: 1111
   description: test
   id: sg-f65
   instances: [ [+]
   ]
   name: test
   owner_id:1111
   region: 222
   rules: [ [-]
     { [-]
       from_port: xxx
       grants: [ [-]
         { [-]
           cidr_ip: null
           group_id: sg-19c
           name: null
           owner_id: 1111
         } { [-]
           cidr_ip: null
           group_id: sg-e
           name: null
           owner_id: 1111
         } { [-]
           cidr_ip: null
           group_id: sg-8
           name: null
           owner_id: 1111
         } { [-]
           cidr_ip: null
           group_id: sg-0
           name: null
           owner_id: 1111
         } { [-]
           cidr_ip: null
           group_id: sg-1
           name: null
           owner_id: 1111
         }]}]

Event 2 :

{ [-]
   account_id: 1111
   description: test
   id: sg-19c

}

If I make a table command, I get this table in my first post : 

What I want is to extract "id" present in "group_id" field. 

Hope you can help me !

Thanks a lot.

[bowesmana]

Here is an example based on your data sample

| makeresults
| eval x="{
  \"account_id\": \"1111\",
  \"description\": \"test\",
  \"id\": \"sg-f65\",
  \"instances\": [],
  \"name\": \"test\",
  \"owner_id\": \"1111\",
  \"region\": \"222\",
  \"rules\": [{
    \"from_port\": \"xxx\",
    \"grants\": [{
      \"cidr_ip\": null,
      \"group_id\": \"sg-19c\",
      \"name\": null,
      \"owner_id\": \"1111\"
    }, {
      \"cidr_ip\": null,
      \"group_id\": \"sg-e\",
      \"name\": null,
      \"owner_id\": \"1111\"
    }, {
      \"cidr_ip\": null,
      \"group_id\": \"sg-8\",
      \"name\": null,
      \"owner_id\": \"1111\"
    }, {
      \"cidr_ip\": null,
      \"group_id\": \"sg-0\",
      \"name\": null,
      \"owner_id\": \"1111\"
    }, {
      \"cidr_ip\": null,
      \"group_id\": \"sg-1\",
      \"name\": null,
      \"owner_id\": \"1111\"
    }]
  }]
}
XXX
{
   \"account_id\": \"1111\",
   \"description\": \"test\",
   \"id\": \"sg-19c\"
}
"
| makemv delim="XXX" x
| mvexpand x
| spath input=x
| fields - x
| eval account_sg=account_id."___".id
| rename rules{}.grants{}.group_id as group_id
| table id group_id
| eval COMMENT="This will validate your data"
| stats values(eval(if(isnull(group_id), id, null()))) as check_ids values(eval(if(isnotnull(group_id), id, null()))) as containing_ids values(group_id) as group_id
| eval exists=if(isnotnull(mvfind(group_id, check_ids)), 1, 0)

the last two lines will do the test, the rest is setting up your data

The idea is that if you are searching for an id (e.g. sg-19c) then it will either be a root level id or an id in the rules grants right?

So, assuming you will always only have two rows if you are searching a single id, then the stats will aggregate the two types of row to a single row, where you can then use mvfind to check the presence. In this case, the exists value is 1 as the data exists.

However, this will most likely need validation with your data set and expected outcomes and use case.

Hope this helps.

[mah]

Hi @bowesmana 

Thanks again for your answer. However, I tried to applied the 2 last commands in my query and it does not react as your test :

The purpose of the search is to check if the value in field "id" is present in the field "group_id" and then extract the value of those "id". 

To give you an example, I have filtered on a sg-xxx which returns 2 events: an event in which it appears in the value of the id field, and another event in which it appears in the group_id field.
When the sg-xxx value of the id field appears in a group_id field then I want to extract it.

But some ids returns only ONE event (the one with id field). Is this will cause an issue when I will run your command ? 

Your query is completely what I was looking for and looks pretty good but I don't know if I apply it well because the "check_ids" fields returns nothing. 

Can you help me again please ? 

Thanks a lot !

[mah]

Hi again @bowesmana 

I find the issue why the search did not work : with the filter sg-xxxdd5 in the first command line we can see in raw events that it returns "group_id" fields with "null" value (it was not the case days before as we can see in my previous post) : 

So your stats command does not work in this case ...

Without filter, your command return result like that :

Probably because of stats value...

All of them are identified with 0 value in "exists" column, whereas we should have both values : I tested the request by filtering on several sg-xxx of the list and after checking, some of them return only one event (the one with the id field) and not 2 including one with a group_id field, so they should appear with value as "0" in "exists" column, and other sg-xxx are like in my first post (with 2 events, one with id, other with the value id in group_id) and should appear with "1" as value of "exists" column. 

How can I deal with that ? 

Thanks again for your help. 

[bowesmana]

The solution will depend on what your expected output must be. If you are always filtering for a single id then the solution should work - but if you expect this to be a solution for an unfiltered list of ids then it would need to be different, as it's not just dealing with 2 rows as in your original example.

Can you provide an example of a table you would have where you are not filtering if that is what you need

[mah]

Hi @bowesmana 

Without filtering on a particular id here is the result of my search : 

index="A" sourcetype="B" source="*C" 
| dedup id
| spath output=instances path=instances{}.id
| eval instances_count=mvcount(instances)
| where isnull(instances_count)
| fields account_id region name id vpc_id insight
| eval account_sg=account_id."___".id
| spath output=group_id path=rules{}.grants{}.group_id
| table id group_id

I tried a query like that : 

index="A" sourcetype="B" source="C" 
| dedup id
| spath output=instances path=instances{}.id
| eval instances_count=mvcount(instances)
| where isnull(instances_count)
| fields account_id region name id vpc_id insight
| eval account_sg=account_id."___".id
| table id
| rename id as group_id
| join type=inner group_id [search index="A" sourcetype="B" source="C" | dedup id| spath output=instances path=instances{}.id| eval instances_count=mvcount(instances)| where isnull(instances_count)| fields account_id region name id vpc_id | spath output=group_id path=rules{}.grants{}.group_id | mvexpand group_id | table group_id ] | rename group_id as id

What do you think ?