Inventory report for Adobe license reviews

[Daniel_B]

Adobe license audits require a full list of computers from asset inventory software and based on AD computer accounts as well as an inventory on Adobe software installations on the network. The following report provides this.

Note: In order to get a valid list, ensure that all your computers were successfully scanned using Scheduled Computer scanning (providing the ADSI paths of all your computer OU's in Active Directory) and any other scanning method like IP Address Range scanning or LsPush.

The report lists all computers scanned in your network.

Sorted on:
- Asset name
- Software name

Select Top 1000000 tblAssets.AssetID,
   tblAssets.Domain,
   tblAssets.AssetName,
   Case When tblComputersystem.Domainrole > 1 Then 'Server' Else Case
       When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
       Else 'Laptop' End End As Type,
   tblState.Statename As [Asset state],
   tsysAssetTypes.AssetTypeIcon10 As icon,
   tblAssets.Lastseen,
   tblOperatingsystem.Caption As OS,
   tSoftware.softwareName,
   tSoftware.SoftwarePublisher,
   tSoftware.softwareVersion
 From tblAssets
   Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
   Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
   Left Join (Select tblSoftware.AssetID,
     tblSoftwareUni.softwareName,
     tblSoftwareUni.SoftwarePublisher,
     tblSoftware.softwareVersion
   From tblSoftware
     Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
   Where (tblSoftwareUni.softwareName Like '%adobe%') Or
     (tblSoftwareUni.SoftwarePublisher Like '%adobe%')) tSoftware
     On tSoftware.AssetID = tblAssets.AssetID
   Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
   Inner Join tblOperatingsystem
     On tblAssets.AssetID = tblOperatingsystem.AssetID
   Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
   Left Join tblState On tblState.State = tblAssetCustom.State
 Order By tblAssets.AssetName,
   tSoftware.softwareName

Adobe may ask for a full list of Adobe executables as well. This can't be retrieved in a fully automatic way as Lansweeper doesn't search for files with an unknown file path. However, you could scan for processes (under Configuration\Item Wait Time enable scanning for PROCESS and reduce the scanning interval), use a report to list the executable paths of Adobe-related processes and set up Custom File scanning based on these.
Alternatively use this deployment package in order to search for executables on network computers.

[brodiemac]

Daniel.B wrote:
Adobe license audits require a full list of computers from asset inventory software and based on AD computer accounts as well as an inventory on Adobe software installations on the network. The following report provides this.

Note: In order to get a valid list, ensure that all your computers were successfully scanned using Scheduled Computer scanning (providing the ADSI paths of all your computer OU's in Active Directory) and any other scanning method like IP Address Range scanning or LsPush.

The report lists all computers scanned in your network.

Sorted on:
- Asset name
- Software name

Select Top 1000000 tblAssets.AssetID,
   tblAssets.Domain,
   tblAssets.AssetName,
   Case When tblComputersystem.Domainrole > 1 Then 'Server' Else Case
       When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
       Else 'Laptop' End End As Type,
   tblState.Statename As [Asset state],
   tsysAssetTypes.AssetTypeIcon10 As icon,
   tblAssets.Lastseen,
   tblOperatingsystem.Caption As OS,
   tSoftware.softwareName,
   tSoftware.SoftwarePublisher,
   tSoftware.softwareVersion
 From tblAssets
   Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
   Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
   Left Join (Select tblSoftware.AssetID,
     tblSoftwareUni.softwareName,
     tblSoftwareUni.SoftwarePublisher,
     tblSoftware.softwareVersion
   From tblSoftware
     Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
   Where (tblSoftwareUni.softwareName Like '%adobe%') Or
     (tblSoftwareUni.SoftwarePublisher Like '%adobe%')) tSoftware
     On tSoftware.AssetID = tblAssets.AssetID
   Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
   Inner Join tblOperatingsystem
     On tblAssets.AssetID = tblOperatingsystem.AssetID
   Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
   Left Join tblState On tblState.State = tblAssetCustom.State
 Order By tblAssets.AssetName,
   tSoftware.softwareName

Adobe may ask for a full list of Adobe executables as well. This can't be retrieved in a fully automatic way as Lansweeper doesn't search for files with an unknown file path. However, you could scan for processes (under Configuration\Item Wait Time enable scanning for PROCESS and reduce the scanning interval), use a report to list the executable paths of Adobe-related processes and set up Custom File scanning based on these.
Alternatively use this deployment package in order to search for executables on network computers.

How do I get it to exclude Adobe Reader DC?

[maciej_przygodz]

Daniel.B wrote:
Adobe license audits require a full list of computers from asset inventory software and based on AD computer accounts as well as an inventory on Adobe software installations on the network. The following report provides this.

Note: In order to get a valid list, ensure that all your computers were successfully scanned using Scheduled Computer scanning (providing the ADSI paths of all your computer OU's in Active Directory) and any other scanning method like IP Address Range scanning or LsPush.

The report lists all computers scanned in your network.

Sorted on:
- Asset name
- Software name

Select Top 1000000 tblAssets.AssetID,
   tblAssets.Domain,
   tblAssets.AssetName,
   Case When tblComputersystem.Domainrole > 1 Then 'Server' Else Case
       When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
       Else 'Laptop' End End As Type,
   tblState.Statename As [Asset state],
   tsysAssetTypes.AssetTypeIcon10 As icon,
   tblAssets.Lastseen,
   tblOperatingsystem.Caption As OS,
   tSoftware.softwareName,
   tSoftware.SoftwarePublisher,
   tSoftware.softwareVersion
 From tblAssets
   Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
   Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
   Left Join (Select tblSoftware.AssetID,
     tblSoftwareUni.softwareName,
     tblSoftwareUni.SoftwarePublisher,
     tblSoftware.softwareVersion
   From tblSoftware
     Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
   Where (tblSoftwareUni.softwareName Like '%adobe%') Or
     (tblSoftwareUni.SoftwarePublisher Like '%adobe%')) tSoftware
     On tSoftware.AssetID = tblAssets.AssetID
   Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
   Inner Join tblOperatingsystem
     On tblAssets.AssetID = tblOperatingsystem.AssetID
   Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
   Left Join tblState On tblState.State = tblAssetCustom.State
 Order By tblAssets.AssetName,
   tSoftware.softwareName

Adobe may ask for a full list of Adobe executables as well. This can't be retrieved in a fully automatic way as Lansweeper doesn't search for files with an unknown file path. However, you could scan for processes (under Configuration\Item Wait Time enable scanning for PROCESS and reduce the scanning interval), use a report to list the executable paths of Adobe-related processes and set up Custom File scanning based on these.
Alternatively use this deployment package in order to search for executables on network computers.

Hi, nice report.
Can this report could also have informations about adobe istalled on Mac computers ?
At the moment I've got about 400 Mac computers added to the domain - it will be great if I can have one report for PC'a and Mac's.

[Daniel_B]

The first post got a small addition: This deployment package allows to search for executables on network computers. It uses a standard dir command and should be quite fast. However, we can't guarantee that it won't strongly affect the performance of client computers.

[Daniel_B]

The default installation of Lansweeper uses SQL Compact as database service. This has many restrictions as you experienced. You can get around it by using only commands which are supported by SQL Compact, like the following statement under the SELECT clause:

 SubString(tSoftware.softwareVersion, 1, CharIndex('.',
  tSoftware.softwareVersion) - 1) As [major version]

However, if you would like to use full SQL compatible reports (with some restrictions caused by the report editor), we recommend that you migrate your database to SQL server Express or higher edition. Instructions on how to do it can be found in this KB article.

[joes3]

I tried sorting by Version and it put the table in this order: 10 > 11 > 9. I looked at the data dictionary and it looks like tblSoftware.softwareVersion is in nvarchar type so I tried a couple of different methods to get it in a format that I could sort properly.

- In the SELECT statement I tried doing a LEFT(tSoftware.softwareVersion, CHARINDEX('.', tSoftware.softwareVersion) - 1) to get just the major version number. This gave me an error message:

Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 424,Token in error = Left ]"

- In the ORDER BY statement I tried using CAST(tSoftware.softwareVersion AS INT) but this gave me a different error message:

error while getting report
Data conversion failed. [ OLE DB status value (if known) = 2 ]

- I also tried multiplying the column by 1 in the ORDER BY statement and got the same error message as when CASTing.

The data dictionary is helpful but it needs to include which standard SQL commands are not available. Is there another way to sort the version numbers correctly?