cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
chrisjones
Engaged Sweeper II
Hi im having an issue with duplicate entries on tblSoftware. Essentially i want to show all the software installed on the various computers and who was the last user to log on. I have tried something similar to the link below but with no success. Any ideas appraciated

Thanks in advance

http://www.lansweeper.com/Forum/yaf_postst10786_Adding-Active-Directory-user-information-to-a-report.aspx#post40397


Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblOperatingsystem.Caption As [OS name],
tblAssets.Domain,
tblAssets.Lastseen,
tblAssetCustom.Department,
tblADusers.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Full Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
Your report includes one of the software tables, but doesn't list any of the software fields, which is why the output looks strange. You're getting a line for every software package, but the actual software name isn't listed. This should provide better results:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblOperatingsystem.Caption As [OS name],
tblAssets.Domain,
tblAssets.Lastseen,
tblAssetCustom.Department,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Name,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Left Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
Software

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
Your report includes one of the software tables, but doesn't list any of the software fields, which is why the output looks strange. You're getting a line for every software package, but the actual software name isn't listed. This should provide better results:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblOperatingsystem.Caption As [OS name],
tblAssets.Domain,
tblAssets.Lastseen,
tblAssetCustom.Department,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Name,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Left Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
Software

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now