Revise installed software report (limited to only Win 7 O/S machines and those seen within 120 days)

[dsn7287]

Hello,

Firstly, my apologies; I believe I initially posted this request incorrectly in the "Reports" forum topic which is for complete/ finalized reports.

Ok as for my request, I am really hoping someone can help me with this please:

I would like to revise the existing Lansweeper report: "Software: All installed software" to limit the report to capture software applicable only to the following:
1. Computers that have been "Last Seen" within the last 4 months (~120 days).
2. Only computers running desktop versions of the O/S (Win 7 SP1, Win 8 and Win 10).

Below is the original report's code

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Count(tblSoftware.AssetID) As Total
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 tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher
Order By Total Desc

I have tried to do this multiple times to edit this report but I keep getting the following error: "Column 'tblAssets.Lastseen' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.". I am sure I am doing something wrong given that my SQL knowledge is pretty limited.

Would someone be able to assist me please? Any help would be greatly appreciated.

Thanks

[altodd]

As another thought, if you add the column to your Group By clause, that solves the immediate problem. Not sure if anything else is wrong upon immediate inspection.

[altodd]

dsn7287 wrote:
Hello,

Firstly, my apologies; I believe I initially posted this request incorrectly in the "Reports" forum topic which is for complete/ finalized reports.

Ok as for my request, I am really hoping someone can help me with this please:

I would like to revise the existing Lansweeper report: "Software: All installed software" to limit the report to capture software applicable only to the following:
1. Computers that have been "Last Seen" within the last 4 months (~120 days).
2. Only computers running desktop versions of the O/S (Win 7 SP1, Win 8 and Win 10).

Below is the original report's code

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Count(tblSoftware.AssetID) As Total
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 tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher
Order By Total Desc

I have tried to do this multiple times to edit this report but I keep getting the following error: "Column 'tblAssets.Lastseen' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.". I am sure I am doing something wrong given that my SQL knowledge is pretty limited.

Would someone be able to assist me please? Any help would be greatly appreciated.

Thanks

Do you still need help with this? I may be able to help some, my SQL knowledge is growing with how many reports I've had to do.