topic Re: how to check whether a particular value is same in a multivalued field? in Dashboards & Visualizations

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

anooshac — Tue, 14 May 2024 09:00:04 GMT

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?

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

ITWhisperer — Tue, 14 May 2024 09:28:25 GMT

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.

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

anooshac — Tue, 14 May 2024 09:53:53 GMT

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.

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

ITWhisperer — Tue, 14 May 2024 14:30:10 GMT

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.

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

anooshac — Wed, 15 May 2024 05:46:34 GMT

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?

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

PickleRick — Wed, 15 May 2024 06:26:52 GMT

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 🙂

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

anooshac — Wed, 15 May 2024 07:33:30 GMT

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

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

ITWhisperer — Wed, 15 May 2024 08:19:47 GMT

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

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

anooshac — Wed, 15 May 2024 08:36:39 GMT

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?

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

ITWhisperer — Wed, 15 May 2024 08:44:09 GMT

Do you mean something like this

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

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

anooshac — Wed, 15 May 2024 10:15:59 GMT

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.

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

ITWhisperer — Wed, 15 May 2024 12:33:32 GMT

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

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

anooshac — Wed, 15 May 2024 16:50:23 GMT

@ITWhisperer ,

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

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

ITWhisperer — Wed, 15 May 2024 17:36:58 GMT

| 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