List events in csv not found in index

[munisb]

Hi,

I have finally got my search to work that compares data between index and lookup (csv) file that contains assets name and provide output of assets found in the index as well as CSV based off some EVALs

index=myindex ASSETS [ | inputlookup linuxhostnames.csv | eval hostname="*".hostname."*" | rename hostname as DNS ] 
| dedup DNS
| eval Agent=if(like(TAG, "%NonProd%"), "Yes - NonProd", "No Agent")
| eval Location=if(like(TAG, "%DataCenter%"), "Data Center", "Not in DC")
| where Agent="No Agent"
| table DNS, IP, OS, Location, TAG

even if i remove the eval statements - the asset output is less than the total count in the .csv

So it's listing ONLY the assets that are found in BOTH csv and index.

How can I generate a table that will show assets that are not in the index but are in the CSV?

Thank you

[richgalloway]

You have the logic for getting assets in both place.  Add NOT to get those assets in the index, but not in the lookup.

index=myindex ASSETS NOT [ | inputlookup linuxhostnames.csv | eval hostname="*".hostname."*" | rename hostname as DNS ] 
| dedup DNS
| eval Agent=if(like(TAG, "%NonProd%"), "Yes - NonProd", "No Agent")
| eval Location=if(like(TAG, "%DataCenter%"), "Data Center", "Not in DC")
| where Agent="No Agent"
| table DNS, IP, OS, Location, TAG

Reverse the main and sub searches to get assets in lookup, but not in the index.

| inputlookup linuxhostnames.csv where NOT [index=myindex ASSETS] 
  | rename DNS as hostname ] 
| dedup hostname 
| eval Agent=if(like(TAG, "%NonProd%"), "Yes - NonProd", "No Agent")
| eval Location=if(like(TAG, "%DataCenter%"), "Data Center", "Not in DC")
| where Agent="No Agent"
| table hostname, IP, OS, Location, TAG

Oh, but we have a problem.  The lookup file (probably) doesn't have all the needed fields and only the hostname (DNS) field is available from the index.  We can fix that with an inner join.

index=myindex ASSETS 
| join type=inner DNS [ | inputlookup linuxhostnames.csv | eval hostname="*".hostname."*" | rename hostname as DNS ] 
| dedup DNS
| eval Agent=if(like(TAG, "%NonProd%"), "Yes - NonProd", "No Agent")
| eval Location=if(like(TAG, "%DataCenter%"), "Data Center", "Not in DC")
| where Agent="No Agent"
| table DNS, IP, OS, Location, TAG