Not in a Dynamic/Static Asset Group(s)

InsightVM Query Builder
1

Looking for a way to view a list of assets that are not in an asset group. I’ve tried using the query builder:

asset.groups IS NULL
asset.groups NOT IN [‘groupA’,‘groupB’,…]

Nothing shows up in the results. End goal would be to have a DAG setup for a quick reference but even just a report would be sufficient at this point. Any suggestions would be great!

1 Like
2

I tested with asset.groups IS NULL and I’m seeing the same, but it’s interesting that the asset.groups NOT IN ... isn’t working. Can you tell if there’s a particular asset group that it’s “failing on”? Eg, when you add that particular group to the “NOT IN” query, then you end up with no results.

If you’re willing to go the report route, one way to do it would be to create a custom SQL report and provide a query for selecting assets that aren’t in any groups. Here we’ve got the schema for asset group-related tables, which can help you figure out which fields you can use in your query.

3

Hi Holly. Thanks for the reply. At least I’m not alone with it not working.

I’m noticing that when I do an asset.groups NOT IN... query regardless of which particular group I select. It seems to only query devices that are a part of a group. Eg, if I have 500 devices with only 300 in a group(s); it only queries those 300.

I’ll have to play around with the custom SQL report. I’ve only dabbled in SQL but that seems to be an option.

1 Like
4

I was messing with this SQL query a bit and I’ve got a starting point for selecting those assets:

SELECT asset_id, ip_address, host_name, dag.name AS "asset group name"
FROM dim_asset da
JOIN dim_asset_group_asset daga USING (asset_id)
JOIN dim_asset_group dag USING (asset_group_id)

That should select your assets that are in groups, and provide their group name. Then there should be a way to alter that to essentially select assets that are in no groups.

Edit: I just used this query for a separate asset group-related ask. This may get you the assets you’re looking for.

SELECT asset_id, ip_address, host_name
FROM dim_asset
WHERE asset_id NOT IN (SELECT asset_id FROM dim_asset_group_asset)
1 Like
5

Sorry for the delay been a hectic month. Thanks for that info. It’s a little dirty getting it to work correctly but it’s workable.

1 Like
6

Thank you Holly. I was looking for the same result since long and this does the job for me however I would want to have the “site” also listed in the final result to get the information of the site that the asset (with no defined asset group) belongs to.

I tried below examples but none worked for me:

SELECT asset_id, ip_address, host_name, site
FROM dim_asset
WHERE asset_id NOT IN (SELECT asset_id FROM dim_asset_group_asset)

SELECT asset_id, ip_address, host_name, site_id
FROM dim_asset
WHERE asset_id NOT IN (SELECT asset_id FROM dim_asset_group_asset)

7

This worked for me :slight_smile:

SELECT sites, asset_id, ip_address, host_name, site_id
FROM dim_asset
WHERE asset_id NOT IN (SELECT asset_id FROM dim_asset_group_asset)