small change to "Software: List of software by computer"

[snigah]

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

[Andy_Sismey · Accepted solution]

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]

Thanks A lot, it worked perfectly!