Unauthorized Software Reports

[ccordova]

I run a daily report showing unauthorized software, and I’m having difficulty clearing old items that either haven’t checked-in in months or have been updated. The list doesn’t appear to be accurate. Does anyone know how to clear the list to remove old machines/items, and start with a fresh unauthorized software report? It’s set to ‘permanently delete assets not seen in the last 30 days’, but the report is still showing machines last seen 2-3 months ago. It's also showing software that has been removed or updated.

[ccordova]

It's a canned report - Software: Unauthorized software

[AZHockeyNut]

ccordova wrote:
It's a canned report - Software: Unauthorized software

in that case, edit the report in the report editor (save as a new report if you don't want to modify the canned one)
add a where clause to the sql that filters the dates that you want to do. You could also modify the joins to filter on a date as well. There are many date fields you could filter on really depends on what you are actually looking for and your data. There is the tblassets.lastseen, lasttried, lastactivescan, lastlspush, lastipscan etc etc...

below is an off the cuff example, give it a go and it should get you closer to what you want. (this was done using SSMS and not the report editor, on a system running sql server not sql express so I cannot say for certain if will work as is for your needs)

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Description,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftware.Lastchanged,
tsysOS.Image As icon,
tblsoftware.Installdate,
tblassets.lastseen

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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSoftwareUni.Approved = 2 And
datediff(d, tblassets.lastseen, getdate()) = 1 and
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
Software

[AZHockeyNut]

ccordova wrote:
I run a daily report showing unauthorized software, and I’m having difficulty clearing old items that either haven’t checked-in in months or have been updated. The list doesn’t appear to be accurate. Does anyone know how to clear the list to remove old machines/items, and start with a fresh unauthorized software report? It’s set to ‘permanently delete assets not seen in the last 30 days’, but the report is still showing machines last seen 2-3 months ago. It's also showing software that has been removed or updated.

is this a canned report or something you wrote? the query can likely be modified to provide what you are looking for.