How do I filter my report by GROUP

[paulruvalcaba]

Below is the report I am trying to fix. I have been able to filter a different report by group by adding the same line, but this one wont let me.

I am trying to report ONLY on workstations and not on servers, yet the servers still show up.

Is there another join command I need? I am still very new to SQL and mainly duplicate other existing reports to edit for our needs. Recently we've added servers to the scan and I want to make sure they're not showing up on the reports.

Select Top 1000000 tblAssets.AssetName,
  tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblAssets.Lasttried,
  tblAssets.Lastseen,
  Case
    When Exists(Select tblSoftware.AssetID
          From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
              tblSoftware.softID
          Where tblSoftware.AssetID = tblAssets.AssetID And
            tblSoftwareUni.softwareName Like
            '%DameWare Mini Remote Proxy Service%') Then 'YES'
    Else 'NO'
  End As DameWare,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode,
  tblAssetGroups
Where tblAssetGroups.AssetGroup = '!All Workstations'

[RCorbeil]

Hmm... you're right. As I'm reading things, the dynamic groups should show up, but when I do a simple join of tblAssets -> tblAssetGroupLink -> tblAssetGroups on my database all I get are the static groups. That's too bad. I'd forgotten the dynamic groups were an option and, if they worked as it looks like they should, they could have simplified something else I'm trying to set up.

Thanks for mentioning that. If I have time later I might poke around and see if I'm missing something.

[RCorbeil]

Try this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When Exists(Select tblSoftware.AssetID
          From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
              tblSoftware.softID
          Where tblSoftware.AssetID = tblAssets.AssetID And
            tblSoftwareUni.softwareName Like
            '%DameWare Mini Remote Proxy Service%') Then 'YES'
    Else 'NO'
  End As DameWare,
  tsysOS.OSname,
  tblAssetGroups.AssetGroup
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssetCustom.State = 1
  And tblAssets.Assettype = -1

The LEFT JOIN tsysOS causes all the assets to be listed, so the WHERE tblAssetsAssetType = -1 limits the list to just Windows assets.

That query should list all your Windows assets and all the groups to which they belong. If the list looks right, add your original AND tblAssetGroups.AssetGroup = '!All Workstations' to the WHERE clause and see if it produces what you expected.

[paulruvalcaba]

Interesting. That showed mostly "default group" and other static groups. The group I was trying to filter for was a dynamic group.

I just searched and I guess dynamic groups cannot be called. Even though I was able to list the groups the other way I had it and the groups were listed correctly.

Either way, your first response helped a lot. Thank you.

RC62N wrote:
Try this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When Exists(Select tblSoftware.AssetID
          From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
              tblSoftware.softID
          Where tblSoftware.AssetID = tblAssets.AssetID And
            tblSoftwareUni.softwareName Like
            '%DameWare Mini Remote Proxy Service%') Then 'YES'
    Else 'NO'
  End As DameWare,
  tsysOS.OSname,
  tblAssetGroups.AssetGroup
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssetCustom.State = 1
  And tblAssets.Assettype = -1

The LEFT JOIN tsysOS causes all the assets to be listed, so the WHERE tblAssetsAssetType = -1 limits the list to just Windows assets.

That query should list all your Windows assets and all the groups to which they belong. If the list looks right, add your original AND tblAssetGroups.AssetGroup = '!All Workstations' to the WHERE clause and see if it produces what you expected.

[RCorbeil]

First, you haven't related the tblAssetGroups to tblAssets in your list of selected tables.

Second, do you definitely have an asset group called "!All Workstations"?

If you do have an asset group "!All Workstations" defined, start by defining how you want tblAssetGroups linked to tblAssets. If you check the database documentation, you'll see that goes

• tblAssets -> tblAssetGroupLink -> tblAssetGroups

If you don't have that group defined, LANSweeper notes the domain role of computers. You might consider trying:

• remove tblAssetGroups from the list of tables
  
• add tblComputerSystem instead (joined on AssetID)
  
• create a filter WHERE tblComputerSystem.DomainRole < 2

(If you check the contents of tblComputerSystem, you'll see that 0 is standalone workstation, 1 is member workstation and 2-6 are various categories of server.)

If you want to see domain role descriptions, add a join to tblDomainRoles and add tblDomainRoles.DomainRoleName to your report.

[paulruvalcaba]

Thank you.

The Domain Role worked. I am just curious how I would join asset groups to assets. When I opened tblComputerSystem it auto related the 2. When I opened tblAssetGroupLink it autolinked with Assets and AssetGroups but it didnt work when I ran the report.

RC62N wrote:
First, you haven't related the tblAssetGroups to tblAssets in your list of selected tables.

Second, do you definitely have an asset group called "!All Workstations"?

If you do have an asset group "!All Workstations" defined, start by defining how you want tblAssetGroups linked to tblAssets. If you check the database documentation, you'll see that goes

• tblAssets -> tblAssetGroupLink -> tblAssetGroups

If you don't have that group defined, LANSweeper notes the domain role of computers. You might consider trying:

• remove tblAssetGroups from the list of tables
  
• add tblComputerSystem instead (joined on AssetID)
  
• create a filter WHERE tblComputerSystem.DomainRole < 2

(If you check the contents of tblComputerSystem, you'll see that 0 is standalone workstation, 1 is member workstation and 2-6 are various categories of server.)

If you want to see domain role descriptions, add a join to tblDomainRoles and add tblDomainRoles.DomainRoleName to your report.