Duplicated assets in a report

[abustraan]

I have 2 reports one for Revit 2015, and one for Revit 2017. the Revit 2015 works great! No duplicates. However, the 2017 (which was just a copy of 2015 and updated for the 2017 references and version numbers) duplicates all of the Assets.

Any Thoughts? TIA

Select Top 1000000 tblAssets.AssetID,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Case tblSoftware.softwareVersion When '15.0.1170.0' Then 'UR08'
When '15.0.1190.0' Then 'UR09' When '15.0.1203.0' Then 'UR10'
When '15.0.1225.0' Then 'UR11' When '15.0.1238.0' Then 'UR12'
When '15.0.1243.0' Then 'UR13' When '15.0.1259.0' Then 'UR14' Else 'Unknown'
End As [Service Pack],
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysOS.Image As icon,
tsysOS.OSname,
tblSoftware.Installdate,
tblAssetCustom.Department
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where (tblSoftwareUni.softwareName Like 'Autodesk Revit Architecture 2015' And
tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit MEP 2015') Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit 2015')
Order By Software,
tblAssets.AssetName,
Version

Select Top 1000000 tblAssets.AssetID,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Case tblSoftware.softwareVersion When '17.0.416.0' Then 'Original'
When '17.0.476.0' Then 'SP1' When '17.0.501.0' Then 'SP2'
When '17.0.503.0' Then '17.0.503.0' When '17.0.1081.0' Then '2017.1'
When '17.0.1099.0' Then '2017.1.1' When '17.0.1117.0' Then '2017.2'
When '17.0.1128.0' Then '2017 2.1' Else 'Unknown'
End As [Service Pack],
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysOS.Image As icon,
tsysOS.OSname,
tblAssetCustom.Department
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where (tblSoftwareUni.softwareName Like 'Autodesk Revit Architecture 2017' And
tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit MEP 2017') Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit 2017')
Order By Software,
tblAssets.AssetName,
Version

[abustraan · Accepted solution]

I updated the record for SoftID 3278 and added the missing information SoftwarePublisher = 'Autodesk' and that fixed the duplicated entry.

Rescanned the assets and the Autodesk Revit 2017 with the missing information for SoftwarePublisher was not recreated and I don't have the duplicated report entries.

Thanks again.

[abustraan]

Thanks for the prompt reply. In viewing the tblSoftwareUni table I have (2) entries for Autodesk Revit 2017

3266 Autodesk Revit 2017 Autodesk 0 1 2016-05-20 22:09:35.990
3278 Autodesk Revit 2017 0 1 2016-05-20 22:09:36.537

I tried to run the following in SQL:

DELETE FROM [lansweeperdb].[dbo].[tblSoftwareUni]
where [SoftID] =3278

and received the following error:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_tblSoftware_tblUniqueSoftware". The conflict occurred in database "lansweeperdb", table "dbo.tblSoftware", column 'softID'.
The statement has been terminated.

[David_G]

It might be possible that your PCs have both Autodesk revit MEP 2017 and Autodesk Revit 2017 installed, which would then result in a row for these assets for both the installations. Additionally, we have made a slight change to the report to lower the risk of duplicates where we have put all the Criteria together.

Select Top 1000000 tblAssets.AssetID,
  tblADusers.Lastname,
  tblADusers.Firstname,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  Case tblSoftware.softwareVersion When '17.0.416.0' Then 'Original'
    When '17.0.476.0' Then 'SP1' When '17.0.501.0' Then 'SP2'
    When '17.0.503.0' Then '17.0.503.0' When '17.0.1081.0' Then '2017.1'
    When '17.0.1099.0' Then '2017.1.1' When '17.0.1117.0' Then '2017.2'
    When '17.0.1128.0' Then '2017 2.1' Else 'Unknown' End As [Service Pack],
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysOS.Image As icon,
  tsysOS.OSname,
  tblAssetCustom.Department
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where (tblSoftwareUni.softwareName Like 'Autodesk Revit Architecture 2017' Or
    tblSoftwareUni.softwareName Like 'Autodesk Revit MEP 2017' Or
    tblSoftwareUni.softwareName Like 'Autodesk Revit 2017') And
  tblAssetCustom.State = 1
Order By Software,
  tblAssets.AssetName,
  Version