Using Inputlookup to Eliminate Search Results

[rotundwizard]

Been banging my head on this and need some assistance. Trying to use a csv to eliminate some search results with no success. The csv file has three fields which I map back to a single field via the rename command. When the field in the csv matches, I don't want to see that event if that makes since. Ultimately trying to eliminate entries sourced from a domain controller.

Unfortunately the unwanted results still show up in my table.

| datamodel Authentication Authentication search
| table Authentication.usr,Authentication.signature_id,Authentication.src,Authentication.dest
| where NOT
[| inputlookup domain_controllers | fields fqdn | rename fqdn as Authenticatin.src] OR
[| inputlookup domain_controllers | fields host | rename host as Authenticatin.src] OR
[| inputlookup domain_controllers | fields ip | rename ip as Authenticatin.src]

[rotundwizard]

Thank you everyone for your help! The solution ended up being a combination of the provided answers, but ultimately replacing "where" with "search" pushed it over the edge. The final missing piece was to do the search right at the beginning of the query.

Here's the final correct answer with info combined from all the responses:

| datamodel Authentication Authentication search
| search NOT
[| inputlookup domain_controllers
| eval Authentication.src=mvappend(fqdn, host, ip)
| fields Authentication.src ]
| table Authentication.usr,Authentication.signature_id,Authentication.src,Authentication.dest

[smoir_splunk]

There are typos in your aliases, and I second @Splunker's suggestion of using "search" instead of "where". You also have 3 subsearches in a row; is there a way to combine those into one subsearch?

something like:

| search NOT
[| inputlookup domain_controllers | table fqdn,host,ip | rename fqdn as Authentication.src | rename host as Authentication.src | rename ip as Authentication.src]

[Splunker · Accepted solution]

Try 'search' instead of 'where' since where is an eval operator. (haven't tested it, just a suggestion) 🙂

[tiagofbmm]

Hey

Have a look at my example (generic, so test it in your env)

index=_internal 
| where NOT ( 
    [| makeresults 
    | eval sourcetype="splunkd" 
    | append 
        [| makeresults 
        | eval sourcetype="eventgen" ] 
    | return 100 sourcetype ])

Using the return you can get the this you want. In your case should be like

| datamodel Authentication Authentication search
| table Authentication.usr,Authentication.signature_id,Authentication.src,Authentication.dest
| where NOT
[| inputlookup domain_controllers | fields fqdn | rename fqdn as Authenticatin.src | return 10000 Authenticatin.src ] OR
[| inputlookup domain_controllers | fields host | rename host as Authenticatin.src | return 10000 Authenticatin.src] OR
[| inputlookup domain_controllers | fields ip | rename ip as Authenticatin.src | return 10000 Authenticatin.src]

[elliotproebstel]

I think a few syntax tweaks should make this run properly:

| datamodel Authentication Authentication search
| table Authentication.usr,Authentication.signature_id,Authentication.src,Authentication.dest
| where NOT (
[| inputlookup domain_controllers | fields fqdn | rename fqdn as Authentication.src ] OR
[| inputlookup domain_controllers | fields host | rename host as Authentication.src ] OR
[| inputlookup domain_controllers | fields ip | rename ip as Authentication.src  ] )

I added parenthesis around the search fields returned by the subsearches, because the NOT wouldn't carry over otherwise. Basically, let's say the first subsearch returned one event: Authentication.src="first", and the second returned Authentication.src="second", and the third returned Authentication.src="third". Everything after | where ... would then translate to this:

| where NOT Authentication.src="first" OR Authentication.src="second" OR Authentication.src="third"

The key here is that the NOT will not be applied to all statements. It's closer to this:

| where Authentication.src!="first" OR Authentication.src="second" OR Authentication.src="third"

But adding parenthesis will cause the NOT to be distributed. You could achieve the same thing by replacing all instances of OR with NOT.

I also fixed some typos in your subsearches, but I assume those were just transcription errors. 🙂

[elliotproebstel]

After thinking about this a little more, I'm pretty sure we could even simplify the query down like this:

| datamodel Authentication Authentication search
| table Authentication.usr,Authentication.signature_id,Authentication.src,Authentication.dest
| where NOT 
[| inputlookup domain_controllers 
 | eval Authentication.src=mvappend(fqdn, host, ip) 
 | fields Authentication.src ] 

This appends all three fields into Authentication.src at once and returns them in a single instance of inputlookup, which is much more efficient than doing three in a row.

[rotundwizard]

Thank you tiagofbmm and elliotproebstel for your answers! Unfortunately after trying both answers I'm still getting results that match entries in the CSV.

Is the disconnect possibly around the fact this is a datamodel search? If I convert it to a standard search (see below) the results I expect to see excluded are excluded as I'd expected to see in the datamodel search.

index=winenentlog sourcetype="WinEventLog:Security"
| where NOT (
[| inputlookup domain_controllers | fields fqdn | rename fqdn as src ] OR
[| inputlookup domain_controllers | fields host | rename host as src ] OR
[| inputlookup domain_controllers | fields ip | rename ip as src ] )