Clone working filter = "cannot be resolved"

[Iskyfly]

Hello!

I am attempting to clone a known good filter and modifying the "LIKE" string in the "Edit Raw SQL" from

   [vri2_Computer].[Name] LIKE 'GT__ZZ%' 

to

   [vri2_Computer].[Name] LIKE 'EC__ZZ%' 

by select "Edit SQL" and changing the computer name prefix from GT to EC but I get the following when I click on OK;

"Raw SQL query cannot be resolved."

The same thing occurs if I create a new filter and copy / paste the query.

It has been a while since I have worked with Raw SQL filters. I originally created these about 3 years ago.  

------------------------------
Giles
------------------------------

[Igor Perevozchikov]

Hi Iskyfly!

1. What warning or error message shown in logs if you open "Altiris Log Viewer" on SMP Server and click test query?
2. These filters were created in "ITMS views" page or from "Manage" > "Filters" page?
3. Please try to run these queries separately in same dialog

SELECT
[vri2_Computer].[Guid] AS [_ItemGuid],
[vri2_Computer].[Name]
FROM
[vRM_Computer_Item] AS [vri2_Computer]
WHERE
LEN([vri2_Computer].[Name])>13
AND
[vri2_Computer].[Name] LIKE '%EC__ZZ%'

SELECT
[vri2_Computer].[Guid] AS [_ItemGuid],
FROM
[vRM_Computer_Item] AS [vri2_Computer]
WHERE
LEN([vri2_Computer].[Name])>13
AND
[vri2_Computer].[Name] LIKE '%EC__ZZ%'

Best regards,
IP.

[Iskyfly]

Hi Igor!

1.

Clicking on the filter in ITMS view generates this;

Invalid collection definition query found for collection 'Copy of Filter - RV Scala' (3137779c-b3ea-479a-808e-70c769544ea6). Incorrect number of columns returned from the query. 1 GUID column is expected, but 2 is found. Collection membership update will ignore the other columns and continue with the operation.
-----------------------------------------------------------------------------------------------------
Date: 3/27/2024 8:49:59 AM, Tick Count: 286861156 (3.07:41:01.1560000), Host Name: NS1V, Size: 612 B
Process: w3wp (8160), Thread ID: 762, Module: Altiris.NS.StandardItems.dll
Priority: 4, Source: Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.FullUpdatePostDataSrcProcessing
File: C:\ProgramData\Symantec\SMP\Logs\a.log

"Test Query" = "A Filter SQL query must return 1 GUID column."

A Filter '3137779c-b3ea-479a-808e-70c769544ea6' SQL query must return 1 GUID column.
-----------------------------------------------------------------------------------------------------
Date: 3/27/2024 8:51:57 AM, Tick Count: 286979843 (3.07:42:59.8430000), Host Name: NS1V, Size: 383 B
Process: w3wp (8160), Thread ID: 461, Module: Symantec.ActivityCenter.Api.dll
Priority: 2, Source: Symantec.ActivityCenter.Api.Models.Filter.FilterProvider.ResolveFilterSqlQuery
File: C:\ProgramData\Symantec\SMP\Logs\a.log

2.- I can't remember if the original filters were created in ITMS view or Manager > Filters. At the moment, what I was attempting to do was clone the filter in ITMS view and then edit the SQL query.

I recreated the filter in ITMS view and things seem to work now after I changed vri2 to vri1

SELECT
   [vri1_Computer].[Guid] AS [Guid]
FROM
   [vRM_Computer_Item] AS [vri1_Computer]
WHERE
   LEN([vri1_Computer].[Name]) > 13
AND
   [vri1_Computer].[Name] like 'EC__ZZ%'  

I am not SQL literate. I did this by trial and error.

However, even though the new filter works and test query works and no red error messages in ITMS console the NS server log file shows this when I click on test query of the filter in ITMS view (which works- screenshot attached)

The NS log viewer shows this;

Invalid collection definition query found for collection 'Filter - EC Scala' (4d08aa06-aae6-404c-a9cf-ed3152d354b1). Incorrect number of columns returned from the query. 1 GUID column is expected, but 2 is found. Collection membership update will ignore the other columns and continue with the operation.
-----------------------------------------------------------------------------------------------------
Date: 3/27/2024 9:42:42 AM, Tick Count: 290024812 (3.08:33:44.8120000), Host Name: NS1V, Size: 622 B
Process: w3wp (8160), Thread ID: 393, Module: Altiris.NS.StandardItems.dll
Priority: 4, Source: Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.FullUpdatePostDataSrcProcessing
File: C:\ProgramData\Symantec\SMP\Logs\a.log

Again, everything is working now.

Back in December our NS and site servers had to be "migrated" to new servers by our admins (Windows Server 2022 Datacenter) and at the same time ITMS was upgraded to 8.6 RU3. Perhaps this "broke" something with the filters.

Thank you!

Giles

------------------------------
Giles
------------------------------