Computer Software compare report

[mwrobo09]

I am looking to do a refresh of computers and would like to have a report that showed the difference in software between the new PC and the old to determine if any software was missed. Thanks.

[jacobsenm]

Thank you for the reply.
It was not the answer I was hoping to hear ...
Anyway, we will try to find another solution.

Thank you.

[Daniel_B]

This works if your database is running on SQL Compact. If you are using SQL Server, you can't create a report with parameters in the report editor. In that case you would need to create a view directly on your SQL Server database through a database script and also add an entry to table tsysReports which refers to the name of the view you created. However, we can't provide scripts here, so you might need to test this with the help of your DB admin.

[jacobsenm]

I forgot to mentikn that the report can not be saved after the error message.

[jacobsenm]

Hi,
I like the solution provided. However I had tested it and I can not save the report.
It gives an error about to declare NewPC.

Any ideas ?

Thanks

[Daniel_B]

You could work with a parameter and only change the parameter in the URL of the report. Parameters have to be submitted with @ as initial character. For example:

Select Top 1000000 tblSoftwareUni.softwareName,
  Case When Coalesce(tNotInstalled.AssetID, 0) = 0 Then 'not installed'
    Else 'installed' End As [installed on new computer]
From tblSoftware
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
  Left Join (Select tblAssets.AssetID,
    tblSoftwareUni.SoftID
  From tblSoftwareUni,
    tblAssets
    Inner Join tblComputersystem
      On tblAssets.AssetID = tblComputersystem.AssetID
  Where tblAssets.AssetName = @newPC And
    Not Exists(Select tblSoftware.softID,
      tblSoftware.AssetID From tblSoftware
    Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
      tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
    tblSoftware.softID
Where tblAssets.AssetName = @oldPC
Order By tblSoftwareUni.softwareName,
  tblAssets.AssetName

When you save this report, you get an error message. But now you can go to the address bar of your browser and at the end of the report's URL add the following:

&@oldPC=HostnameA&@newPC=HostnameB

That will list the report with computer "HostnameA" as oldPC and computer "HostnameB" as newPC.

[mwrobo09]

This is helpful, thank you. Need to add to the wish list to have the ability to add a drop down box to a report so that this could be easily changed instead of having to change the query all the time.

[Daniel_B · Accepted solution]

The following report lists software on one PC ("OldPC") and whether it is installed on another PC ("NewPC") or not. This page provides a report which lists all workstation computers not having one of the products installed, however the report might take a while to calculate.

Select Top 1000000 tblSoftwareUni.softwareName,
  Case When Coalesce(tNotInstalled.AssetID, 0) = 0 Then 'not installed'
    Else 'installed' End As [installed on new computer]
From tblSoftware
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
  Left Join (Select tblAssets.AssetID,
    tblSoftwareUni.SoftID
  From tblSoftwareUni,
    tblAssets
    Inner Join tblComputersystem
      On tblAssets.AssetID = tblComputersystem.AssetID
  Where tblAssets.AssetName = 'NewPC' And
    Not Exists(Select tblSoftware.softID,
      tblSoftware.AssetID From tblSoftware
    Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
      tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
    tblSoftware.softID
Where tblAssets.AssetName = 'OldPC'
Order By tblSoftwareUni.softwareName,
  tblAssets.AssetName