Create Report Showing Software Installed and Price of Software.

[Mcasim]

Hello Everyone.

I'm trying to create a report that will show the software installed in computers and show the cost of each program installed.

I have also created in the Licenses-->Edit purchased licenses, entries for each software i found installed in the computers.
Whether it is Free, GPL, bought and payed etc.

What i would like to do, is create a report that will show the software installed in each computer (like the existing one Software: List of software by computer) and add the columns i find in the report Licenses-->Software license compliance.

Is there a way to connect the tblAssets with the tblSqlLicenses ? It seems that the tblLicenses contain the prices of the Software i have placed in the Licenses-->Edit purchased licenses. But these two seems that cannot be connected.
Is there a way to do this ?

Thank you for your time.
Harry.

[Mcasim]

Hello Susan !

Sorry for asking your help once more but i'm in a bit of trouble again.

The reports is not giving me reports, about computers/Servers running Linux and Mac as operating system.

I tried running a script-report, that you gave to another Lansweeper customer.
( http://www.lansweeper.com/Forum/yaf_postst12616_Mac-Software-on--Software--List-of-software-by-computer.aspx#post45456 )

The report shows every computer running Windows-Linux-Mac operating systems and their software installed.

How am i gonna add the priceperlicense from the tblLicenses, in that report, in order to have the prices for each software ?

So i tried doing the following but of course is not correct as i get an error saying:
The column name is not valid. [ Node name (if any) = tblLicenses,Column name = Priceperlicense ]

Could you please help me one more time ?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblLicenses.Priceperlicense
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblLicenses On tblLicenses.Priceperlicense =
tblSoftwareUni.softwareName
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblLinuxSoftware.Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblLicenses.Priceperlicense
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblLinuxSoftware.SoftwareUniID
Where tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblMacApplications.Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblLicenses.Priceperlicense
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMacApplications
On tblAssets.AssetID = tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblMacApplications.softid = tblSoftwareUni.SoftID
Where tblAssetCustom.State = 1
Order By AssetName,
Domain

Thank you once more for all your help.

Best regards,
Harry.

[Mcasim]

Thank you very much for the support Susan.

It worked like a charm !

[Susan_A · Accepted solution]

You can always manually link two tables by left-clicking and dragging a field in one table over to the corresponding field in the other table. If the name of your license item corresponds with the name of the software package - it should by default - you can link the softwareName field in the tblSoftwareUni table and the softwareName field in the tblLicenses table. Afterwards, you can tick the Priceperlicense field in tblLicenses.