→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mwrobo09
Champion Sweeper
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.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
jacobsenm
Engaged Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper III
I forgot to mentikn that the report can not be saved after the error message.
jacobsenm
Engaged Sweeper III
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
Lansweeper Alumni
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
Champion Sweeper
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
Lansweeper Alumni
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