Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper
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,
Where tblAssets.Lastseen > GetDate() - 30 And tblState.Statename = 'Active'
Group By tblSoftwareUni.softwareName,
Having tblSoftwareUni.softwareName Like '%java%'
Order By Total Desc,
Engaged Sweeper
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
--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,
--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,