Notification

Icon
Error

How do I filter my report by GROUP - I am trying to add a filter by group, but it still lists assets not in that group

Posted: Tuesday, May 7, 2019 5:57:59 PM(UTC)
Apaulcolypse

Apaulcolypse

Member Original PosterPosts: 25
0
Like
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.


Code:
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'
RC62N
#1RC62N Member Posts: 374  
posted: 5/9/2019 7:52:37 PM(UTC)
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.
Apaulcolypse
#2Apaulcolypse Member Original PosterPosts: 25  
posted: 5/9/2019 9:05:11 PM(UTC)
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.


Originally Posted by: RC62N Go to Quoted Post
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.


RC62N
#3RC62N Member Posts: 374  
posted: 5/9/2019 9:47:16 PM(UTC)
Try this:
Code:
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.
Apaulcolypse
#4Apaulcolypse Member Original PosterPosts: 25  
posted: 5/10/2019 12:51:14 AM(UTC)
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.


Originally Posted by: RC62N Go to Quoted Post
Try this:
Code:
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.
RC62N
#5RC62N Member Posts: 374  
posted: 5/10/2019 6:34:35 PM(UTC)
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.

Active Discussions

Action Change Windows domain PC Name
by  max204   Go to last post Go to first unread
Last post: 9/19/2019 10:28:29 AM(UTC)
Lansweeper Launch PowerShell remote PSSession
by  Ian  
Go to last post Go to first unread
Last post: 9/9/2019 12:10:56 PM(UTC)
Action Remote print management
by  CyberCitizen   Go to last post Go to first unread
Last post: 9/4/2019 3:53:00 AM(UTC)
Lansweeper Infopath installer help
by  CyberCitizen  
Go to last post Go to first unread
Last post: 9/2/2019 8:18:06 AM(UTC)
Lansweeper Remote Registry 2019
by  EB   Go to last post Go to first unread
Last post: 8/22/2019 3:47:10 PM(UTC)
Lansweeper lspush smtp direct send
by  Danilo Ferrari  
Go to last post Go to first unread
Last post: 8/1/2019 1:39:26 PM(UTC)
Action Delete old user profiles
by  DaveDischord   Go to last post Go to first unread
Last post: 7/30/2019 6:18:28 PM(UTC)
Lansweeper Best way to delete multiple registries
by  Corcos  
Go to last post Go to first unread
Last post: 7/25/2019 8:18:23 PM(UTC)