cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Diego_Tomazzoni
Engaged Sweeper III
Hi!

In our environment, computers that are in groups that start with "SP" as "SP-W2K12-IIS" or "SP-W2K8-SQL" are computers that comply with security policies. So I need to see all the computers that are not in any group that starts with "SP", so that I can let them according to security policies.

I checked this code below that Susan was created, it shows all the computers that are in a particular group. I could not change the group to make it possible to show all the computers that are not in a group that start with "SP". Is it possible do it?


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblADComputers.AssetID
From tblADComputers Left Join tblADMembership
On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID Left Join
tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
Where tblADGroups.Name = 'your group name') And tblAssetCustom.State = 1 And
tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName


tks a lot!
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
We received and answered this question via email. For everyone else's benefit, I'm pasting the reply we sent via email below.
Replacing this part of the query...
= 'your group name'

... with the following should do the trick:
Like 'SP%'

If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
We received and answered this question via email. For everyone else's benefit, I'm pasting the reply we sent via email below.
Replacing this part of the query...
= 'your group name'

... with the following should do the trick:
Like 'SP%'

If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.