topic How do you filter by Host and Account_Name with inputlookup and display only differences? in Splunk Search

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

chanthongphiob — Tue, 29 Sep 2020 18:12:45 GMT

I have currently a lookup table that consists of Account_Name 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 Account_Name 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.

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

somesoni2 — Tue, 27 Feb 2018 17:04:18 GMT

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

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

chanthongphiob — Tue, 27 Feb 2018 21:21:03 GMT

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.