topic Re: How to search for an id where its column values does not include a certain value in Splunk Search

How to search for an id where its column values does not include a certain value

amanda_dg — Thu, 28 Apr 2022 03:33:46 GMT

Hi everyone,

I am new to SPLUNK and I am trying to search for distinct IDs where its PRODUCT column does not include certain value.

For example. If I assume I have the following table called TABLE1:

ID	PRODUCT	PHONE
1	A	999999
2	A	888888
2	B	888888
1	C	999999
3	D	777777
3	C	777777
3	B	777777
4	B	666666
4	D	666666
5	A	555555
5	B	555555
5	D	555555
...	....	.....

What I want is the following output when I want to look for IDs where its Product column values does not equal C:

ID	PHONE
2	888888
4	666666
5	555555
....	.....

How to write the search query in splunk? pls help 🙂

Re: How to search for an id where its column values does not include a certain value

bowesmana — Thu, 28 Apr 2022 03:23:47 GMT

This will do that - I assume that the last 55555 should have the extra 5 at the end

| makeresults | eval _raw="ID PRODUCT PHONE 1 A 999999 2 A 888888 2 B 888888 1 C 999999 3 D 777777 3 C 777777 3 B 777777 4 B 666666 4 D 666666 5 A 555555 5 B 555555 5 D 555555" | multikv forceheader=1 | table ID PRODUCT PHONE | stats values(PRODUCT) as PRODUCTS by ID PHONE | where isnull(mvfind(PRODUCTS, "C")) | fields - PRODUCTS

You can run this search and it will show you that output - the bit that does the work is from the stats command

Re: How to search for an id where its column values does not include a certain value

amanda_dg — Thu, 28 Apr 2022 03:34:35 GMT

Where to include the index and source type?

Re: How to search for an id where its column values does not include a certain value

bowesmana — Thu, 28 Apr 2022 04:18:06 GMT

I assumed you have a search that already produces that table.

You put your search that produces that table before the stats command

Re: How to search for an id where its column values does not include a certain value

amanda_dg — Thu, 28 Apr 2022 06:10:41 GMT

Hi thank you so much for helping me out 🙂 It worked

Can I also ask another question?

If I want to find IDs where it purchased Product C but not A, how should I write the search? 🙂

Re: How to search for an id where its column values does not include a certain value

bowesmana — Thu, 28 Apr 2022 06:22:32 GMT

You would use that mvfind again

| where isnotnull(mvfind(PRODUCTS, "C")) AND isnull(mvfind(PRODUCTS, "A"))

There are often a number of ways of achieving the same thing, but this works.

Note - if your question is answered, please mark the question as answered, so others can benefit.

Re: How to search for an id where its column values does not include a certain value

PickleRick — Thu, 28 Apr 2022 08:10:15 GMT

With multivalued fields you can match a bit easier in terms of writing the conditions (but a bit more confusing in interpreting it :-))

Your condition may be rewritten simply as

| where PRODUCTS="C" AND NOT PRODUCTS="A"

Mind you, it's not the same as

| where PRODUCTS="C" AND PRODUCTS!="A"

Re: How to search for an id where its column values does not include a certain value

amanda_dg — Fri, 29 Apr 2022 01:19:14 GMT

Thank you for your reply. if I want to look for multiple PRODUCTS can I use "IN"? Like the following:

| where PRODUCTS IN (A, B, C) AND NOT PRODUCTS IN (G, D)

How should I put it?

Thank you 🙂

Re: How to search for an id where its column values does not include a certain value

amanda_dg — Fri, 29 Apr 2022 01:36:14 GMT

Thank you for your reply. if I want to look for multiple PRODUCTS how should I write? I tried the below search but did not work

where isnotnull(mvfind(PRODUCTS, (A,B,C))) AND isnull(mvfind(PRODUCTS,(G,D)))

How should I put it?

Thank you

Re: How to search for an id where its column values does not include a certain value

PickleRick — Fri, 29 Apr 2022 06:47:20 GMT

Multivalued fields are sometimes a bit counterintuitive to use but the beauty of them is that your conditions are avaluated for each single value in a multivalued field.

So if you want to limit your result to those in which _any_ of the values in the multivalues field matches any of the values from the given set you can do

| search field IN ("value1","value2","value3)

But if you want to find results in which you have both "value1" and "value2" in a multivalued field you can use this - as I said - a bit counterintuitive syntax

| search field="value1" AND field="value2"

If you analyse it in context of a single-valued field it seems pointless because the field cannot be two different values at once. But with multivalued fields since the condition is matched to each value separately, one value in the multivalued field will match "value1" and another one will match "value2".

The opposite is true as well - if you do the negative match on multivalue field if you match

| search field!="value1"

You will get all the results in which at least one of the values in multivalued fields is different than "value1" (the "value1" may still be present within the multivalue field!)

But if you match with

| search NOT field="value1"

You'll get only those results when there is no "value1" among values in a multivalued field