Help with Lansweeper Report

[jvalenzano]

Hello I am trying to create a report that tells me which people have the "Autodesk Revit % Standalone" software I need. I get it to work but I don't want to see duplicate computer names. I have tried a few things but I am still seeing duplicates. Any help would be appreciated. There are 4 different software names that will come up so that might be why its shows multiple times. I just want to see if the person has it and only want there computer to show up once in the report.

Thank you below is what I have so far

Select Distinct Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblComputers.Description,
upgrade_tblSoftware.softwareName
From upgrade_tblComputers
Inner Join upgrade_tblSoftware On upgrade_tblComputers.Computername =
upgrade_tblSoftware.ComputerName
Inner Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
upgrade_tblComputers.Computername
Where upgrade_tblSoftware.softwareName Like 'Autodesk Revit % Standalone' And
upgrade_tblSoftware.softwareName Like 'Autodesk Revit % Standalone'
Group By upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblComputers.Description,
upgrade_tblSoftware.softwareName
Having Count(1) = 1

[RCorbeil · Accepted solution]

Something like this?

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName Like 'Autodesk Revit %'
  AND tblSoftwareUni.softwareName Like '% Standalone'

(You can do a name match for 'Autodesk Revit % Standalone', but when I was testing against my own database with a particular name, I found the results returned a lot faster by splitting it into two distinct matches, as above.)