Splunk Enterprise Security

Using Inputlookup to Eliminate Search Results

Explorer

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.signatureid,Authentication.src,Authentication.dest
| where NOT
[| inputlookup domain
controllers | fields fqdn | rename fqdn as Authenticatin.src] OR
[| inputlookup domaincontrollers | fields host | rename host as Authenticatin.src] OR
[| inputlookup domain
controllers | fields ip | rename ip as Authenticatin.src]

0 Karma
1 Solution

Communicator

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

View solution in original post

Explorer

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 domaincontrollers
| eval Authentication.src=mvappend(fqdn, host, ip)
| fields Authentication.src ]
| table Authentication.usr,Authentication.signature
id,Authentication.src,Authentication.dest

Splunk Employee
Splunk Employee

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]

0 Karma

Communicator

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

View solution in original post

Influencer

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]
0 Karma

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

0 Karma

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.

0 Karma

Explorer

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 domaincontrollers | 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 ] )

0 Karma