Splunk Search
Highlighted

How do you filter by Host and Account_Name with inputlookup and display only differences?

Path Finder

I have currently a lookup table that consists of AccountName and Host. This was created from Windows Event 4624 (An Account was successfully logged on) from a search parameter of the last 30 days. I am wanting to use the lookup table to filter the AccountName and Hosts, and display in the new query the differences that the new search brings. For example,

Lookup Table:
Account_Name,Host
Alpha, comp1
Bravo, comp1,comp3
Charlie, comp5,comp6
Delta, comp4,comp8

New Logons Data:
Alpha, comp1,comp2
Bravo, comp2,comp3
Charlie, comp4,comp5,comp6
Delta, comp4,comp8

So the new results should provide me with:

Alpha, comp2
Bravo, comp2
Charlie, comp4

So far my query is as follow:

index=main EventCode=4624 NOT  [ | inputlookup lookuptable.csv ] | Table Account_Name Host

This is how I set up lookup tables with one field for filtering, but trying to filter from two fields has got me stuck.

Thanks in advance for any help.

0 Karma
Highlighted

Re: How do you filter by Host and Account_Name with inputlookup and display only differences?

SplunkTrust
SplunkTrust

Assuming an event of EventCode 4624 has single Account_Name and Host mapping and your lookup has multiple Host entries separated by comma, try like this

index=main EventCode=4624 NOT [ | inputlookup lookuptable.csv | table Account_Name Host |  makemv Host delim="," | mvexpand Host] | Table Account_Name Host
0 Karma
Highlighted

Re: How do you filter by Host and Account_Name with inputlookup and display only differences?

Path Finder

The lookup table is delimited by a space.

However, I was mistaken about the table function. I used...

index=main EventCode=4624 | stats count values(ComputerName) AS Host by Account_Name | outputlookup lookuptable.csv

The results from the above query gave me my original lookup table. Now I want to produce another query to filter with the differences from Account_Name and Host.

The first solution did not filter anything. I ran the search without "NOT | inputlookup lookuptable.csv" and the results are the exact same with the "NOT | inputlookup lookuptable.csv" added to the query.

0 Karma