→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
joes3
Engaged Sweeper III
I'm in need of some SQL assistance. I'm trying to design a report that lists every computer that has Adobe Acrobat installed on it, which version of Acrobat is installed, and the Product Key for Acrobat if it exists. It's the last bit that's throwing me for a loop. Seems like tblSoftware and tblSoftwareUni don't share a direct relationship with tblSerialnumber. I can't even match it based on the name of the software since tblSerialnumber.Product and tblSoftwareUni.softwareName aren't identical. This means that when I try to join those tables to tblAssets, I get... wrong information. Presumably it's an issue with my JOINS (not sure I'm using the right ones) so if someone else could help me with this I'd appreciate it.

Here's what I have so far:
Select Top 1000000 tblAssets.AssetName,
soft.softwareName,
soft.softwareVersion,
soft.SoftwarePublisher,
serial.ProductKey
From tblAssets
Join (
Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID)
As soft On soft.AssetID = tblAssets.AssetID
Join (
Select tblSerialnumber.AssetID,
tblSerialnumber.ProductKey
From tblSerialnumber)
As serial On serial.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName


I would like it to look like this where it shows every computer with Acrobat and lists the product key when it's available but still lists the computer and the Acrobat version info for those computers where the product key isn't available.



Of course, a better solution would be if Lansweeper could gather the Product Key from every version of Acrobat *hint hint* because then I could just run the built-in License: Software licensekey overview report and get everything I need. I'm currently on the trial version of Lansweeper which is listed as 5.1.0.55 on the license page.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
It is impossible to make a report that links the table tblsoftware and tblserialnumber. So the report that you want to make is currently impossible.

We will take your remarks in mind but I cannot assure you that this is going to be implemented.

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
It is impossible to make a report that links the table tblsoftware and tblserialnumber. So the report that you want to make is currently impossible.

We will take your remarks in mind but I cannot assure you that this is going to be implemented.