Confused on a report output.

[drlord]

I'm trying to construct a report that will list all of the Adobe Acrobat installs and their corresponding install keys in our database. I have the report where its listing valid output, but its not listing anything over a numeric version in the product name. We're missing items like "Adobe Acrobat X" Any thoughts as to where I'm going wrong?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Department,
web40replicensekey.Product,
web40replicensekey.ProductKey
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Inner Join web40replicensekey On web40replicensekey.AssetID =
tblAssets.AssetID
Where web40replicensekey.Product Like 'Adobe Acrobat%'

[drlord]

BRILLIANT !!!! Thank you VERY much.

[RCorbeil · Accepted solution]

First, you can simplify your query slightly by referring to tblSerialNumber instead of web40replicensekey.

Second, LANSweeper collects what serial numbers it does separately from installed software. If you want to see the software name, version, etc. you need to link against tblSoftware and tblSoftwareUni. The serial numbers are stored separately in tblSerianNumber.

tblSoftware and tblSerialNumber can each be linked against a particular machine/asset, but there isn't anything sufficiently unique in tblSerialNumber to directly link against any particular entry in tblSoftware. The best you could do is make assumptions and fake some connections.

Quick-and-dirty:

SELECT DISTINCT TOP 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Department,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSerialnumber.ProductKey
FROM
  tblAssets
  LEFT JOIN tblSoftware     ON tblAssets.AssetID = tblSoftware.AssetID
  LEFT JOIN tblSoftwareUni  ON tblSoftwareUni.SoftID = tblSoftware.softID
  LEFT JOIN tblSerialnumber ON tblAssets.AssetID = tblSerialnumber.AssetID AND tblSerialnumber.Product LIKE 'Adobe Acrobat%'
  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 'Adobe Acrobat%'

With my inventory, that produces a list of Adobe Acrobats -- 8, 9 and XI -- and license keys for the 8 & 9. Presumably LANSweeper 5.1 doesn't root out keys for XI.