How can I compare 2 fields from database dumps and return lists from 1 database off all data that is not found in the other?

[peetchow]

I have dbdump from my vulnerability software RetinaCS and dbdump from McAfee. I want to compare the assetNames field and return back to me the retina assetNames that are not found in McAfee. this would help with listing machines that do not have AntiVirus installed:

Professional Services created a dashboard with the Search:

index=database_dump sourcetype="retinacs-database-view-asset" AssetName="*" | fields AssetName, index, OSName, DomainName, IPAddress, LastUpdateDate | dedup AssetName | eval AssetHostName=upper(AssetName) | append [search sourcetype="symantec-db-clients" | dedup COMPUTER_NAME | eval UPPERCaseSymantecAssetName=upper(COMPUTER_NAME) | eval AssetHostName=UPPERCaseSymantecAssetName | fields AssetHostName, index ] | regex AssetHostName!="[0-9].*"  | search NOT AssetHostName=*CLUSTER* NOT OSName=*Linux* NOT OSName=*Blue* NOT OSName=*Cent* NOT OSName=*ESX* NOT OSName=*iLO* NOT OSName=*Integrated* NOT OSName=FreeBSD* NOT OSName=*LaserJet* NOT OSName=*MAC* DomainName=DMC |stats count, values(IPAddress) as IPAddress, values(DomainName) as Domain, values(OSName) as OperatingSystem, values(LastUpdateDate) as LastUpdateDate, values(index) as Source by AssetName | search count=1 Source=database_dump | fields - count Source

This does not work as I am getting a list that also has Assets that are in both dbs !

[woodcock]

Try this:

index=database_dump sourcetype="retinacs-database-view-asset" AssetName="*"
| fields AssetName, index, OSName, DomainName, IPAddress, LastUpdateDate
| eval AssetHostName=upper(AssetName) | dedup AssetHostName
| search NOT [search sourcetype="symantec-db-clients"
| eval AssetHostName=upper(COMPUTER_NAME) | dedup AssetHostName
| fields AssetHostName, index ]
| regex AssetHostName!="[0-9].*"
| search NOT AssetHostName=*CLUSTER*
         NOT OSName=*Linux*
         NOT OSName=*Blue*
         NOT OSName=*Cent*
         NOT OSName=*ESX*
         NOT OSName=*iLO*
         NOT OSName=*Integrated*
         NOT OSName=FreeBSD*
         NOT OSName=*LaserJet*
         NOT OSName=*MAC*
        DomainName=DMC
|stats count, values(IPAddress) as IPAddress, values(DomainName) as Domain, values(OSName) as OperatingSystem, values(LastUpdateDate) as LastUpdateDate, values(index) as Source by AssetName 
| search count=1 Source=database_dump
| fields - count Source