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

[amanda_dg]

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 🙂

[bowesmana · Accepted solution]

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

[amanda_dg]

Where to include the index and source type? 

[bowesmana]

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

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

[amanda_dg]

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? 🙂

[bowesmana]

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.

[amanda_dg]

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 

[PickleRick]

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

[PickleRick]

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"

[amanda_dg]

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 🙂