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