"Query Failed" when viewing Computer Status Logs in Endpoint Protection Manager

Products

• Endpoint Protection

Issue/Introduction

"Query Failed" when viewing Computer Status Logs in SEPM (Symantec Endpoint Protection Manager).

Typical error from Reporting.log:

2019-06-18 16:52:04	ERROR:<br>QUERY: select TOP 20 * from ((select TOP 20 I.COMPUTER_ID as Computer_Idx, SA.AGENT_TYPE , SA.R_OS_TYPE, I.SERVICE_PACK, I.HARDWARE_KEY as HARDWARE_KEY,
                       I.COMPUTER_NAME as Computer, I.IP_ADDR1_TEXT as IP_Address, SA.PROFILE_SERIAL_NO,
                       convert(varchar,DATEADD(second, SA.LAST_UPDATE_TIME/1000, '19700101'),120) as LastCheckinTime1,
	               left( convert (varchar, PAT.patterndate, 120), 10 ) as patterndate1, PAT.sequence, PAT.revision, PAT.version,
                       SA.AGENT_VERSION as ProductVersion,case when SA.LAST_SCAN_TIME >= SA.CREATION_TIME THEN SA.LAST_SCAN_TIME ELSE 0 end AS LAST_SCAN_TIME ,
  		       SA.REBOOT_REQUIRED, SA.REBOOT_REASON, G.NAME as Clientgroup, S.NAME as Servergroup, I.CURRENT_LOGIN_USER as Clientuser, SA.AP_ONOFF as APOnOff,
  		       SA.PTP_ONOFF as PTPOnOff,SA.DA_ONOFF as DAOnOff,(case when (PEP_ONOFF IS NULL) then 127 else PEP_ONOFF END) as PEP_ONOFF,SA.CIDS_DRV_ONOFF as IPSOnOff,SA.CIDS_BROWSER_IE_ONOFF as IEOnOff,
  		       SA.CIDS_BROWSER_FF_ONOFF as FFOnOff,SA.CIDS_SILENT_MODE,SA.INFECTED as Infected, SA.FIREWALL_ONOFF, SA.DEPLOY_STATUS, SA.EDR_STATUS, SA.ATP_SERVER, SA.ELAM_ONOFF,
  		       (select count(*) from ALERTS A where A.COMPUTER_IDX=I.COMPUTER_ID and A.SOURCE='PowerEraser' and A.ACTUALACTION_IDX=14 and A.DELETED=0 and A.Mother_Idx = '' and A.ALERTDATETIME >= cast({ts'2019-05-18 16:52:03'} as datetime) and
            A.ALERTDATETIME <= cast({ts'2019-06-18 16:52:03'} as datetime)) as POWERERASER_PENDING,
  		       (Infected+(select count(*) from ALERTS A where A.COMPUTER_IDX=I.COMPUTER_ID and A.SOURCE='PowerEraser' and A.ACTUALACTION_IDX=14 and A.DELETED=0 and A.Mother_Idx = '' and A.ALERTDATETIME >= cast({ts'2019-05-18 16:52:03'} as datetime) and
            A.ALERTDATETIME <= cast({ts'2019-06-18 16:52:03'} as datetime))) as SUM_INFECTED,
								 G.ID as GROUP_ID, SA.LAST_UPDATE_TIME, SA.AGENT_ID, SA.LB_ONOFF,
                                 (SELECT MAX(PAT1.VERSION) FROM SEM_CONTENT C INNER JOIN PATTERN PAT1 ON C.PATTERN_IDX=PAT1.PATTERN_IDX WHERE C.AGENT_ID=SA.AGENT_ID AND C.DELETED=0 AND PAT1.DELETED=0 AND PAT1.PATTERN_TYPE = 'ADVML_AV') AS ADVML_VERSION,
                SA.NTR_STATUS,
				PSK.PSK_VERSION
   	            from V_AGENT_INCL_GROUP_ID as SA with (NOLOCK)
	        LEFT OUTER JOIN PATTERN PAT on SA.PATTERN_IDX=PAT.PATTERN_IDX
			LEFT OUTER JOIN CIA_PSK_VERSIONS PSK ON PSK.PROFILE_SERIAL_NO = SA.PROFILE_SERIAL_NO
	         INNER JOIN V_GROUPS G ON SA.GROUP_ID=G.ID
	        INNER JOIN V_DOMAINS S ON SA.DOMAIN_ID = S.ID
	        INNER JOIN V_SEM_COMPUTER I on SA.COMPUTER_ID=I.COMPUTER_ID
	        LEFT OUTER JOIN GROUP_HI_STATUS GHS ON GHS.GROUP_ID = SA.GROUP_ID
            
                    where  I.DELETED = 0 and   (DATEADD(second, SA.LAST_UPDATE_TIME/1000, '19700101') >= cast({ts'2019-06-11 16:52:02'} as datetime) OR SA.LAST_UPDATE_TIME = 0)  AND ((GHS.GROUP_ID IS NOT NULL) ) AND SA.R_OS_TYPE !=0   and SA.DELETED='0' and (SA.IS_NPVDI_CLIENT = 0 OR SA.STATUS = 1)

	               order by  LAST_UPDATE_TIME desc , SA.AGENT_ID DESC) UNION ALL (select TOP 20 I.COMPUTER_ID as Computer_Idx, SA.AGENT_TYPE , SA.R_OS_TYPE, I.HARDWARE_KEY as HARDWARE_KEY,
                           I.COMPUTER_NAME as Computer, I.IP_ADDR1_TEXT as IP_Address, SA.PROFILE_SERIAL_NO,
                           convert(varchar,DATEADD(second, SA.LAST_UPDATE_TIME/1000, '19700101'),120) as LastCheckinTime1,
    	               left( convert (varchar, PAT.patterndate, 120), 10 ) as patterndate1, PAT.sequence, PAT.revision, PAT.version,
                           SA.AGENT_VERSION as ProductVersion,case when SA.LAST_SCAN_TIME >= SA.CREATION_TIME THEN SA.LAST_SCAN_TIME ELSE 0 end AS LAST_SCAN_TIME ,
      		       SA.REBOOT_REQUIRED, SA.REBOOT_REASON, G.NAME as Clientgroup, S.NAME as Servergroup, I.CURRENT_LOGIN_USER as Clientuser, SA.AP_ONOFF as APOnOff,
      		       SA.PTP_ONOFF as PTPOnOff,SA.DA_ONOFF as DAOnOff,(case when (PEP_ONOFF IS NULL) then 127 else PEP_ONOFF END) as PEP_ONOFF,SA.CIDS_DRV_ONOFF as IPSOnOff,SA.CIDS_BROWSER_IE_ONOFF as IEOnOff,
      		       SA.CIDS_BROWSER_FF_ONOFF as FFOnOff,SA.CIDS_SILENT_MODE,SA.INFECTED as Infected, SA.FIREWALL_ONOFF, SA.DEPLOY_STATUS, SA.EDR_STATUS, SA.ATP_SERVER, SA.ELAM_ONOFF,
      		       POWERERASER_PENDING=0,SUM_INFECTED=0,
    								 G.ID as GROUP_ID, SA.LAST_UPDATE_TIME, SA.AGENT_ID, SA.LB_ONOFF,
                                     (SELECT MAX(PAT1.VERSION) FROM SEM_CONTENT C INNER JOIN PATTERN PAT1 ON C.PATTERN_IDX=PAT1.PATTERN_IDX WHERE C.AGENT_ID=SA.AGENT_ID AND C.DELETED=0 AND PAT1.DELETED=0 AND PAT1.PATTERN_TYPE = 'ADVML_AV') AS ADVML_VERSION,
                    SA.NTR_STATUS,
					PSK.PSK_VERSION
       	            from V_AGENT_INCL_GROUP_ID as SA with (NOLOCK)
    	        LEFT OUTER JOIN PATTERN PAT on SA.PATTERN_IDX=PAT.PATTERN_IDX
				LEFT OUTER JOIN CIA_PSK_VERSIONS PSK ON PSK.PROFILE_SERIAL_NO = SA.PROFILE_SERIAL_NO
    	 INNER JOIN V_GROUPS G ON SA.GROUP_ID=G.ID
	        INNER JOIN V_DOMAINS S ON SA.DOMAIN_ID = S.ID
    	        INNER JOIN V_SEM_COMPUTER I on SA.COMPUTER_ID=I.COMPUTER_ID
    	        LEFT OUTER JOIN GROUP_HI_STATUS GHS ON GHS.GROUP_ID = SA.GROUP_ID
    	
                        where SA.R_OS_TYPE=0 AND  I.DELETED = 0 and   (DATEADD(second, SA.LAST_UPDATE_TIME/1000, '19700101') >= cast({ts'2019-06-11 16:52:02'} as datetime) OR SA.LAST_UPDATE_TIME = 0)  AND ((GHS.GROUP_ID IS NOT NULL))  and SA.DELETED='0' and (SA.IS_NPVDI_CLIENT = 0 OR SA.STATUS = 1)

    	               order by  LAST_UPDATE_TIME desc , SA.AGENT_ID DESC)) as SA order by  LAST_UPDATE_TIME desc , SA.AGENT_ID DESC<br>EXCEPTION: com_exception: <b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][SQL Server Native Client 11.0][SQL Server]All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. in D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php:584
Stack trace:
#0 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(584): com->Execute(' select TOP 20 ...', -1)
#1 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(340): ado_doPreparedStatement(' select TOP 20 ...', Object(com))
#2 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\connectdb.php(246): ado_query('select TOP 20 *...', Object(com), false)
#3 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Inventory\inventorylist.php(816): sav_query('select TOP 20 *...', Object(com))
#4 {MAIN.EN_US}Error message: <b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][SQL Server Native Client 11.0][SQL Server]All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.<br>Error code: -2147352567<br>File and line: D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(584)<br>Trace: #0 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(584): com->Execute(' select TOP 20 ...', -1)
#1 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\ado.php(340): ado_doPreparedStatement(' select TOP 20 ...', Object(com))
#2 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Php\Include\Common\connectdb.php(246): ado_query('select TOP 20 *...', Object(com), false)
#3 D:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\Inetpub\Reporting\Inventory\inventorylist.php(816): sav_query('select TOP 20 *...', Object(com))
#4 {MAIN.EN_US}<br>

 

Cause

Error is due to an improperly constructed SQL query

Resolution

This issue is fixed in Symantec Endpoint Protection 14.2 RU2. For information on how to obtain the latest build of Symantec Endpoint Protection, see Download the latest version of Symantec software here.