Finding multiple adobe softwar by computer

[cross_eur]

I am trying to create a report that will count the number of adobe software that are on computers. I need to see this to be able to identify if we should be assigning a Creative complete license, or multiple Creative sole licenses to each computer.

Here is what I have so far, that outputs all the adobe software on the computers, but does not count how many pièces of software that are on each computer.

Thanks for any help that is offered.



Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftwareUni.SoftwarePublisher) As Total,
tblAssets.AssetName
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
Where tblSoftwareUni.SoftwarePublisher Like N'%Adobe%' And
tblSoftwareUni.softwareName Not Like '%help%' And
tblSoftwareUni.softwareName Not Like '%air%' And
tblSoftwareUni.softwareName Not Like '%flashax%' And
tblSoftwareUni.softwareName Not Like '%flashpg%' And
tblSoftwareUni.softwareName Not Like '%reader%' And
tblSoftwareUni.softwareName Not Like '%flash%player%' And
tblSoftwareUni.softwareName Not Like '%quiz%' And
tblSoftwareUni.softwareName Not Like '%digital%' And
tblSoftwareUni.softwareName Not Like '%wallaby%' And
tblSoftwareUni.softwareName Not Like '%assistant%' And
tblSoftwareUni.softwareName Not Like '%connect%' And
tblSoftwareUni.softwareName Not Like '%toolkit%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%reviewer' And
tblSoftwareUni.softwareName Not Like '%add-%' And
tblSoftwareUni.softwareName Not Like '%shockwave%player%' And
tblSoftwareUni.softwareName Not Like '%acrobat%' And tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.AssetName
Order By tblAssets.AssetName,
Total Desc,
Software

[Nick_VDB]

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Total,
  Count.Count,
  tblAssets.AssetName
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 (Select Top 1000000 tblAssets.AssetID,
    Count(tblAssets.AssetID) As Count
  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
  Where tblSoftwareUni.softwareName Not Like '%help%' And
    tblSoftwareUni.softwareName Not Like '%air%' And
    tblSoftwareUni.softwareName Not Like '%flashax%' And
    tblSoftwareUni.softwareName Not Like '%flashpg%' And
    tblSoftwareUni.softwareName Not Like '%reader%' And
    tblSoftwareUni.softwareName Not Like '%flash%player%' And
    tblSoftwareUni.softwareName Not Like '%quiz%' And
    tblSoftwareUni.softwareName Not Like '%digital%' And
    tblSoftwareUni.softwareName Not Like '%wallaby%' And
    tblSoftwareUni.softwareName Not Like '%assistant%' And
    tblSoftwareUni.softwareName Not Like '%connect%' And
    tblSoftwareUni.softwareName Not Like '%toolkit%' And
    tblSoftwareUni.softwareName Not Like '%viewer%' And
    tblSoftwareUni.softwareName Not Like '%reviewer' And
    tblSoftwareUni.softwareName Not Like '%add-%' And
    tblSoftwareUni.softwareName Not Like '%shockwave%player%' And
    tblSoftwareUni.softwareName Not Like '%acrobat%' And
    tblSoftwareUni.SoftwarePublisher Like N'%Adobe%' And tblAssetCustom.State =
    1
  Group By tblAssets.AssetID) As Count On tblAssets.AssetID = Count.AssetID
Where tblSoftwareUni.softwareName Not Like '%help%' And
  tblSoftwareUni.softwareName Not Like '%air%' And
  tblSoftwareUni.softwareName Not Like '%flashax%' And
  tblSoftwareUni.softwareName Not Like '%flashpg%' And
  tblSoftwareUni.softwareName Not Like '%reader%' And
  tblSoftwareUni.softwareName Not Like '%flash%player%' And
  tblSoftwareUni.softwareName Not Like '%quiz%' And
  tblSoftwareUni.softwareName Not Like '%digital%' And
  tblSoftwareUni.softwareName Not Like '%wallaby%' And
  tblSoftwareUni.softwareName Not Like '%assistant%' And
  tblSoftwareUni.softwareName Not Like '%connect%' And
  tblSoftwareUni.softwareName Not Like '%toolkit%' And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  tblSoftwareUni.softwareName Not Like '%reviewer' And
  tblSoftwareUni.softwareName Not Like '%add-%' And
  tblSoftwareUni.softwareName Not Like '%shockwave%player%' And
  tblSoftwareUni.softwareName Not Like '%acrobat%' And
  tblSoftwareUni.SoftwarePublisher Like N'%Adobe%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Total Desc,
  Software