Adobe Acrobat with license key

[JTempleton]

Good day.

I am looking for help with a report that will show what computers have Adobe Acrobat, but not reader. I also need to see the Asset name, software, version, domain, Username, IP address, IP Location, Manufacturer, Model, Adobe Acrobat key.

thank you!

[RCorbeil · Accepted solution]

Try this as a starting point.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSerialnumber.Product,
  tblSerialnumber.ProductKey
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID And tblSerialnumber.Product Like '%Acrobat%'
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName Like 'Adobe Acrobat%'
  AND tblSoftwareUni.softwareName Not Like 'Adobe%Reader%'

The two conditions in the WHERE clause should limit the software to the full version of Acrobat, excluding Reader from the result set. The condition on the JOIN will limit license keys to those associated with a product containing "Acrobat" in the name.

There isn't a direct relationship between the software table and the license keys, so it's conceivable -- probable, even -- that you may end up finding more than one associated key on some machines.