Not able to set a Filter in Reports. 'Filtered By' is not available.

Products

• Patch Management Solution for Windows
• Management Platform (Formerly known as Notification Server)

Issue/Introduction

Unable to select a filter in the reports that have a filter option.  The option to select on 'Filtered By' is not available. 

Cloning the report will allow the option to show, but logging in as a specific user doesn't allow the original report to show the option.

Note: This can affect 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 a Patch Management Solution report that was having a problem:

Entry 1:

Priority: 4, Source: Altiris.Reporting.DataSource.RawSqlDataSource.RunRawSqlDataSource is running query:

EXEC [dbo].[spPMWindows_ComplianceByBulletin]
    @VendorGuid          = '9d5f6bb8-8adf-49d1-9d84-2932ca46ce1e',
    @OperatingSystem     = '%',
    @DistributionStatus  = '%',
    @ScopeCollectionGuid = 'da713c37-d04b-4069-bec0-649079a78646',
    @StartDate           = '2016-09-17T00:00:00',
    @EndDate             = '2017-09-17T00:00:00',
    @pCulture            = 'en-US',
    @FilterCollection    = 'a8d70141-9520-4747-9864-dc484113338d',
    @TrusteeScope        = '{2C87DA4E-xxxx...},{2E1F478A-xxx-...},{582029E2-xxx-...},{B760E9A9-xxx-....},{FD38CD61-xxxx-....}'

 

Entry 2:

Priority: 4, Source: Altiris.Reporting.DataSource.RawSqlDataSource.RunRawSqlDataSource returned 0 rows in 'Table' table

 

Cause

Customer selected a custom filter in a report, then later deleted the filter from the NS Console.  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 particular users setting.  It has also been seen where the cache of the report settings was corrupted for all users.

Environment

NS and Patch Management 8.x

Resolution

This issue has been addressed with ITMS 8.1 RU4 release and later.

Work through the following on the SMP if you have a previous version:

1. Log into the Notification Server as Problem User

2. In NS Console go to the problem report

3. Open SQL Management Studio and run the following query:
   select distinct v.Name, t.Name as [User], '{' + a.sid + '}' [Sid], '%' + REPLACE (a.Guid, '-', '') + '%'Id
   from vItem v
   join
   (
   select a.Guid, a.Sid, a.Id
   from
   (
   select SUBSTRING (Id, 34 , 8) + '-' +  SUBSTRING (Id, 42, 4) + '-' +  SUBSTRING (Id, 46, 4) + '-' +  SUBSTRING (Id, 50, 4) + '-' +  SUBSTRING (Id, 54, 12) Guid,
   replace (replace (Sid, '{', ''), '}', '') as sid, Id
   from UserSettings
   where Id not like 'Altiris%'
   and Id not like 'Custom%'
   union
   select SUBSTRING (Id, 1 , 8) + '-' +  SUBSTRING (Id, 9, 4) + '-' +  SUBSTRING (Id, 13, 4) + '-' +  SUBSTRING (Id, 17, 4) + '-' +  SUBSTRING (Id, 21, 12) Guid,
   replace (replace (Sid, '{', ''), '}', '') as sid, Id
   from UserSettings
   where Id not like 'Altiris%'
   and Id not like 'Custom%'
   ) a
   where LEN (a.Guid) = 36
   ) a on a.Guid = v.Guid
   join vRM_Trustee_Item t on t.Guid = a.Sid

4. Copy the SID and Id for the Problem User and Report from the query results to use in step 5

5. Run the following Script in SQL Server Management Studio:

   declare @sid as nvarchar (max) = ''--ENTER THE "SID" FROM THE ABOVE QUERY HERE FOR THE PROBLEM "USER"
   declare @id as nvarchar (max)  = ''--ENTER THE "Id" FROM THE ABOVE QUERY HERE FOR THE PROBLEM "REPORT"

   delete us
   from UserSettings us
   where Id like '%' + @id + '%'
   and us.Sid like @sid

6. Restart IIS and the Altiris Service

   •  From CMD Prompt on SMP:   IISReset

   • Restart the Altiris Service

7. Close any open consoles for the problem user, Open the 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.