topic How to create a search that compares two lists of field values? in Splunk Search

How to create a search that compares two lists of field values?

C_Sparn — Mon, 14 Apr 2014 14:02:41 GMT

Hello,

I'm looking for a possibility to compare two lists of field values from two different sourecetypes. For that I started a search like:

sourcetype=test1 OR sourcetype=test2 | rex field=_raw "field1" | rex field=_raw "field2"

After this search, I get field1 and field2 and both have multiple values.
Now I want to check if the values of field1 contain the values of field2.
The end result should be a list of values from field2 that are not included in the values list of field1.

I thought of something like this Python code, but with Splunk functions:

for value in field2.values:
    if value NOT in field1.values:
        print(value)

Thanks for any suggestions!

Greetings

Re: How to create a search that compares two lists of field values?

MuS — Mon, 14 Apr 2014 14:13:25 GMT

Hi C_Sparn,

try something like this:

index=_internal sourcetype="splunkd" OR sourcetype="splunk_web_access" 
| streamstats count by status, idx, sourcetype 
| stats values(idx) AS idx, values(status) AS status, values(sourcetype) AS sourcetype 
| mvexpand status 
| eval Status = if(match(idx,status), "MATCH", "NO MATCH") 
| table status, idx, Status
| where Status="NO MATCH"

Update: this is a run everywhere example of the final search used to get the expected result. streamstats hint came from a originally hint to use stats made by @somesoni2 🙂

hope this helps to get you started ...

cheers, MuS

Re: How to create a search that compares two lists of field values?

C_Sparn — Mon, 14 Apr 2014 15:26:50 GMT

Hi,
Thank you for the answer.It seems to be the right search but now i have another problem.
The search is loading for 20 minutes now but it has just 10%!But i think that splunk has a problem with 2 mvexpand options in this search, because the results have 4500 and 2100 values.
You can see that because

| stats values(field1) AS t1 values(field2) AS t2| mvexpand t1 | mvexpand t2 |table t1

is already very slow.
Maybe some values are twice in the lists.
How can i delete dublicate values?
Or is there maybe another reason why splunk is that slow?
Greeting

Re: How to create a search that compares two lists of field values?

somesoni2 — Mon, 14 Apr 2014 15:37:21 GMT

Before the "|stats values(field1)...." , put another stats command "|stats count by field1,field2". This should improve the performance.

Re: How to create a search that compares two lists of field values?

C_Sparn — Tue, 15 Apr 2014 07:44:18 GMT

Yes you were right it improved the speed of that search a lot but for me there are no results now.
The search look like this:

Like you see in this example i just wanted to table the results without any comparison of t1 and t2, but anyway there are no results. With removing one of the fields i get the right results! Like this:

|stats count by field1
|stats values(field1) AS t1
|mvexpand t1
|table t1

Greetings

Re: How to create a search that compares two lists of field values?

MuS — Tue, 15 Apr 2014 08:26:19 GMT

the problem is that with @somesoni2's first stats you either have field1 or field2 in the event but not both. This will work if you change it to
| streamstats count by field1, field2

Next, if don't want to compare fields you don't need to mvexpand just use a table and you're done. See this run everywhere example:

index=_internal sourcetype="splunkd" OR sourcetype="splunk_web_access" | streamstats count by status, sourcePort, sourcetype | stats values(sourcePort) AS sourcePort, values(status) AS status, values(sourcetype) AS sourcetype | table status, sourcePort, sourcetype

Re: How to create a search that compares two lists of field values?

C_Sparn — Tue, 15 Apr 2014 09:12:56 GMT

Yes its true, streamstats is the solution.
And with eval status = if(match(field2,field1),"","")
it is working.
I think now I understand what you want to say.
I think for me this is working now:

Re: How to create a search that compares two lists of field values?

C_Sparn — Tue, 15 Apr 2014 14:09:15 GMT

So now i checked out both possibilities and i noticed that it is exactly the same result for both methods:

Method1:
|mvexpand field2
|eval status =if(match(field1,field2),"True","False")
|where status ="False"

Method2:
|mvexpand field2
|where field2!=field1

But it works now. Thx!

Re: How to create a search that compares two lists of field values?

MuS — Tue, 15 Apr 2014 14:11:49 GMT

nice, feel free to accept the answer by ticking the tick. I will update the answer with the latest search command

Re: How to create a search that compares two lists of field values?

C_Sparn — Tue, 15 Apr 2014 14:26:09 GMT

Maybe before you can help me with another problem refering to that search. Is it possible to get all field values(in a table) of the events that have a value of the list i get with the search above?

Re: How to create a search that compares two lists of field values?

MuS — Tue, 15 Apr 2014 19:18:29 GMT

I have to split my comment because it will be too long......

okay, listen this is my personal opinion:

I will not answer this last question and I think it is pretty rude and not fair play after all, if you make it dependent to accept this answer only if I answer the next one.

This is a community support page, although there are Splunk employees around and answering questions as well, most of us are Splunk users or Splunk Partners themselves and spent their spare time solving other people problems.

Re: How to create a search that compares two lists of field values?

MuS — Tue, 15 Apr 2014 19:18:42 GMT

You can show your support to this community by accepting a correct and useful answer and open a new question for your next problem.
Also you could show some support to the people trying to help you, by up voting their answers or send some beers 🙂

Re: How to create a search that compares two lists of field values?

C_Sparn — Wed, 16 Apr 2014 07:53:02 GMT

First I'm new to the splunk forum and didn't know how to vote for your very helpful answer.. so sorry! And besides I didn't want to vote just if you answer my next question, but I just thought that the new question is connected to the solved one, so I thought it's not necessary to open a new thread!!