→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jvalenzano
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.)

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
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.)