Software Report Question

[mfry1379]

OK, so I have an issue trying to build a report in LANSweeper and sadly my SQL-fu is weak so I can't just hack the code.

I want to create a report that lists out all computers in an AD Group, that do not have the correct version of a piece of software installed.

The AD Group part is fine, (see below) but the software part is harder.

When I add in the software and say "Does not equal" <> it just throws me back another 80000 lines of software on all the PCs that are not that version. basically, because each PC may be listed numerous times in the software table (once for every piece of software), when I ask for all rows where the software is not there or the right version, the PCs that I do not want in the list appear because of another piece of software. Soooooo....

What I want, is to get the full list of computers in the group, and then remove any that already have the software title installed. From there I will use the report to create a list of PCs for a software deployment.

Any ideas?

AD Group Report:

Select Top 1000000 tblAssets.AssetName,
tblADGroups.Name As [Group],
tblADComputers.Location,
tblADComputers.OU,
tblADComputers.Lastchanged
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblADComputers On tblADComputers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblAssets On tblAssets.AssetID = tblADComputers.AssetID
Where tblADGroups.Name = 'ADGroup'
Order By [Group]

With Failed "not Equal":

Select Top 1000000 tblAssets.AssetName,
tblADGroups.Name As [Group],
tblADComputers.Location,
tblADComputers.OU,
tblADComputers.Lastchanged,
tblSoftwareUni.softwareName
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblADComputers On tblADComputers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblAssets On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblADGroups.Name = 'mfrytest' And tblSoftwareUni.softwareName <>
'Adobe Flash Player 16 ActiveX'
Order By [Group]

Shows the computers in the group that I want to exclude:

Select Top 1000000 tblAssets.AssetName,
tblADGroups.Name As [Group],
tblADComputers.Location,
tblADComputers.OU,
tblADComputers.Lastchanged,
tblSoftwareUni.softwareName
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblADComputers On tblADComputers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblAssets On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblADGroups.Name = 'mfrytest' And tblSoftwareUni.softwareName =
'Adobe Flash Player 16 ActiveX'
Order By [Group]

[Susan_A · Accepted solution]

Your report is listing all software packages whose name isn't "Adobe Flash Player 16 ActiveX"; it isn't listing machines *without* the software package. We recommend having a look at this report example.