Getting Data In

Comparing a field value with an entire column or CSV

jarrex
Explorer

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.

0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

woodcock
Esteemed Legend

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.

jarrex
Explorer

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!

jarrex
Explorer

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?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma
Get Updates on the Splunk Community!

Data Preparation Made Easy: SPL2 for Edge Processor

By now, you may have heard the exciting news that Edge Processor, the easy-to-use Splunk data preparation tool ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Tips & Tricks When Using Ingest Actions

Tune in to learn about:Large scale architecture when using Ingest ActionsRegEx performance considerations ...