topic Re: Comparing a field value with an entire column or CSV in Getting Data In

Comparing a field value with an entire column or CSV

jarrex — Fri, 10 Jul 2015 18:47:12 GMT

I have been searching for how to do this for the longest time and it's rather frustrating that I can't seem to find a simple explanation anywhere.

I have a search setup and a CSV file that lists a bunch of "blacklist" items that I do not want included in the results I have tried just about everything with regards to the CSV and I have seen no progress. It got to the point that I have now been trying to append the CSV and then just search the value within the appended column but there doesn't seem to be a way to search a CONTAINS within a different column.

Here is what I want to do:

I have a table that looks like this

Name    Teams   Removed Team    TeamDels
Bob Team A  Yes           TeamA
Bob Team B  No            TeamE
Jane    Team C  No            TeamF
Jane    Team D  No            TeamG
Jane    Team E  Yes           TeamH

What I am currently thinking I am going to have to do is append the CSV (TeamDels column) and then somehow do a search if Teams contains a value in TeamDels but I can't do this?

Removed Team would be calculated by looking at the Team value (Team A, Team B etc.) and seeing if it is in the blacklist. If it is in the black list is says Yes.

I do not care how to go about doing this, I can even remove the Removed Team column if there was a way to filter out the teams based on the contents of the CSV file but there doesn't seem to be a way.

It should be noted that the "Teams" are multivalue to start so I have to split them.

DeletedTeamList.csv contains 1 column with the header TeamDels

Here is my current search:

index=teams action=* 
| append [inputlookup DeletedTeamList.csv] 
| table Name,Teams,Removed_Team
| mvexpand Teams 
| eval Removed_Team=If(Teams!=TeamDels,"No","Yes")

If I typo'd anything on here please ignore as these aren't the real fields or anything.

Re: Comparing a field value with an entire column or CSV

woodcock — Fri, 10 Jul 2015 20:46:38 GMT

I am not sure that I understand but perhaps this?

index=teams action=* | eval type=1 | append [| inputlookup DeletedTeamList.csv | rename TeamDels AS "Teams" | table Teams | eval type=2] | mvexpand Teams | stats values(*) AS * dc(type) AS numTypes BY Teams | where numTypes=1 AND type=1

This will show you all (fields for) events in the first search whose value for Teams is not found in the lookup file in any entry's TeamDels. Is that what you desire? If not, perhaps you can tweak the where part a bit differently.

Re: Comparing a field value with an entire column or CSV

jarrex — Mon, 13 Jul 2015 13:13:24 GMT

You understood perfectly! Had to tweak it 1 last time to filter out any blanks

| where numTypes=1 AND type=1 AND teams!=""

But now it works perfectly, thank you a lot!

Re: Comparing a field value with an entire column or CSV

jarrex — Mon, 13 Jul 2015 13:15:58 GMT

Could you explain 1 thing to me? When appending the CSV and renaming the column, does that cause the two identically named columns to merge?

Re: Comparing a field value with an entire column or CSV

woodcock — Fri, 17 Jul 2015 05:40:04 GMT

Not exactly. When I append the second dataset, it does exactly that. But before I let Splunk append it, I need to normalize the field TeamDels to match the field in the first dataset. That way I can do a breakout across the combined dataset by a field that exists in both sets.