Report for multiple criteria

[JaniK]

Hi there,

I'm trying to create a report that would display all computers that do NOT belong to a specific AD group but have a certain program installed.

I can create a report that shows computers that do not belong to the AD group.

I can create a report that shows computers that have the program installed.

It's combining these two criteria into a single report that I haven't been able to figure out. Any help would be appreciated.

[JaniK]

Thank you very much, that works perfectly.

[MikeMc · Accepted solution]

Lansweeper keeps track of workstations, servers, DC's and etc. through the domainrole column in the tblComputersystem table. This is what I have in my notes:

• <2 - Workstations
• 3 - Servers
• 4 and 5 - DC's

Anyway, I tweaked the query for you.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblAssets.Lastseen,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like 'Program%') And tblAssetCustom.State =
  1 And tblComputersystem.Domainrole < 2 And tblADComputers.ADObjectID
  Not In (Select tblADMembership.ChildAdObjectID
  From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
      tblADMembership.ParentAdObjectID Where tblADGroups.Name = 'ADGroup')
Order By tblAssets.AssetName

[JaniK]

Thanks, that works great!

...however, I did realize I need one more qualifier, the report should only include workstations (so no servers). I thought this would be a simple addition, but after looking at the pre-made reports that only return workstations (like Workstation: All workstations) I have no idea how the search is limited to those.

I tried taking the relative parts from the report Workstation: All workstations and got this:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblComputersystem.Lastchanged,
  tsysOS.Image As icon
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like 'Program%') And tblAssetCustom.State = 1
  And tblADComputers.ADObjectID Not In (Select tblADMembership.ChildAdObjectID
  From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
      tblADMembership.ParentAdObjectID
  Where tblADGroups.Name = 'ADGroup' And tblComputersystem.Domainrole < 2 And
    tblAssetCustom.State = 1)
Order By tblAssets.AssetName

but it still has servers included. What am I missing?

[MikeMc]

This should get you started. Just change the criteria on lines 11 and 15 to suit your needs.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like 'Adobe%') And tblAssetCustom.State = 1
  And tblADComputers.ADObjectID Not In (Select tblADMembership.ChildAdObjectID
  From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
      tblADMembership.ParentAdObjectID
  Where tblADGroups.Name = 'Domain Computers')
Order By tblAssets.AssetName