cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
snigah
Engaged Sweeper II
Hi,

Can someone kindly help me figuring this out. i have no SQL query experience, hence this problem.

The builtin report: Software: List of software by computer

is for my purpose missing Fullname (instead of username) and the Company attribute from AD.

When i drag and drop and connect the missing pieces the report blows up in size and shows multiple entry per all users logged on to the device, asset id, and on top per company also.

I need some help how to put it together so that is shows only:

One entry per software per asset, last loggedon user and company attribute for that user


Here is my query:

Select Top 1000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblUsers.Fullname,
tblADusers.Company
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblUsers On tblAssets.AssetID = tblUsers.AssetID
Inner Join lansweeperdb.dbo.tblADusers On tblAssets.Username =
tblADusers.Username
Where tblUsers.Fullname Is Not Null And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
Hi,

These are the fields from AD Lansweeper scans https://www.lansweeper.com/knowledgebase/active-directory-user-and-computer-attributes-scanned-by-lansweeper/ , The Full Name you are using "I believe" is the User from Lansweeper, he is the report with AD Name and Company:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Name,
tblADusers.Company,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,

tblAssetCustom.State
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Right Join lansweeperdb.dbo.tblADusers On tblAssets.Username =
tblADusers.Username
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version

View solution in original post

2 REPLIES 2
Andy_Sismey
Champion Sweeper III
Hi,

These are the fields from AD Lansweeper scans https://www.lansweeper.com/knowledgebase/active-directory-user-and-computer-attributes-scanned-by-lansweeper/ , The Full Name you are using "I believe" is the User from Lansweeper, he is the report with AD Name and Company:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Name,
tblADusers.Company,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,

tblAssetCustom.State
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Right Join lansweeperdb.dbo.tblADusers On tblAssets.Username =
tblADusers.Username
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version
snigah
Engaged Sweeper II
Thanks A lot, it worked perfectly!