Non-Printable Characters in SCCM

Recently, the Enhansoft Team was made aware of an issue with non-printable characters in several System Center Configuration Manager (SCCM) SQL Server views. What does this mean? SCCM reports with non-printable characters are failing because several SQL Server views and Role-Based Administration (RBA) functions are affected. Among those identified: Add/Remove Programs, Recently Used Applications and Software Metering. Unfortunately, there may be more views and functions affected, but as of this time these are the only ones we know about.

TL;DR

In case you don’t have time to read this post in its entirety, here is a short summary. If you are seeing this error (see the screenshot above or the section below) within the default SCCM reports, upgrade to SCCM 1810 or later. If you are seeing this error within one of Enhansoft Reporting’s reports, upgrade to SCCM 1810 or later and ensure that you are running the latest version of Enhansoft Reporting. If you are seeing this error within your own custom reports, keep reading below because we’ll give you additional information about how to fix this problem.

Non-Printable Characters Error Message

If you create a report and use one of the affected SQL Server views, you might see an error similar to the one listed below. Depending on what hex character is the problem, your error message might be slightly different.

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. There is an error in XML document (1, 37635). “, hexadecimal value 0x02, is an invalid character. Line 1, position 37635.

Effected SQL Server Views and RBA Functions

Add/Remove Programs

Six SQL Server views and five RBA functions are affected. Here they are:

• v_Add_Remove_Programs
• v_GS_ADD_REMOVE_PROGRAMS
• v_GS_ADD_REMOVE_PROGRAMS_64
• v_GS_Mapped_Add_Remove_Programs
• v_HS_ADD_REMOVE_PROGRAMS
• v_HS_ADD_REMOVE_PROGRAMS_64
• fn_rbac_Add_Remove_Programs
• fn_rbac_GS_ADD_REMOVE_PROGRAMS
• fn_rbac_GS_ADD_REMOVE_PROGRAMS_64
• fn_rbac_HS_ADD_REMOVE_PROGRAMS
• fn_rbac_HS_ADD_REMOVE_PROGRAMS_64

Each of these views and RBA functions have the following columns that might exhibit the problem: DisplayName0, Publisher0, and ProdID0.

Recently Used Applications

Currently the only SQL Server view and RBA function that we know of that is affecting Recently Used Applications is:
v_GS_CCM_RECENTLY_USED_APPS and fn_rbac_GS_CCM_RECENTLY_USED_APPS

This SQL Server view has the following columns that might exhibit the problem: FileDescription0, msiDisplayName0, msiPublisher0, and ProductName0.

Software Metering

Similar to Recently Used Applications, there is only one SQL Server view and one RBA function that might be affected by this issue and they are:

v_MeteredFiles and fn_rbac_MeteredFiles

This SQL Server view has the following columns that might exhibit the problem: OriginalFileName, FileName, and MeteredFileName.

Solution

Once the Enhansoft Team was able to duplicate the non-printable characters problem, we contacted the Microsoft SCCM product team in order to make them aware of this issue. The SCCM Team updated the very next release of SCCM current branch 1810 to include a fix for this issue. We thank them for their fast action in resolving this issue.

If you see this problem with the built-in SCCM reports, make sure you upgrade to SCCM 1810 or later. Fortunately, that’s all you need to do for the built-in reports.

However, if you are seeing this problem with custom reports, first you’ll need to upgrade to SCCM 1810. Next, edit your SQL Server query to add the following function to each column.

dbo.fn_RemoveNonPrintableChars

Here’s a simple example:

Select
 ARP.Publisher0 as ‘Publisher’,
 ARP.DisplayName0 as ‘Display Name’,
 ARP.Version0 as ‘Version’
from
 dbo.v_Add_Remove_Programs ARP

Now, see below how we updated the columns by adding dbo.fn_RemoveNonPrintableChars. This will allow the function to filter out all bad characters.

Select
 dbo.fn_RemoveNonPrintableChars(ARP.Publisher0) as ‘Publisher’,
 dbo.fn_RemoveNonPrintableChars(ARP.DisplayName0) as ‘Display Name’,
 ARP.Version0 as ‘Version’
from
 dbo.v_Add_Remove_Programs ARP

What Are the Non-Printable Characters?

0 = NUL (null)

1 = SOH (start of heading)

2 = STX (start of text)

3 = ETX (end of text)

4 = EOT (end of transmission)

5 = ENQ (enquiry)

6 = ACK (acknowledge)

7 = BEL (bell)

8 = BS (backspace)

9 = TAB (horizontal tab)

10 = LF (NL line feed, new line)

11 = VT (vertical tab)

12 = FF (NP form feed, new page)

13 = CR (carriage return)

14 = SO (shift out)

15 = SI (shift in)

16 = DLE (data link escape)

17 = DC1 (device control 1)

18 = DC2 (device control 2)

19 = DC3 (device control 3)

20 = DC4 (device control 4)

21 = NAK (negative acknowledge)

22 = SYN (synchronous idle)

23 = ETB (end of trans. block)

24 = CAN (cancel)

25 = EM (end of medium)

26 = SUB (substitute)

27 = ESC (escape)

28 = FS (file separator)

29 = GS (group separator)

30 = RS (record separator)

31 = US (unit separator)

127 = Del (Delete)

If you go back and review the error (0x02) at the top of this blog post, you will see that 0x02 = STX (start of text) was embedded within the ARP results for the query. We confirmed at least two Microsoft software updates which put this character within the Add/Remove Program titles. We also investigated a few other titles that exhibited this issue.

Are you finding similar SCCM reporting issues? Whether it’s with our reports or the built-in reports, please let us know. If you have any questions about non-printable characters and SCCM reports, please feel free to contact @GarthMJ.