cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cross_eur
Engaged Sweeper II
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
1 REPLY 1
Nick_VDB
Champion Sweeper III
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