Not able to set the Filter in Patch Reports
Article ID: 161185
Updated On:
Products
Issue/Introduction
Unable to select a filter in the Patch Report - Windows Compliance by Bulletin. The option to select is not available that 'Filter by' is not available. Cloning the report will allow the option to show but logging in as the problem user or even AppID didn't allow the original report to show the option.
Note: This was seen on the Application Identity, ruling out the possibility of permissions scoping being unable to view a Filter Target created by a user with higher credentials.
No Error message but the following showed in the logs while trying to run the report
Process: <unknown> (23192),
Thread ID: 678,
Module: <unknown>
Priority: 4, Source: Altiris.Reporting.DataSource.RawSqlDataSource.Run
RawSqlDataSource is running query:
EXEC [dbo].[spPMWindows_ComplianceByBulletin]
@VendorGuid = '9d5f6bb8-8adf-49d1-9d84-2932ca46ce1e',
@OperatingSystem = '%',
@DistributionStatus = '%',
@ScopeCollectionGuid = 'da713c37-d04b-4069-bec0-649079a78646',
@StartDate = '2013-09-17T00:00:00',
@EndDate = '2014-09-17T00:00:00',
@pCulture = 'en-US',
@FilterCollection = 'a8d70141-9520-4747-9864-dc484113338d',
@TrusteeScope = '{2C87DA4E-xxxx...},{2E1F478A-xxx-...},{582029E2-xxx-...},{B760E9A9-xxx-....},{FD38CD61-xxxx-....}'
Process: <unknown> (23192),
Thread ID: 678,
Module: <unknown>
Priority: 4, Source: Altiris.Reporting.DataSource.RawSqlDataSource.Run
RawSqlDataSource returned 0 rows in 'Table' table
Cause
Customer selected a test filter in the report, then later deleted the filter from the NS Console and this caused a 'lost link' that could not be resolved by the report. Or some other issue with the report cache being corrupt for a user's setting. It has also been seen where the cache of the report settings was corrupted for all users.
Environment
NS and Patch Managment 7.x / 8.x
Resolution
Note: This issue is corrected in version 8.5
For 8.1
1. Run the following query in SQL Management Studio
--Enter Domain\Username of the affected user here
declare @user as nvarchar (250) = 'Domain\Username'
declare @ug as uniqueidentifier
set @ug = (select top 1 '{' + cast (Guid as nvarchar (50)) + '}'
from vRM_Trustee_Item where name = @user)
--select *--SUBSTRING (id, 34, 32) Guid
delete
from UserSettings
where LEN (Id) = 65
and id like '%_%'
and Sid = @ug
and SUBSTRING (id, 34, 32) in
( select replace (Guid, '-', '') Guid
from vitem
where ClassGuid = 'AA232A71-E633-4F4A-A48F-BCCCC7535269'
and ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA')
2. Restart IIS from CMD Prompt on SMP: IISReset
Note: Part of the data is stored in the IIS Metadata and needs to be cleared.
3. Open NS Console and Open the problem Report: As it is loading you may still see the old view but wait until the page has loaded and press the refresh button.
For 7.5, 7.6, and 8.0
Work through the following on the SMP
1. Log into the Notification Server as Problem User
2. In NS Console change view to NOT look at the problem report
3. Open a CMD Prompt and type
whoami /user
4. Copy the SID for the Problem User
It will look something like 'S-1-5-21-1212121212-3434343434-5656565656-500'
5. Run the following Script in SQL Server Management Studio:
declare @sid as nvarchar (max)
set @sid = 'Place SID Here'
delete us
from UserSettings us
join ResourceAssociation ra on ra.ParentResourceGuid = us.[Sid]
and ra.ResourceAssociationTypeGuid = '4582ADBC-43FA-4783-807A-A3CD7D58992C'--Account Credential
join item i on i.Guid= ra.ResourceAssociationTypeGuid
join ResourceKey rk on ra.ChildResourceGuid = rk.ResourceGuid
AND rk.KeyName = 'sid'
AND rk.KeyValue = @sid
where Id like '%MyReportView%'
6. Restart IIS from CMD Prompt on SMP:
IISReset
Note: Part of the data is stored in the IIS Metadata and needs to be cleared.
7. Open NS Console and Open the problem Report: As it is loading you may still see the old view but wait until the page has loaded and press the refresh button.
Feedback
