Java report - Last seen and inactive

[dlabriola]

Hello, I'm looking to create a report that shows a count of the Java installations and versions. So far I have this below. I would like to exclude inactive computers (which I think I did by including 'Active') and exclude computers that haven't been seen in 14 days and / or 30 days. I'm on the right track but I need a little assistance. The report is still showing old computers that are still Active. Thanks for your help.

Select Top 1000000 tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
Count(tblSoftware.SoftwareID) As Total
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID,
tblState
Where tblAssets.Lastseen > GetDate() - 30 And tblState.Statename = 'Active'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
Having tblSoftwareUni.softwareName Like '%java%'
Order By Total Desc,
software

[Jelly]

Good resource for SQL

Also make sure you are using your database schema. You can open it from the report builder. Labeled "View Database Documentation" should be right of the title text box.

Select Top 1000000 
	tblSoftwareUni.softwareName As software,
	tblSoftware.softwareVersion As version,
	Count(tblAssets.AssetID) As Total
From tblAssets
	Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where 
--use the DATEADD(measuring by, length, from when)
	tblAssets.Lastseen > DATEADD(DAY, -30, GETDATE())
--use this to check if an asset is marked active 1=active
	And tblAssetCustom.State = 1
	And tblSoftwareUni.softwareName Like '%java%'
Group By tblSoftwareUni.softwareName, 
	tblSoftware.softwareVersion
--the having clause is only necessary if you are filtering on your agregate functions
--so if you wanted something like:
--HAVING Count(tblAssets.AssetID) > 10
--^ would only show software/versions that appear more than 10 times in your ecosystem
Order By Total Desc,
	software