how to check whether a particular value is same in a multivalued field?

[anooshac]

Hi Everyone,

I have created a mutli valued field by using some of the fields called as combi_fields. I am showing those multivalued fields as | stats values(*) as * by identity.

Now I have a table with Identity and combi_fields.

In combi fields i want to check for a data whether it is same in all the mutivalued data for a given Identity.

For example,

Identity                                  combi_fields

ABC                                         abcdefg - 231 - 217 - Passed - folder1- folder2

                                                  abcdefg - 441 - 456 - Passed - folder1- folder2

                                                  abcdefg - 113 - 110 - Passed - folder1- folder2

In the above example all the 1st data is same. If it is same i have to consider the greatest number and give its status as output. Like ABC abcdefg  Passed

there might be different data in the 1 st place like below

ABC                                         abcdefg - 231 - 217 - Passed - folder1- folder2

                                                  abcdefg - 441 - 456 - Passed - folder1- folder2

                                                  xyzabc- 113 - 110 - Passed - folder1- folder2

                                                  xyzabc- 201 - 219- Passed - folder1- folder2

Here is hould show as ABC abcdefg Passed

                                             ABC xyzabc Passed.

How can i do this? How can i compare among a field?

[ITWhisperer]

You probably need to use mvexpand on the combi_fields then split it or parse it into separate fields, and use stats/eventstats to find the highest number (which number are you talking about?) for each "data" within each identity, and take the "status" from that event.

Having said that, you might be better off going back a step or two i.e. before the stats values(*) as * and whatever commands you used to combine the fields in the first place, as it seems you have just made it harder for yourself.

[anooshac]

Hi @ITWhisperer ,

Yeah, i also feel the same. But if i take stats values of every data by the Identity, i am not able to get the desired results like i explained. Is there any better way ?

At the end i should be having Identity, data, status in a table as i described.

I am finding it very hard to get a logic for this.

[ITWhisperer]

It would help if you could share some anonymised raw events in a code block to prevent formatting corruptions, that way we can see what you are working with and be better able to guide you.

[anooshac]

Hi @ITWhisperer , I will give some sample data like this.

In my events i have data Identity, Test_ID, Test_Data and Test_Status. I want to find maximum Test_ID for given Test_Data and then show a table with all the above fields only for the maximum Test_ID.

First i used eventstats to get max Test_ID, then i am assigning it to Test_ID and then i am creating a table.

Is is the correct way? or should i have to do anything else?

[ITWhisperer · Accepted solution]

If I understand your description of your data correctly, you could try something like this

| eventstats max(Test_ID) as max_Test_ID by Identity, Test_Data
| where Test_ID = max_Test_ID
| table Identity, Test_Data, Test_ID, Test_Status

[anooshac]

Hi @ITWhisperer ,

I did event stats like you mentioned and i am able to get the proper table. Thank you so much.

I have a list with 4 data, and i have to check whether the field Test_Data has last data from the list. If yes I have to consider only those rows in the table. How can i compare this?

[ITWhisperer]

Do you mean something like this

| eventstats last(Test_Data) as last_Test_Data by Identity
| where Test_Data = last_Test_Data

[anooshac]

No, i am having a separate comma separated value like bl01,bl02,bl03,0_Ref_res.

These are the folder names. I want to check whether Test_Data have the last folder name. If yes i want to consider that particular row. If last value is not present it should consider bl03. If not it should consider bl02 etc.

I am trying to find a logic for this.

[ITWhisperer]

Is this list the same for all identities? Is it static or does it vary over time?

[anooshac]

@ITWhisperer ,

Yes occasionally the list changes. So I thought of saving the list as a macro or something. How can I achieve this?

[ITWhisperer]

| eval test_data_index=mvfind(split("bl01,bl02,bl03,0_Ref_res", ","), Test_Data)
| eventstats max(test_data_index) as max_test_data_index by Identity
| where test_data_index = max_test_data_index

[PickleRick]

Unless I'm misunderstanding something if you just want to list a result for a maximum Test_ID value your initial approach to use eventstats is good but you can simply filter by that value.

So you'll get something like

| eventstats max(Test_ID) as maxtestid
| where Test_ID=maxtestid

Unless you want something else and we have some miscommunication here 🙂

[anooshac]

Hi @PickleRick ,

I did the same like you mentioned and created a table for the fields. But im getting some duplicates values .  Is there anything else i have to do