Identify Assets that are only in a Discovery Site

I’m trying to Identify Assets that are only in a Discovery Site and not Global or a Vulnerability Site.
I started with asset.sites NOT IN for my query, but the returned data is not including the rest of the Asset information needed to figure out why they are missed. I would like to see all the standard Asset data and an array of Sites that an Asset is in. I pulled an Asset’s information from the API, but there wasn’t a member for Sites listed, even under Links where I thought there would be.
Any suggestions here?

I just ran a simple test query in my own environment and I got all blank values for the sites column. I’m looking into it to see why that might be because while I don’t know if the value should be an integer or an array I know it should be something other than blank.

Also though, for trying to find membership to only one site the query is kinda backwards. You would need to go into expert mode and do NOT (assets.sites IN Global, SiteA, SiteB) etc

I would expect to see an array, Global should just indicate that length(assets.sites) > 1

Table/field may be different in console vs data warehouse, but you can use “last assessed for vulnerabilities” and return assets where the value is null.

select
da.host_name
from dim_asset da
where da.last_assessed_for_vulnerabilities is null