2015-02-25 15:44:47.084 THREAD 1 SEVERE: ================== Server Environment =================== 2015-02-25 15:44:47.100 THREAD 1 SEVERE: os.name = Windows Server 2008 R2 2015-02-25 15:44:47.100 THREAD 1 SEVERE: os.version = 6.1 2015-02-25 15:44:47.100 THREAD 1 SEVERE: os.arch = x64 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.version = 1.7.0_55 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.vendor = Oracle Corporation 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.vm.name = Java HotSpot(TM) Client VM 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.vm.version = 24.55-b03 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.home = C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\jre 2015-02-25 15:44:47.100 THREAD 1 SEVERE: catalina.home = C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\tomcat 2015-02-25 15:44:47.100 THREAD 1 SEVERE: java.user = null 2015-02-25 15:44:47.100 THREAD 1 SEVERE: user.language = en 2015-02-25 15:44:47.100 THREAD 1 SEVERE: user.country = US 2015-02-25 15:44:47.100 THREAD 1 SEVERE: scm.server.version = 12.1.5337.5000 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> Main>> Checking upgrade arguments... Command-line argument length is: 1 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> Main>> Upgrade args length = 1 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> Main>> Upgrade args = SQL 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> retrieveAdminInfo>> Waited for 1 seconds to get info from stdin. 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> retrieveAdminInfo>> Get string from stdin = username=sa 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> retrieveAdminInfo>> adminUserID = sa 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> retrieveAdminInfo>> Password is received. 2015-02-25 15:44:47.100 THREAD 1 INFO: Get string from stdin:END_INPUT_FROM_STDIN 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> Main>> Unable to retrieve the admin password. 2015-02-25 15:44:47.100 THREAD 1 INFO: Main> Main>> Server Home = C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\tomcat 2015-02-25 15:44:53.037 THREAD 1 INFO: Main> Main>> Database server is MSSQL. Testing DB connection... Current time = Wed Feb 25 15:44:53 EET 2015 2015-02-25 15:44:53.037 THREAD 1 INFO: DatabaseUtilities> testDBConnection>> Testing DB connection using 'DefaultDatabaseConnection' method. Parameters used (needDateFormat: false, isRetryUntilDBUp: false, defaultConnectionMaxRetry: 20) 2015-02-25 15:44:53.272 THREAD 1 INFO: Main> Main>> DB(MSSQL) test connection succeeded. Continuing with upgrade process...Current time = Wed Feb 25 15:44:53 EET 2015 2015-02-25 15:44:53.522 THREAD 1 INFO: Has valid SAV license 2015-02-25 15:44:53.522 THREAD 1 INFO: Info>> No SNAC license file in C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\tomcat\etc\license 2015-02-25 15:44:53.819 THREAD 1 INFO: Main> executePreLaunchSteps>> Config schema format is getting upgraded from (schema version from db): 12.1.4.0 2015-02-25 15:44:54.866 THREAD 1 INFO: Saving disaster recovery data to: C:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\tomcat\..\Server Private Key Backup\recovery_2015-02-25-15-44-54.zip 2015-02-25 15:44:54.944 THREAD 1 INFO: Main> executePreLaunchSteps>> DbVersion: 12.1.4.0 schemaVersionFromDB: 12.1.4.0 isSpm51Mr7: false hasOtherOSAgents: false dbcompanySize: 3 dbConnected: true 2015-02-25 15:44:54.944 THREAD 1 INFO: Main> checkUpgradeFeasibility>> Checking for upgrade feasibility, current schema version = 12.1.5.4, schema version from db = 12.1.4.0 2015-02-25 15:44:54.944 THREAD 1 INFO: VersionToBeSupported: 12.1.4.0 2015-02-25 15:44:54.944 THREAD 1 INFO: SchemaVersion>> isSupported> versionToBeSupported '12.1.4.0 is supported for this release 2015-02-25 15:44:55.006 THREAD 1 INFO: Main> displayOnlineServerListIfAny>> Checking online servers. siteId = F62432B40A64047C01AE5CD8CF8BC076 2015-02-25 15:44:55.022 THREAD 1 INFO: SELECT CONTENT FROM SYSTEM_STATE WHERE OWNER = '386CFBE50A64047901BA47EFC89E1816' 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : Checking onlist status for server CLBSEPMNGSRV01 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : Not checking status. offline status detected 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : [name=CLBSEPMNGSRV01,id=386CFBE50A64047901BA47EFC89E1816] is offline 2015-02-25 15:44:55.037 THREAD 1 INFO: SELECT CONTENT FROM SYSTEM_STATE WHERE OWNER = '127D4FB30A64047A012938D7CE74E72D' 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : Checking onlist status for server CLBSEPMNGSRV02 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : Not checking status. offline status detected 2015-02-25 15:44:55.037 THREAD 1 INFO: SYSOUT : [name=CLBSEPMNGSRV02,id=127D4FB30A64047A012938D7CE74E72D] is offline 2015-02-25 15:44:55.037 THREAD 1 INFO: Main> displayOnlineServerListIfAny>> Checking online servers. Exclude local server id = 127D4FB30A64047A012938D7CE74E72D 2015-02-25 15:44:55.037 THREAD 1 INFO: Main> displayOnlineServerListIfAny>> Online servers = [] 2015-02-25 15:44:55.037 THREAD 1 INFO: Main> launchMainFrame>> Launching the UI. 2015-02-25 15:44:55.584 THREAD 1 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> isWinAuth = false 2015-02-25 15:44:55.584 THREAD 1 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> Trying jdbcURL = jdbc:jtds:sqlserver://10.100.4.220:1433/sem5 2015-02-25 15:44:55.959 THREAD 1 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> Created DB connection. 2015-02-25 15:44:56.069 THREAD 1 INFO: ServerConfigurationRemediator >> needChooseLaunchServicePort >> ServerVersion.FERRARI_VERSION=12.1.4426.5000 2015-02-25 15:44:56.069 THREAD 1 INFO: ServerConfigurationRemediator >> needChooseLaunchServicePort >> needChooseLaunchServicePort=true serverVersionPreUpgrade=12.1.4013.4013 2015-02-25 15:44:56.131 THREAD 1 INFO: ServerConfigurationRemediator >> needChooseLaunchServicePort >> ServerVersion.FERRARI_VERSION=12.1.4426.5000 2015-02-25 15:44:56.131 THREAD 1 INFO: ServerConfigurationRemediator >> needChooseLaunchServicePort >> needChooseLaunchServicePort=true serverVersionPreUpgrade=12.1.4013.4013 2015-02-25 15:44:56.241 THREAD 1 INFO: isLicenseInstallationValid: sep.slf =true OD file=true 2015-02-25 15:44:56.256 THREAD 1 INFO: MainFrame >> needShowUpgradeDBTasksConfigPanel >> skipping panel because preUpgradeVersion=12.1.4013.4013 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.server.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.http.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.webserver.http.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.webserver.https.port is not found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.shutdown.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.console.reporting.https.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.webservices.port is found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.iis.http.port is not found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.iis.https.port is not found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.iisproxy.http.port is not found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.iisproxy.https.port is not found in conf.properties 2015-02-25 15:45:26.866 THREAD 15 INFO: scm.rmmwebservices.port is found in conf.properties 2015-02-25 15:45:32.726 THREAD 15 INFO: Run LiveUpdate during upgrade=false 2015-02-25 15:45:32.991 THREAD 19 WARNING: Upgrade> doUpgrade>> Upgrade process started @ : 0 2015-02-25 15:45:33.007 THREAD 19 INFO: UpgradeStep >> checkUpgradeStatus. 2015-02-25 15:45:34.866 THREAD 19 WARNING: Upgrade> doUpgrade>> Stopping SemSrv... 2015-02-25 15:45:43.163 THREAD 19 INFO: The Symantec Endpoint Protection Manager service is not started.More help is available by typing NET HELPMSG 3521. 2015-02-25 15:45:43.163 THREAD 19 INFO: SemServiceManager> serviceControl>> Executed command - stop semsrv, process return value = 2 2015-02-25 15:45:43.163 THREAD 19 INFO: SemServiceManager> waitForServiceTermination>> Waiting for service termination: semsrv 2015-02-25 15:45:43.163 THREAD 19 INFO: SemServiceManager> getServiceStatus>> Retrieve status for service semsrv 2015-02-25 15:45:44.632 THREAD 19 INFO: SemServiceManager> getServiceStatus>> The status for semsrv' service is 1 2015-02-25 15:45:44.648 THREAD 19 INFO: SemServiceManager> waitForServiceTermination>> Service is stopped. 2015-02-25 15:45:44.773 THREAD 19 INFO: The Symantec Endpoint Protection Manager Webserver service is not started.More help is available by typing NET HELPMSG 3521. 2015-02-25 15:45:44.773 THREAD 19 INFO: SemServiceManager> serviceControl>> Executed command - stop semwebsrv, process return value = 2 2015-02-25 15:45:44.898 THREAD 19 INFO: The service name is invalid.More help is available by typing NET HELPMSG 2185. 2015-02-25 15:45:44.898 THREAD 19 INFO: SemServiceManager> serviceControl>> Executed command - stop semlaunchsrv, process return value = 2 2015-02-25 15:45:44.898 THREAD 19 INFO: SemServiceManager> waitForServiceTermination>> Waiting for service termination: semlaunchsrv 2015-02-25 15:45:44.898 THREAD 19 INFO: SemServiceManager> getServiceStatus>> Retrieve status for service semlaunchsrv 2015-02-25 15:45:45.023 THREAD 19 INFO: SemServiceManager> getServiceStatus>> Error code from sc query: 1060 2015-02-25 15:45:45.023 THREAD 19 INFO: SemServiceManager> waitForServiceTermination>> Service is stopped. 2015-02-25 15:45:45.023 THREAD 19 WARNING: Upgrade> doUpgrade>> Stopping SemSrv done! 2015-02-25 15:45:45.038 THREAD 19 INFO: DatabaseUtilities> testDBConnection>> Testing DB connection using 'DefaultDatabaseConnection' method. Parameters used (needDateFormat: false, isRetryUntilDBUp: false, defaultConnectionMaxRetry: 20) 2015-02-25 15:45:45.273 THREAD 19 INFO: isLicenseInstallationValid: sep.slf =true OD file=true 2015-02-25 15:45:45.788 THREAD 19 INFO: UpgradeDBUtil> createODBC>> Creating ODBC... [dbServer: 10.100.4.220, dbDomain: null, dbUser: sem5, dbPwd: ***, dbName: sem5, dbServerPort: 1433, dbVendor: MSSQLServer, SPMport: 8443, remotePort: 9090] 2015-02-25 15:45:48.163 THREAD 19 INFO: UpgradeDBUtil> createODBC>> Creating ODBC done! 2015-02-25 15:45:48.163 THREAD 19 INFO: Upgrade> grantSEMUserSQLDBOwnerPrivilege>> Granting 'sem5' 'db_owner' privilege... 2015-02-25 15:45:48.163 THREAD 19 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> isWinAuth = false 2015-02-25 15:45:48.163 THREAD 19 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> Trying jdbcURL = jdbc:jtds:sqlserver://10.100.4.220:1433/sem5 2015-02-25 15:45:48.273 THREAD 19 INFO: DatabaseUtilities> getDatabaseConnectionWithNTLMv2Retry>> Created DB connection. 2015-02-25 15:45:48.288 THREAD 19 INFO: Upgrade> grantSEMUserSQLDBOwnerPrivilege>> Granting 'sem5' db_owner privilege done! 2015-02-25 15:45:48.632 THREAD 19 INFO: UpgradeStep >> checkUpgradeStatus. 2015-02-25 15:45:48.632 THREAD 19 INFO: UpgradeStep >> checkUpgradeStatus. 2015-02-25 15:45:48.632 THREAD 19 INFO: Set the config manager cache threshold to 500! 2015-02-25 15:45:51.179 THREAD 19 INFO: Upgrade> setUnrestrictedMaxLogFileSize>> Max Log File Size from DB: 2097152 2015-02-25 15:45:51.179 THREAD 19 INFO: Upgrade> setUnrestrictedMaxLogFileSize>> Max Log File Size is 2TB, No need to change the Max Trasaction Log Size to Unrestircted size. 2015-02-25 15:45:51.320 THREAD 19 WARNING: Upgrade> doUpgrade>> Upgrading database schema... [formatVersion = 12.1.4.0, dbVersion = 12.1.4.0] 2015-02-25 15:45:51.351 THREAD 19 INFO: Process SQL script file:MSSQLServerTeslaToLightCycle.sql 2015-02-25 15:45:51.351 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@584adc 2015-02-25 15:45:51.367 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : DROP VIEW V_AGENT_INCL_GROUP_ID 2015-02-25 15:45:51.367 THREAD 19 INFO: DROP VIEW V_AGENT_INCL_GROUP_ID 2015-02-25 15:45:51.382 THREAD 19 INFO: CREATE VIEW V_AGENT_INCL_GROUP_ID as SELECT AGENT_ID, AGENT_TYPE, R_OS_TYPE, COMPUTER_ID, DOMAIN_ID, GROUP_ID, AGENT_VERSION, PROFILE_VERSION, PROFILE_SERIAL_NO, PROFILE_CHECKSUM, IDS_VERSION, IDS_SERIAL_NO, IDS_CHECKSUM, HI_STATUS, HI_REASONCODE, HI_REASONDESC, CREATION_TIME, STATUS, LAST_UPDATE_TIME, LAST_SERVER_ID, LAST_SITE_ID, ATTRIBUTE_EXTENSION, FULL_NAME, EMAIL, JOB_TITLE, DEPARTMENT, EMPLOYEE_NUMBER, EMPLOYMENT_STATUS, OFFICE_PHONE, MOBILE_PHONE, HOME_PHONE, USN, TIME_STAMP, DELETED, PATTERN_IDX, AP_ONOFF, INFECTED, WORSTINFECTION_IDX, LAST_SCAN_TIME, LAST_VIRUS_TIME, CONTENT_UPDATE, AVENGINE_ONOFF, TAMPER_ONOFF, MAJOR_VERSION, MINOR_VERSION, REBOOT_REQUIRED, REBOOT_REASON, LICENSE_STATUS, LICENSE_EXPIRY, TIMEZONE, FIREWALL_ONOFF, FREE_MEM, FREE_DISK, LAST_DOWNLOAD_TIME, CURRENT_CLIENT_ID, LICENSE_ID, IS_GRACE, SNAC_LICENSE_ID, PTP_ONOFF, LAST_HEURISTIC_THREAT_TIME, BASH_STATUS, DA_ONOFF, CIDS_DRV_ONOFF, CIDS_SILENT_MODE, CIDS_DRV_MULF_CODE, CIDS_BROWSER_IE_ONOFF, CIDS_BROWSER_FF_ONOFF, CIDS_ENGINE_VERSION, CIDS_DEFSET_VERSION, DEPLOY_STATUS, DEPLOY_MSG, DEPLOY_PRE_VER, DEPLOY_TARGET_VER, DEPLOY_RUNNING_VER, DEPLOY_TIMESTAMP, OS_BIT_TYPE, ELAM_ONOFF, OSELAM_STATUS, VSIC_STATUS, IS_NPVDI_CLIENT, SVA_ID, LAST_CONNECTED_IP_ADDR FROM SEM_AGENT with (NOLOCK) 2015-02-25 15:45:51.413 THREAD 19 INFO: Process SQL script file:MSSQLServer12RU4MP1ToFerrariPCC.sql 2015-02-25 15:45:51.413 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@ab20dc 2015-02-25 15:45:51.413 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : DROP TABLE TASKSTATE 2015-02-25 15:45:51.413 THREAD 19 INFO: DROP TABLE TASKSTATE 2015-02-25 15:45:51.429 THREAD 19 INFO: CREATE TABLE TASKSTATE ( IDX CHAR(32) NOT NULL, SERVER_ID CHAR(32) NOT NULL, TASKTYPE NVARCHAR(255) NOT NULL, STATE INT NOT NULL, STARTTIME BIGINT NOT NULL, LASTUPDATETIME BIGINT NOT NULL, LASTRUNGMT BIGINT NOT NULL, DETAIL NVARCHAR(255) NULL ) 2015-02-25 15:45:51.429 THREAD 19 INFO: DROP TABLE ENUM_MAP 2015-02-25 15:45:51.429 THREAD 19 INFO: CREATE TABLE ENUM_MAP ( NAMESPACE NVARCHAR(255) NULL, VALUE INT NULL, NAME NVARCHAR(255) NULL ) 2015-02-25 15:45:51.429 THREAD 19 INFO: DROP VIEW V_TASKSTATE 2015-02-25 15:45:51.429 THREAD 19 INFO: CREATE VIEW V_TASKSTATE as select TS.SERVER_ID, TS.TASKTYPE, TS.STARTTIME, TS.LASTUPDATETIME, TS.LASTRUNGMT, TS.DETAIL, TV.NAME FROM TASKSTATE as TS, ENUM_MAP as TV where TS.TASKTYPE=TV.NAMESPACE and TS.STATE=TV.VALUE 2015-02-25 15:45:51.429 THREAD 19 INFO: DROP VIEW V_SCANS 2015-02-25 15:45:51.429 THREAD 19 INFO: CREATE VIEW V_SCANS as select * from SCANS where DELETED=0 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('LiveUpdate', 0, 'LIVEUPDATE_SUCCEEDED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('LiveUpdate', 1, 'LIVEUPDATE_FAILED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('LiveUpdate', 2, 'LIVEUPDATE_STARTED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('PolicyChange', 0, 'POLICYCHANGE_SUCCEEDED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('PolicyChange', 1, 'POLICYCHANGE_FAILED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('PolicyChange', 2, 'POLICYCHANGE_REQUESTRECEIVED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('PolicyChange', 3, 'POLICYCHANGE_PUBLISHBEGIN') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('PolicyChange', 4, 'POLICYCHANGE_PUBLISHINPROGRESS') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 0, 'REPLICATION_SUCCESS') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 1, 'REPLICATION_FAILED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 2, 'REPLICATION_CHANGE_RECEIVED') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 3, 'REPLICATION_REMOTEREPLICATE') 2015-02-25 15:45:51.445 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 4, 'REPLICATION_NEEDTO_SYNC_SERVERTIME') 2015-02-25 15:45:51.460 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 5, 'REPLICATION_RESOLVING_NAME_CHANGE') 2015-02-25 15:45:51.460 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 6, 'REPLICATION_SKIP') 2015-02-25 15:45:51.460 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 7, 'REPLICATION_START') 2015-02-25 15:45:51.460 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 8, 'REPLICATION_CHANGE_RECEIVING') 2015-02-25 15:45:51.460 THREAD 19 INFO: INSERT INTO ENUM_MAP (NAMESPACE, VALUE, NAME) VALUES ('Replication', 9, 'REPLICATION_DELTAMERGE') 2015-02-25 15:45:51.460 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'SEM_COMPUTER' AND C.NAME='KERNEL') BEGIN ALTER TABLE SEM_COMPUTER ADD KERNEL NVARCHAR(256) NULL END 2015-02-25 15:45:51.476 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'SEM_COMPUTER' AND C.NAME='UUID') BEGIN ALTER TABLE SEM_COMPUTER ADD UUID varchar(128) NULL END 2015-02-25 15:45:51.492 THREAD 19 INFO: /* defect 3508795, adjusting datatype and length of the column to match definition of fresh install*/ IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'ALERTS' AND C.NAME='SCAN_GUID_IDX') BEGIN ALTER TABLE ALERTS ADD SCAN_GUID_IDX char(32) NULL END 2015-02-25 15:45:51.507 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'HPP_ALERTS' AND C.NAME='SHOULD_REMEDIATE') BEGIN ALTER TABLE HPP_ALERTS ADD SHOULD_REMEDIATE tinyint NULL END 2015-02-25 15:45:51.523 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'SCANREPORT' AND C.NAME='SCAN_TYPE') BEGIN ALTER TABLE SCANREPORT ADD SCAN_TYPE VARCHAR(64) NULL END 2015-02-25 15:45:51.523 THREAD 19 INFO: IF NOT EXISTS (SELECT PARAMETER FROM GUIPARMS WHERE PARAMETER = 'latest_smr_defs' ) INSERT INTO GUIPARMS (GUIPARMS_IDX, PARAMETER, VALUE) VALUES (75, 'latest_smr_defs', '') 2015-02-25 15:45:51.538 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_TRAFFIC_LOG_1' AND C.NAME='NESTED_PROTOCOL_TYPE') BEGIN ALTER TABLE AGENT_TRAFFIC_LOG_1 ADD NESTED_PROTOCOL_TYPE CHAR(32) NULL DEFAULT NULL END 2015-02-25 15:45:51.538 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_TRAFFIC_LOG_1' AND C.NAME='NESTED_PROTOCOL_SUB_TYPE') BEGIN ALTER TABLE AGENT_TRAFFIC_LOG_1 ADD NESTED_PROTOCOL_SUB_TYPE CHAR(32) NULL DEFAULT NULL END 2015-02-25 15:45:51.554 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_TRAFFIC_LOG_2' AND C.NAME='NESTED_PROTOCOL_TYPE') BEGIN ALTER TABLE AGENT_TRAFFIC_LOG_2 ADD NESTED_PROTOCOL_TYPE CHAR(32) NULL DEFAULT NULL END 2015-02-25 15:45:51.570 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_TRAFFIC_LOG_2' AND C.NAME='NESTED_PROTOCOL_SUB_TYPE') BEGIN ALTER TABLE AGENT_TRAFFIC_LOG_2 ADD NESTED_PROTOCOL_SUB_TYPE CHAR(32) NULL DEFAULT NULL END 2015-02-25 15:45:51.570 THREAD 19 INFO: DROP VIEW V_AGENT_INCL_RISK_INFO 2015-02-25 15:45:51.570 THREAD 19 INFO: CREATE VIEW V_AGENT_INCL_RISK_INFO AS SELECT *, POWERERASER_PENDING.ALERTS_COUNT AS POWERERASER_PENDING_ALERTS FROM SEM_AGENT SA with (NOLOCK) LEFT JOIN (SELECT COUNT(A.ALERT_IDX) AS ALERTS_COUNT,A.COMPUTER_IDX FROM ALERTS A with (NOLOCK) WHERE A.SOURCE='PowerEraser' AND A.ACTUALACTION_IDX=14 AND A.DELETED=0 AND A.Mother_Idx = '' AND (DATEDIFF(day,A.ALERTDATETIME, GETDATE()) < 30) GROUP BY A.COMPUTER_IDX) AS POWERERASER_PENDING ON POWERERASER_PENDING.COMPUTER_IDX = SA.COMPUTER_ID 2015-02-25 15:45:51.585 THREAD 19 INFO: drop view V_AGENT_TRAFFIC_LOG 2015-02-25 15:45:51.585 THREAD 19 INFO: CREATE VIEW V_AGENT_TRAFFIC_LOG as SELECT *, CASE WHEN LOCAL_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(LOCAL_HOST_IPV6))) > 0 THEN substring(LOCAL_HOST_IPV6, 1, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 5, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 9, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 13, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 17, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 21, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 25,4 ) + ':' + substring(LOCAL_HOST_IPV6, 29, 4 ) ELSE CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end & 0xFF as VARCHAR) END as LOCAL_HOST_IP_TEXT , CASE WHEN REMOTE_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(REMOTE_HOST_IPV6))) > 0 THEN substring(REMOTE_HOST_IPV6, 1, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 5, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 9, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 13, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 17, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 21, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 25,4 ) + ':' + substring(REMOTE_HOST_IPV6, 29, 4 ) ELSE CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end & 0xFF as VARCHAR) END as REMOTE_HOST_IP_TEXT FROM AGENT_TRAFFIC_LOG_1 UNION ALL SELECT *, CASE WHEN LOCAL_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(LOCAL_HOST_IPV6))) > 0 THEN substring(LOCAL_HOST_IPV6, 1, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 5, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 9, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 13, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 17, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 21, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 25,4 ) + ':' + substring(LOCAL_HOST_IPV6, 29, 4 ) ELSE CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end & 0xFF as VARCHAR) END as LOCAL_HOST_IP_TEXT , CASE WHEN REMOTE_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(REMOTE_HOST_IPV6))) > 0 THEN substring(REMOTE_HOST_IPV6, 1, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 5, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 9, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 13, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 17, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 21, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 25,4 ) + ':' + substring(REMOTE_HOST_IPV6, 29, 4 ) ELSE CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end & 0xFF as VARCHAR) END as REMOTE_HOST_IP_TEXT FROM AGENT_TRAFFIC_LOG_2 2015-02-25 15:45:51.601 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'COMPLIANCE_REPORT' AND C.NAME='LOCAL_IP_ADDRESS') BEGIN ALTER TABLE COMPLIANCE_REPORT ADD LOCAL_IP_ADDRESS NVARCHAR(255) NULL END 2015-02-25 15:45:51.617 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'FIREWALL_REPORT' AND C.NAME='LOCAL_IP_ADDRESS') BEGIN ALTER TABLE FIREWALL_REPORT ADD LOCAL_IP_ADDRESS NVARCHAR(255) NULL END 2015-02-25 15:45:51.617 THREAD 19 INFO: IF NOT EXISTS (SELECT ACTUALACTION FROM ACTUALACTION WHERE ACTUALACTION_IDX = 25 ) INSERT INTO ACTUALACTION (ACTUALACTION_IDX, ACTUALACTION) VALUES (25, 'RepairFailedPowerEraser2') 2015-02-25 15:45:51.632 THREAD 19 INFO: IF EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'SEM_COMPUTER' AND C.NAME='LAST_CONNECTED_IP_ADDR') BEGIN ALTER TABLE SEM_COMPUTER drop column LAST_CONNECTED_IP_ADDR END 2015-02-25 15:45:51.632 THREAD 19 INFO: DROP VIEW V_SEM_COMPUTER 2015-02-25 15:45:51.648 THREAD 19 INFO: CREATE VIEW V_SEM_COMPUTER AS SELECT *, CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end & 0xFF as VARCHAR) as DNS_SERVER1_TEXT, CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end & 0xFF as VARCHAR) as DNS_SERVER2_TEXT, CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end & 0xFF as VARCHAR) as WINS_SERVER1_TEXT, CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end & 0xFF as VARCHAR) as WINS_SERVER2_TEXT, CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end & 0xFF as VARCHAR) as DHCP_SERVER_TEXT, CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end & 0xFF as VARCHAR) as IP_ADDR1_TEXT, CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end & 0xFF as VARCHAR) as GATEWAY1_TEXT, CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end & 0xFF as VARCHAR) as SUBNET_MASK1_TEXT, CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end & 0xFF as VARCHAR) as IP_ADDR2_TEXT, CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end & 0xFF as VARCHAR) as GATEWAY2_TEXT, CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end & 0xFF as VARCHAR) as SUBNET_MASK2_TEXT, CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end & 0xFF as VARCHAR) as IP_ADDR3_TEXT, CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end & 0xFF as VARCHAR) as GATEWAY3_TEXT, CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end & 0xFF as VARCHAR) as SUBNET_MASK3_TEXT, CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end & 0xFF as VARCHAR) as IP_ADDR4_TEXT, CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end & 0xFF as VARCHAR) as GATEWAY4_TEXT, CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end & 0xFF as VARCHAR) as SUBNET_MASK4_TEXT FROM SEM_COMPUTER WHERE DELETED=0 2015-02-25 15:45:51.648 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Red Hat Enterprise Linux Server') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Red Hat Enterprise Linux Server', 'Red Hat', 'Server', 'Red Hat', 'Server', 6, 3, '6.3', 3,0,1,0, 'LINUX_REDHAT_SERVER') END 2015-02-25 15:45:51.663 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Fedora') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Fedora', 'Fedora', '', 'Fedora', '', 17, 0, '17.0', 3,0,1,0, 'LINUX_FEDORA') END 2015-02-25 15:45:51.663 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'SUSE Linux Enterprise Server') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('SUSE Linux Enterprise Server', 'SUSE', 'Server', 'SUSE', 'Server', 11, 2, '11.2', 3,0,1,0, 'LINUX_SUSE_SERVER') END 2015-02-25 15:45:51.663 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'SUSE Linux Enterprise Desktop') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('SUSE Linux Enterprise Desktop', 'SUSE', 'Desktop', 'SUSE', 'Desktop', 11, 2, '11.2', 3,0,1,0, 'LINUX_SUSE_DESKTOP') END 2015-02-25 15:45:51.663 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Ubuntu Server') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Ubuntu Server', 'Ubuntu', 'Server', 'Ubuntu', 'Server', 12, 4, '12.4', 3,0,1,0, 'LINUX_UBUNTU_SERVER') END 2015-02-25 15:45:51.663 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Ubuntu Desktop') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Ubuntu Desktop', 'Ubuntu', 'Desktop', 'Ubuntu', 'Desktop', 12, 4, '12.4', 3,0,1,0, 'LINUX_UBUNTU_DESKTOP') END 2015-02-25 15:45:51.679 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Debian') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Debian', 'Debian', '', 'Debian', '', 6, 0, '6.0', 3,0,1,0, 'LINUX_DEBIAN') END 2015-02-25 15:45:51.679 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'Oracle Linux Server') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('Oracle Linux Server', 'Oracle', 'Server', 'Oracle', 'Server', 5, 8, '5.8', 3,0,1,0, 'LINUX_ORACLE_SERVER') END 2015-02-25 15:45:51.679 THREAD 19 INFO: IF NOT EXISTS (SELECT OS_NAME FROM SEM_OS_INFO WHERE OPERATING_SYSTEM = 'CentOS') BEGIN INSERT INTO SEM_OS_INFO (OPERATING_SYSTEM, OS_NAME, OS_TYPE, SPC_OS_NAME, SPC_OS_TYPE, OS_MAJOR, OS_MINOR, SPC_OS_VERSION, OS_FAMILY, DELETED, USN, TIME_STAMP, I18N_KEY) VALUES ('CentOS', 'CentOS', '', 'CentOS', '', 2, 6, '2.6', 3,0,1,0, 'LINUX_CENTOS') END 2015-02-25 15:45:51.679 THREAD 19 INFO: IF NOT EXISTS(SELECT NAME FROM SYSOBJECTS SO JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID WHERE OBJECT_NAME(SO.PARENT_OBJ) = 'COMPUTER_APPLICATION' AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS WHERE ID = OBJECT_ID('COMPUTER_APPLICATION') AND NAME = 'DELETED')) BEGIN ALTER TABLE COMPUTER_APPLICATION ADD DEFAULT ((0)) FOR DELETED END 2015-02-25 15:45:51.742 THREAD 19 INFO: IF NOT EXISTS(SELECT NAME FROM SYSOBJECTS SO JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID WHERE OBJECT_NAME(SO.PARENT_OBJ) = 'SEM_APPLICATION' AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS WHERE ID = OBJECT_ID('SEM_APPLICATION') AND NAME = 'DELETED')) BEGIN ALTER TABLE SEM_APPLICATION ADD DEFAULT ((0)) FOR DELETED END 2015-02-25 15:45:51.804 THREAD 19 INFO: IF NOT EXISTS(SELECT NAME FROM SYSOBJECTS SO JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID WHERE OBJECT_NAME(SO.PARENT_OBJ) = 'SCANS' AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS WHERE ID = OBJECT_ID('SCANS') AND NAME = 'VSIC_SCAN')) BEGIN ALTER TABLE SCANS ADD DEFAULT ((0)) FOR VSIC_SCAN END 2015-02-25 15:45:51.835 THREAD 19 INFO: IF EXISTS(SELECT 1 from sysindexes si,SYSOBJECTS so where so.name='LAST_CONNECTED_IP_ADDR'and si.id=so.id and si.name LIKE 'PK__HYPERVISOR_VENDO__%') BEGIN DECLARE @SQL VARCHAR(4000) SET @SQL = 'ALTER TABLE HYPERVISOR_VENDOR DROP CONSTRAINT |ConstraintName| ' SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( select TOP 1 si.name from sysindexes si,SYSOBJECTS so where so.name='HYPERVISOR_VENDOR'and si.id=so.id and si.name LIKE 'PK__HYPERVISOR_VENDO__%')) EXEC (@SQL) ALTER TABLE HYPERVISOR_VENDOR ADD CONSTRAINT PK_HYPERVISOR_VENDOR PRIMARY KEY NONCLUSTERED (HYPERVISOR_VENDOR_ID) ON FG_INDEX END 2015-02-25 15:45:51.867 THREAD 19 INFO: IF EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'SEM_COMPUTER' AND C.NAME='LAST_CONNECTED_IP_ADDR') BEGIN ALTER TABLE SEM_COMPUTER drop column LAST_CONNECTED_IP_ADDR END 2015-02-25 15:45:51.867 THREAD 19 INFO: DROP VIEW V_SEM_COMPUTER 2015-02-25 15:45:51.882 THREAD 19 INFO: CREATE VIEW V_SEM_COMPUTER AS SELECT *, CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DNS_SERVER1 < 0 then 0xFFFFFFFF + DNS_SERVER1 else DNS_SERVER1 end & 0xFF as VARCHAR) as DNS_SERVER1_TEXT, CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DNS_SERVER2 < 0 then 0xFFFFFFFF + DNS_SERVER2 else DNS_SERVER2 end & 0xFF as VARCHAR) as DNS_SERVER2_TEXT, CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when WINS_SERVER1 < 0 then 0xFFFFFFFF + WINS_SERVER1 else WINS_SERVER1 end & 0xFF as VARCHAR) as WINS_SERVER1_TEXT, CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when WINS_SERVER2 < 0 then 0xFFFFFFFF + WINS_SERVER2 else WINS_SERVER2 end & 0xFF as VARCHAR) as WINS_SERVER2_TEXT, CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when DHCP_SERVER < 0 then 0xFFFFFFFF + DHCP_SERVER else DHCP_SERVER end & 0xFF as VARCHAR) as DHCP_SERVER_TEXT, CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end & 0xFF as VARCHAR) as IP_ADDR1_TEXT, CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY1 < 0 then 0xFFFFFFFF + GATEWAY1 else GATEWAY1 end & 0xFF as VARCHAR) as GATEWAY1_TEXT, CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK1 < 0 then 0xFFFFFFFF + SUBNET_MASK1 else SUBNET_MASK1 end & 0xFF as VARCHAR) as SUBNET_MASK1_TEXT, CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR2 < 0 then 0xFFFFFFFF + IP_ADDR2 else IP_ADDR2 end & 0xFF as VARCHAR) as IP_ADDR2_TEXT, CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY2 < 0 then 0xFFFFFFFF + GATEWAY2 else GATEWAY2 end & 0xFF as VARCHAR) as GATEWAY2_TEXT, CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK2 < 0 then 0xFFFFFFFF + SUBNET_MASK2 else SUBNET_MASK2 end & 0xFF as VARCHAR) as SUBNET_MASK2_TEXT, CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR3 < 0 then 0xFFFFFFFF + IP_ADDR3 else IP_ADDR3 end & 0xFF as VARCHAR) as IP_ADDR3_TEXT, CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY3 < 0 then 0xFFFFFFFF + GATEWAY3 else GATEWAY3 end & 0xFF as VARCHAR) as GATEWAY3_TEXT, CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK3 < 0 then 0xFFFFFFFF + SUBNET_MASK3 else SUBNET_MASK3 end & 0xFF as VARCHAR) as SUBNET_MASK3_TEXT, CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR4 < 0 then 0xFFFFFFFF + IP_ADDR4 else IP_ADDR4 end & 0xFF as VARCHAR) as IP_ADDR4_TEXT, CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when GATEWAY4 < 0 then 0xFFFFFFFF + GATEWAY4 else GATEWAY4 end & 0xFF as VARCHAR) as GATEWAY4_TEXT, CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when SUBNET_MASK4 < 0 then 0xFFFFFFFF + SUBNET_MASK4 else SUBNET_MASK4 end & 0xFF as VARCHAR) as SUBNET_MASK4_TEXT FROM SEM_COMPUTER WHERE DELETED=0 2015-02-25 15:45:51.882 THREAD 19 INFO: ALTER TABLE SEM_COMPUTER ALTER COLUMN OPERATION_SYSTEM nvarchar(512) 2015-02-25 15:45:51.882 THREAD 19 INFO: ALTER TABLE SEM_SVA_COMPUTER ALTER COLUMN OPERATION_SYSTEM nvarchar(512) 2015-02-25 15:45:51.898 THREAD 19 INFO: DROP VIEW V_AGENT_INCL_GROUP_ID 2015-02-25 15:45:51.898 THREAD 19 INFO: CREATE VIEW V_AGENT_INCL_GROUP_ID as SELECT AGENT_ID, AGENT_TYPE, R_OS_TYPE, COMPUTER_ID, DOMAIN_ID, GROUP_ID, AGENT_VERSION, PROFILE_VERSION, PROFILE_SERIAL_NO, PROFILE_CHECKSUM, IDS_VERSION, IDS_SERIAL_NO, IDS_CHECKSUM, HI_STATUS, HI_REASONCODE, HI_REASONDESC, CREATION_TIME, STATUS, LAST_UPDATE_TIME, LAST_SERVER_ID, LAST_SITE_ID, ATTRIBUTE_EXTENSION, FULL_NAME, EMAIL, JOB_TITLE, DEPARTMENT, EMPLOYEE_NUMBER, EMPLOYMENT_STATUS, OFFICE_PHONE, MOBILE_PHONE, HOME_PHONE, USN, TIME_STAMP, DELETED, PATTERN_IDX, AP_ONOFF, INFECTED, WORSTINFECTION_IDX, LAST_SCAN_TIME, LAST_VIRUS_TIME, CONTENT_UPDATE, AVENGINE_ONOFF, TAMPER_ONOFF, MAJOR_VERSION, MINOR_VERSION, REBOOT_REQUIRED, REBOOT_REASON, LICENSE_STATUS, LICENSE_EXPIRY, TIMEZONE, FIREWALL_ONOFF, FREE_MEM, FREE_DISK, LAST_DOWNLOAD_TIME, CURRENT_CLIENT_ID, LICENSE_ID, IS_GRACE, SNAC_LICENSE_ID, PTP_ONOFF, LAST_HEURISTIC_THREAT_TIME, BASH_STATUS, DA_ONOFF, CIDS_DRV_ONOFF, CIDS_SILENT_MODE, CIDS_DRV_MULF_CODE, CIDS_BROWSER_IE_ONOFF, CIDS_BROWSER_FF_ONOFF, CIDS_ENGINE_VERSION, CIDS_DEFSET_VERSION, DEPLOY_STATUS, DEPLOY_MSG, DEPLOY_PRE_VER, DEPLOY_TARGET_VER, DEPLOY_RUNNING_VER, DEPLOY_TIMESTAMP, OS_BIT_TYPE, ELAM_ONOFF, OSELAM_STATUS, VSIC_STATUS, IS_NPVDI_CLIENT, SVA_ID, LAST_CONNECTED_IP_ADDR FROM SEM_AGENT with (NOLOCK) 2015-02-25 15:45:51.898 THREAD 19 INFO: DROP PROCEDURE SEM_REPORT_NEW_RISK 2015-02-25 15:45:51.898 THREAD 19 INFO: CREATE PROCEDURE SEM_REPORT_NEW_RISK @timebase NVARCHAR(30), @filterDateTimeFrom NVARCHAR(19), @filterDateTimeTo NVARCHAR(19), @filter_5150 NTEXT, /* filter_5150 is part of where clause, it may be longer than 4000 */ @top_num INT AS BEGIN /* For defect 1926711, create this procedure to enhance performace */ /* If record count = 0, the procedure will return one recordset : the record count */ /* If record count > 0, the procedure will return one more recordsets: all or top num detailed records */ DECLARE @rec_count INT DECLARE @sql NVARCHAR(4000) DECLARE @sql_virus_query NVARCHAR(4000) DECLARE @sql_virus_group NVARCHAR(4000) DECLARE @sql_hpp_query NVARCHAR(4000) DECLARE @sql_hpp_group NVARCHAR(4000) DECLARE @top_num_str NVARCHAR(10) /* Define temporary tables */ CREATE TABLE #TEMP_RPT_VIRUS (VIRUSNAME_IDX VARCHAR(32), FIRSTTIME DATETIME) CREATE TABLE #TEMP_RPT_HPP (HPP_APP_IDX VARCHAR(32), FIRSTTIME DATETIME) CREATE TABLE #TEMP_RECORD_COUNT(RECORD_COUNT INT) /* Extract interim result to temporary table */ SET @sql = 'INSERT INTO #TEMP_RPT_VIRUS ' + 'SELECT VIRUSNAME_IDX, MIN(' + @timebase + ')' + 'FROM ALERTS WITH(NOLOCK) ' + 'WHERE SOURCE != ''Heuristic Scan'' and ALERT_IDX in(1, 2) ' + 'GROUP BY VIRUSNAME_IDX ' + 'HAVING MIN(' + @timebase + ') >= cast({ts''' + @filterDateTimeFrom + '''} as datetime) ' + ' AND MIN(' + @timebase + ') <= cast({ts''' + @filterDateTimeTo + '''} as datetime)' EXECUTE sp_executesql @sql SET @sql = 'INSERT INTO #TEMP_RPT_HPP ' + 'SELECT HPP_APP_IDX, MIN(' + @timebase + ') ' + 'FROM ALERTS WITH(NOLOCK) ' + 'WHERE SOURCE = ''Heuristic Scan'' and ALERT_IDX in(1, 2) ' + 'GROUP BY HPP_APP_IDX ' + 'HAVING MIN(' + @timebase + ') >= cast({ts''' + @filterDateTimeFrom + '''} as datetime) ' + ' AND MIN(' + @timebase + ') <= cast({ts''' + @filterDateTimeTo + '''} as datetime)' EXECUTE sp_executesql @sql /* COLLATE DATABASE_DEFAULT */ /* Since type of filter_5150 is NTEXT, all parts of main sql should be defined respectively, and finally concatenated when calling EXEC */ SET @sql_virus_query = 'SELECT A.SERVERGROUP_IDX, A.CLIENTGROUP_IDX, A.USER_NAME, ' + 'A.PARENTSERVER_IDX, A.SOURCE, I.COMPUTER_NAME, ' + 'V.VIRUSNAME, V.CATEGORY, V.TYPE2, V.TYPE, V.VIRUSNAME as VirusHelp, ' + 'convert(char,A.' + @timebase + ', 120) as firsttime, ' + 'left(convert(char, V.DISCOVERED, 120), 10) AS Discovered, ' + 'MIN(A.IDX) AS Alert_Idx ' + 'FROM #TEMP_RPT_VIRUS SUB ' + 'INNER JOIN ALERTS A with (NOLOCK) ON SUB.VIRUSNAME_IDX COLLATE DATABASE_DEFAULT = A.VIRUSNAME_IDX COLLATE DATABASE_DEFAULT AND SUB.firsttime = A.' + @timebase + ' ' + 'INNER JOIN VIRUS V with (NOLOCK) ON V.VIRUSNAME_IDX = A.VIRUSNAME_IDX ' + 'INNER JOIN SEM_COMPUTER I with (NOLOCK) on A.COMPUTER_IDX = I.COMPUTER_ID ' + 'INNER JOIN V_AGENT_INCL_GROUP_ID SA with (NOLOCK) on SA.COMPUTER_ID = I.COMPUTER_ID ' + 'INNER JOIN V_GROUPS G with (NOLOCK) on G.ID = SA.GROUP_ID ' + 'INNER JOIN V_DOMAINS S with (NOLOCK) on S.ID = SA.DOMAIN_ID ' IF datalength(@filter_5150) > 0 SET @sql_virus_query = @sql_virus_query + ' WHERE A.ALERT_IDX IN (1,2,10) AND ' SET @sql_virus_group = ' GROUP BY A.SERVERGROUP_IDX, A.CLIENTGROUP_IDX, A.USER_NAME, A.PARENTSERVER_IDX, A.SOURCE, ' + 'I.COMPUTER_NAME, V.VIRUSNAME, V.CATEGORY, V.TYPE2, V.TYPE, ' + 'convert(char,A.' + @timebase + ', 120), V.DISCOVERED ' SET @sql_hpp_query = 'SELECT A.SERVERGROUP_IDX, A.CLIENTGROUP_IDX, A.USER_NAME, ' + 'A.PARENTSERVER_IDX, A.SOURCE, I.COMPUTER_NAME, ' + 'HP.APP_NAME as VIRUSNAME, V.CATEGORY, V.TYPE2, V.TYPE, V2.VIRUSNAME as VirusHelp, ' + 'convert(char,A.' + @timebase + ', 120) as firsttime, ' + 'left(convert(char, V.DISCOVERED, 120), 10) AS Discovered, ' + 'MIN(A.IDX) AS Alert_Idx ' + 'FROM #TEMP_RPT_HPP SUB ' + 'INNER JOIN ALERTS A with (NOLOCK) ON SUB.HPP_APP_IDX COLLATE DATABASE_DEFAULT = A.HPP_APP_IDX COLLATE DATABASE_DEFAULT AND SUB.firsttime = A.' + @timebase + ' ' + 'INNER JOIN VIRUS V with (NOLOCK) ON V.VIRUSNAME_IDX = A.VIRUSNAME_IDX ' + 'INNER JOIN HPP_APPLICATION HP with (NOLOCK) on HP.APP_IDX = A.HPP_APP_IDX ' + 'INNER JOIN VIRUS V2 with (NOLOCK) on V2.VIRUSNAME_IDX = HP.HELP_VIRUS_IDX ' + 'INNER JOIN SEM_COMPUTER I with (NOLOCK) on A.COMPUTER_IDX = I.COMPUTER_ID ' + 'INNER JOIN V_AGENT_INCL_GROUP_ID SA with (NOLOCK) on SA.COMPUTER_ID = I.COMPUTER_ID ' + 'INNER JOIN V_GROUPS G with (NOLOCK) on G.ID = SA.GROUP_ID ' + 'INNER JOIN V_DOMAINS S with (NOLOCK) on S.ID = SA.DOMAIN_ID ' IF datalength(@filter_5150) > 0 SET @sql_hpp_query = @sql_hpp_query + ' WHERE A.ALERT_IDX IN (1,2,10) AND ' SET @sql_hpp_group = ' GROUP BY A.SERVERGROUP_IDX, A.CLIENTGROUP_IDX, A.USER_NAME, A.PARENTSERVER_IDX, A.SOURCE, ' + 'I.COMPUTER_NAME, HP.APP_NAME, V.CATEGORY, V.TYPE2, V.TYPE, ' + 'convert(char,A.' + @timebase + ', 120), V.DISCOVERED, V2.VIRUSNAME ' /* Extract the record count into #TEMP_RECORD_COUNT by using INSERT EXEC */ INSERT INTO #TEMP_RECORD_COUNT EXEC('SELECT COUNT(1) FROM (' + @sql_virus_query + @filter_5150 + @sql_virus_group + ' UNION ALL ' + @sql_hpp_query + @filter_5150 + @sql_hpp_group + ') SUB') SELECT @rec_count = RECORD_COUNT FROM #TEMP_RECORD_COUNT /* The first record set is generated by this clause */ SELECT @rec_count as anzahl /* The second record set is generated by EXEC */ IF (@rec_count > 0) BEGIN IF (@top_num = 0) EXEC('SELECT * FROM (' + @sql_virus_query + @filter_5150 + @sql_virus_group + ' UNION ALL ' + @sql_hpp_query + @filter_5150 + @sql_hpp_group + ') SUB ORDER BY SUB.firsttime DESC, SUB.VIRUSNAME ASC') ELSE BEGIN SELECT @top_num_str = str(@top_num) EXEC('SELECT TOP ' + @top_num_str + ' * FROM (' + @sql_virus_query + @filter_5150 + @sql_virus_group + ' UNION ALL ' + @sql_hpp_query + @filter_5150 + @sql_hpp_group + ') SUB ORDER BY SUB.firsttime DESC, SUB.VIRUSNAME ASC') END END /* Drop temporary tables */ DROP TABLE #TEMP_RPT_VIRUS DROP TABLE #TEMP_RPT_HPP DROP TABLE #TEMP_RECORD_COUNT END 2015-02-25 15:45:51.913 THREAD 19 INFO: /* used for the SONAR chart on av summary view */ DROP PROCEDURE SEM_AV_SUMMARY_SONAR 2015-02-25 15:45:51.913 THREAD 19 INFO: CREATE PROCEDURE SEM_AV_SUMMARY_SONAR @filter_5150 NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT SUM(A.NOOFVIRUSES) as SUM, H.APP_NAME ' + ' FROM ALERTS A with (NOLOCK), V_AGENT_INCL_GROUP_ID as SA, SEM_COMPUTER as I, HPP_APPLICATION H, HPP_ALERTS HA with (NOLOCK), V_GROUPS as G, V_SERVERS as P, V_DOMAINS as S ' + ' WHERE A.HPP_APP_IDX=H.APP_IDX AND HA.IDX = A.IDX and A.SOURCE=''Heuristic Scan'' and A.ALERT_IDX != 9 and I.COMPUTER_ID=SA.COMPUTER_ID and I.COMPUTER_ID=A.COMPUTER_IDX ' + ' and G.ID=SA.GROUP_ID and P.ID=SA.LAST_SERVER_ID and S.ID=SA.DOMAIN_ID and ' + @filter_5150 + ' GROUP BY H.APP_NAME ORDER BY SUM DESC '; EXEC sp_executesql @q END 2015-02-25 15:45:51.913 THREAD 19 INFO: /* used for the Risk Distribution by Attacker chart on the av summary view */ DROP PROCEDURE SEM_AV_SUMMARY_ATTACKER 2015-02-25 15:45:51.913 THREAD 19 INFO: CREATE PROCEDURE SEM_AV_SUMMARY_ATTACKER @filter_5150 NVARCHAR(MAX) /* filter_5150 is part of where clause, it may be longer than 4000 */ AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT A.SOURCE_COMPUTER_NAME as Computer, A.SOURCE_COMPUTER_IP as IP_Address, SUM(A.NOOFVIRUSES) as anzahl ' + ' FROM ALERTS as A with (NOLOCK) INNER JOIN V_AGENT_INCL_GROUP_ID as SA with (NOLOCK) ON SA.COMPUTER_ID = A.COMPUTER_IDX INNER JOIN V_DOMAINS S on S.ID=SA.DOMAIN_ID ' + ' INNER JOIN V_SERVERS P on P.ID=SA.LAST_SERVER_ID INNER JOIN V_GROUPS G on G.ID=SA.GROUP_ID ' + ' WHERE (A.SOURCE_COMPUTER_IP != 0 or A.SOURCE_COMPUTER_NAME != '''') AND A.ALERT_IDX IN (1,2,10) and ' + @filter_5150 + ' GROUP BY A.SOURCE_COMPUTER_NAME, A.SOURCE_COMPUTER_IP ORDER BY anzahl DESC '; EXEC sp_executesql @q END 2015-02-25 15:45:51.913 THREAD 19 INFO: /* used for the Risk Distribution by Group chart on the av summary view */ DROP PROCEDURE SEM_AV_SUMMARY_GROUP 2015-02-25 15:45:51.913 THREAD 19 INFO: CREATE PROCEDURE SEM_AV_SUMMARY_GROUP @filter_5150 NVARCHAR(MAX) /* filter_5150 is part of where clause, it may be longer than 4000 */ AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT G.NAME as Clientgroup, SUM(A.NOOFVIRUSES) as anzahl ' + ' FROM ALERTS as A with (NOLOCK) INNER JOIN V_AGENT_INCL_GROUP_ID as SA with (NOLOCK) ON SA.COMPUTER_ID = A.COMPUTER_IDX INNER JOIN V_DOMAINS S on S.ID=SA.DOMAIN_ID ' + ' INNER JOIN V_SERVERS P on P.ID=SA.LAST_SERVER_ID INNER JOIN V_GROUPS G with (NOLOCK) on G.ID=SA.GROUP_ID ' + ' WHERE A.ALERT_IDX IN (1,2,10) AND ' + @filter_5150 + ' GROUP BY G.NAME ORDER BY anzahl DESC, G.NAME ASC '; EXEC sp_executesql @q END 2015-02-25 15:45:51.913 THREAD 19 INFO: /* used for the Risk Distribution by Source chart on the av summary view */ DROP PROCEDURE SEM_AV_SUMMARY_SOURCE 2015-02-25 15:45:51.913 THREAD 19 INFO: CREATE PROCEDURE SEM_AV_SUMMARY_SOURCE @filter_5150 NVARCHAR(MAX) /* filter_5150 is part of where clause, it may be longer than 4000 */ AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT A.SOURCE as Source, SUM(A.NOOFVIRUSES) as anzahl ' + ' FROM ALERTS as A with (NOLOCK) INNER JOIN V_AGENT_INCL_GROUP_ID as SA with (NOLOCK) ON SA.COMPUTER_ID = A.COMPUTER_IDX INNER JOIN V_GROUPS G on G.ID=SA.GROUP_ID ' + ' INNER JOIN V_DOMAINS S on S.ID=SA.DOMAIN_ID INNER JOIN V_SERVERS P on P.ID=SA.LAST_SERVER_ID ' + ' WHERE A.ALERT_IDX IN (1,2,10) AND ' + @filter_5150 + ' GROUP BY A.SOURCE ORDER BY anzahl DESC, A.SOURCE ASC '; EXEC sp_executesql @q END 2015-02-25 15:45:51.913 THREAD 19 INFO: /* used for the Risk Distribution chart on the av summary view */ DROP PROCEDURE SEM_AV_SUMMARY_RISK_DIST 2015-02-25 15:45:51.929 THREAD 19 INFO: CREATE PROCEDURE SEM_AV_SUMMARY_RISK_DIST @filter_5150 NVARCHAR(MAX) /* filter_5150 is part of where clause, it may be longer than 4000 */ AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT (CASE WHEN V.VIRUSNAME!='''' THEN V.VIRUSNAME ELSE HP.APP_NAME END) AS Virusname,Count(DISTINCT A.Computer_Idx) as NumUniqueInfectedMachines ' + ' FROM alerts as A with (NOLOCK) INNER JOIN V_AGENT_INCL_GROUP_ID as SA with (NOLOCK) ON SA.Computer_ID = A.Computer_IDX INNER JOIN V_GROUPS G on G.ID=SA.GROUP_ID ' + ' INNER JOIN V_DOMAINS S on S.ID=SA.DOMAIN_ID INNER JOIN V_SERVERS P on P.ID=SA.LAST_SERVER_ID , v_virus as V with (NOLOCK), v_hpp_application_risk as HP with (NOLOCK) ' + ' WHERE A.virusname_idx = V.virusname_idx and A.ALERT_IDX IN (1,2,10) AND A.hpp_app_idx = HP.app_idx AND ' + @filter_5150 + ' GROUP BY (CASE WHEN V.VIRUSNAME!='''' THEN V.VIRUSNAME ELSE HP.APP_NAME END) ORDER BY Count(DISTINCT A.Computer_Idx) desc, Virusname asc '; EXEC sp_executesql @q END 2015-02-25 15:45:51.929 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_UNMANAGED_MACHINES 2015-02-25 15:45:51.929 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_UNMANAGED_MACHINES @filter_5150 NVARCHAR(MAX) /* filter_5150 is part of where clause, it may be longer than 4000 */ AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = ' SELECT count(*) as anzahl ' + ' from (SELECT DISTINCT L.MAC_ADDRESS, L.IP_ADDRESS, COM.COMPUTER_NAME ' + ' FROM LAN_DEVICE_DETECTED L with (NOLOCK), SEM_COMPUTER COM with (NOLOCK), V_AGENT_INCL_GROUP_ID SA with (NOLOCK) ' + ' WHERE L.COMPUTER_ID = COM.COMPUTER_ID AND L.DELETED = 0 AND COM.DELETED = 0 ' + ' AND COM.COMPUTER_ID = SA.COMPUTER_ID AND SA.DELETED = 0 ' + ' AND L.MAC_ADDRESS NOT IN (SELECT MAC_ADDR1 FROM SEM_COMPUTER with (NOLOCK) WHERE MAC_ADDR1 IS NOT NULL) ' + ' AND L.MAC_ADDRESS NOT IN (SELECT MAC_ADDR2 FROM SEM_COMPUTER with (NOLOCK) WHERE MAC_ADDR2 IS NOT NULL) ' + ' AND L.MAC_ADDRESS NOT IN (SELECT MAC_ADDR3 FROM SEM_COMPUTER with (NOLOCK) WHERE MAC_ADDR3 IS NOT NULL) ' + ' AND L.MAC_ADDRESS NOT IN (SELECT MAC_ADDR4 FROM SEM_COMPUTER with (NOLOCK) WHERE MAC_ADDR4 IS NOT NULL) ' + ' AND NOT EXISTS (SELECT * FROM LAN_DEVICE_EXCLUDED E with (NOLOCK) ' + ' WHERE L.HASH = E.HASH AND E.DELETED = 0 AND ( (E.EXCLUDE_MODE = 0 AND E.MAC_ADDRESS = L.MAC_ADDRESS) OR (E.EXCLUDE_MODE = 1 AND E.IP_ADDRESS = L.IP_ADDRESS) ' + ' OR (E.EXCLUDE_MODE = 2 AND L.IP_ADDRESS >= E.IP_RANGE_START AND L.IP_ADDRESS <= E.IP_RANGE_END))) ' + @filter_5150 + ' ) as UNMANAGED_MACHINES '; EXEC sp_executesql @q END 2015-02-25 15:45:51.929 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_TWEXPIRING_LICENSES 2015-02-25 15:45:51.929 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_TWEXPIRING_LICENSES @timestart BIGINT, @timeend BIGINT AS BEGIN DECLARE @q NVARCHAR(4000) SET @q = 'select sum(ls.meter_count) as count ' + ' from license ls with (NOLOCK) ' + ' where ls.expire_date in (select max(expire_date) from license with (NOLOCK) group by license.chainid) and ls.expire_date > ' + @timestart + ' and ls.expire_date <= ' + @timeend + ' and ls.deleted=0 and ls.type = ''A'' '; EXEC sp_executesql @q, N'@timestart BIGINT, @timeend BIGINT', @timestart, @timeend END 2015-02-25 15:45:51.929 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_SONAR_REDANDYELLOW 2015-02-25 15:45:51.929 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_SONAR_REDANDYELLOW @yesterday BIGINT, @tooolddef NVARCHAR(100), @sqlwhere NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT COUNT(A.color) as anzahl, A.color ' + ' FROM (SELECT COUNT(DISTINCT(SA.AGENT_ID)) as anzahl, ''color'' = (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END ), SA.AGENT_ID ' + ' FROM V_AGENT_INCL_GROUP_ID SA with (NOLOCK) INNER JOIN V_GROUPS G ON G.ID=SA.GROUP_ID INNER JOIN V_DOMAINS S ON S.ID=SA.DOMAIN_ID ' + ' LEFT OUTER JOIN SEM_CONTENT SC with (NOLOCK) on SC.AGENT_ID=SA.AGENT_ID LEFT OUTER JOIN V_SONAR ON SC.PATTERN_IDX=V_SONAR.PATTERN_IDX ' + ' WHERE SA.AGENT_TYPE=105 AND SA.DELETED=0 and (V_SONAR.DELETED=0 OR V_SONAR.DELETED IS NULL) AND SA.STATUS=1 AND SA.MAJOR_VERSION>10 ' + ' AND SA.PTP_ONOFF not in (2, 127) and SC.DELETED=0 and (SA.R_OS_TYPE) NOT IN (269091840,269092096,269092352,269092608,269092864,269093120) ' + @sqlwhere + ' group by (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END), SA.AGENT_ID HAVING ( MAX(V_SONAR.VERSION) IS NULL ' + ' OR MAX(V_SONAR.VERSION) <=@tooOldDef )) AS A GROUP BY A.color'; EXEC sp_executesql @q, N'@yesterday BIGINT, @tooOldDef NVARCHAR(100)', @yesterday, @tooOldDef END 2015-02-25 15:45:51.929 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_IPS_REDANDYELLOW 2015-02-25 15:45:51.929 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_IPS_REDANDYELLOW @yesterday BIGINT, @tooolddef NVARCHAR(100), @sqlwhere NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT COUNT(DISTINCT(SA.AGENT_ID)) as anzahl, ''color'' = (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END ) ' + ' FROM V_AGENT_INCL_GROUP_ID SA with (NOLOCK) INNER JOIN V_GROUPS G ON G.ID=SA.GROUP_ID INNER JOIN V_DOMAINS S ON S.ID=SA.DOMAIN_ID ' + ' LEFT OUTER JOIN SEM_CONTENT SC with (NOLOCK) on SC.AGENT_ID=SA.AGENT_ID LEFT OUTER JOIN V_IPS ON SC.PATTERN_IDX=V_IPS.PATTERN_IDX ' + ' WHERE SA.AGENT_TYPE=''105'' AND SA.DELETED=0 and (V_IPS.DELETED=0 OR V_IPS.DELETED IS NULL) AND SA.STATUS=1 AND SA.MAJOR_VERSION>10 ' + ' AND (( SA.MAJOR_VERSION = 11 AND SA.FIREWALL_ONOFF not in (2, 127)) OR ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION = 0 AND SA.FIREWALL_ONOFF not in (2, 127)) OR ' + ' ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION > 0 AND SA.CIDS_DRV_ONOFF not in (2, 127)) OR ( SA.MAJOR_VERSION > 12 AND SA.CIDS_DRV_ONOFF not in (2, 127))) ' + ' and SC.DELETED=0 ' + @sqlwhere + ' GROUP BY (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END) HAVING ( MAX(V_IPS.VERSION) IS NULL OR MAX(V_IPS.VERSION) <=@tooolddef )'; EXEC sp_executesql @q, N'@yesterday BIGINT, @tooOldDef NVARCHAR(100)', @yesterday, @tooOldDef END 2015-02-25 15:45:51.945 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_MRCLEAN_REDANDYELLOW 2015-02-25 15:45:51.945 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_MRCLEAN_REDANDYELLOW @yesterday BIGINT, @tooolddef NVARCHAR(100), @sqlwhere NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT COUNT(DISTINCT(SA.AGENT_ID)) as anzahl, ''color'' = (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END ) ' + ' FROM V_AGENT_INCL_GROUP_ID SA with (NOLOCK) INNER JOIN V_GROUPS G ON G.ID=SA.GROUP_ID INNER JOIN V_DOMAINS S ON S.ID=SA.DOMAIN_ID ' + ' LEFT OUTER JOIN SEM_CONTENT SC with (NOLOCK) on SC.AGENT_ID=SA.AGENT_ID LEFT OUTER JOIN V_MR_CLEAN ON SC.PATTERN_IDX=V_MR_CLEAN.PATTERN_IDX ' + ' WHERE SA.AGENT_TYPE=105 AND SA.DELETED=0 and (V_MR_CLEAN.DELETED=0 OR V_MR_CLEAN.DELETED IS NULL) AND SA.STATUS=1 AND SA.MAJOR_VERSION>10 AND SA.DA_ONOFF not in (2, 127) and SC.DELETED=0 ' + @sqlwhere + ' GROUP BY (CASE WHEN @yesterday > SA.CREATION_TIME THEN ''RED'' ELSE ''YELLOW'' END) HAVING ( MAX(V_MR_CLEAN.VERSION) IS NULL OR MAX(V_MR_CLEAN.VERSION) <=@tooOldDef )'; EXEC sp_executesql @q, N'@yesterday BIGINT, @tooOldDef NVARCHAR(100)', @yesterday, @tooOldDef END 2015-02-25 15:45:51.945 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_DISABLED_COUNTS 2015-02-25 15:45:51.945 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_DISABLED_COUNTS @sqlfrom NVARCHAR(4000), @sqlwhere NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT COUNT(*) as anzahl, (CAST(SA.AP_ONOFF as VARCHAR) + ''-'' + CAST(SA.DA_ONOFF as VARCHAR) + ''-'' + CAST(SA.FIREWALL_ONOFF as VARCHAR) + ''-'' + CAST(SA.PTP_ONOFF as VARCHAR) + ''-'' + CAST(CASE when SA.MAJOR_VERSION = 11 OR ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION = 0) then SA.FIREWALL_ONOFF else SA.CIDS_DRV_ONOFF end as varchar)) as TECH_STATE ' + ' FROM V_AGENT_INCL_GROUP_ID SA with (NOLOCK) ' + @sqlfrom + ' WHERE (SA.AP_ONOFF in (0,4) or SA.DA_ONOFF in (0,4) or SA.PTP_ONOFF in (0,4) or SA.FIREWALL_ONOFF in (0,4) or SA.CIDS_DRV_ONOFF in (0,4)) and SA.DELETED=0 AND SA.STATUS=1 and SA.AGENT_TYPE=105 ' + @sqlwhere + ' GROUP BY (CAST(SA.AP_ONOFF as VARCHAR) + ''-'' + CAST(SA.DA_ONOFF as VARCHAR) + ''-'' + CAST(SA.FIREWALL_ONOFF as VARCHAR) + ''-'' + CAST(SA.PTP_ONOFF as VARCHAR) + ''-'' + CAST(CASE when SA.MAJOR_VERSION = 11 OR ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION = 0) then SA.FIREWALL_ONOFF else SA.CIDS_DRV_ONOFF end as VARCHAR)) '; EXEC sp_executesql @q END 2015-02-25 15:45:51.945 THREAD 19 INFO: DROP PROCEDURE SEM_HOMEPAGE_TOTAL_TECH 2015-02-25 15:45:51.945 THREAD 19 INFO: CREATE PROCEDURE SEM_HOMEPAGE_TOTAL_TECH @sqlfrom NVARCHAR(4000), @sqlwhere NVARCHAR(MAX) AS BEGIN DECLARE @q NVARCHAR(MAX) SET @q = 'SELECT COUNT(*) as anzahl, (CAST(SA.AP_ONOFF as VARCHAR) + ''-'' + CAST(SA.DA_ONOFF as VARCHAR) + ''-'' + CAST(SA.FIREWALL_ONOFF as VARCHAR) + ''-'' + CAST(SA.PTP_ONOFF as VARCHAR)+ ''-'' + CAST(CASE when SA.MAJOR_VERSION = 11 OR ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION = 0) then SA.FIREWALL_ONOFF else SA.CIDS_DRV_ONOFF end as varchar)) as TECH_STATE ' + ' from V_AGENT_INCL_GROUP_ID SA with (NOLOCK) ' + @sqlfrom + ' where (SA.AP_ONOFF in (0,1,3,4) or SA.DA_ONOFF in (0,1,3,4) or SA.PTP_ONOFF in (0,1,3,4) or SA.FIREWALL_ONOFF in (0,1,3,4) or SA.CIDS_DRV_ONOFF in (0,1,3,4)) and SA.DELETED=0 and SA.AGENT_TYPE=105 ' + @sqlwhere + ' GROUP BY (CAST(SA.AP_ONOFF as VARCHAR) + ''-'' + CAST(SA.DA_ONOFF as VARCHAR) + ''-'' + CAST(SA.FIREWALL_ONOFF as VARCHAR) + ''-'' + CAST(SA.PTP_ONOFF as VARCHAR)+ ''-'' + CAST(CASE when SA.MAJOR_VERSION = 11 OR ( SA.MAJOR_VERSION = 12 AND SA.MINOR_VERSION = 0) then SA.FIREWALL_ONOFF else SA.CIDS_DRV_ONOFF end as VARCHAR)) '; EXEC sp_executesql @q END 2015-02-25 15:45:51.945 THREAD 19 INFO: drop view V_CLIENT_CHANGE_LOG 2015-02-25 15:45:51.945 THREAD 19 INFO: CREATE VIEW V_CLIENT_CHANGE_LOG as SELECT ATL.CLIENT_ID, ATL.COMPUTER_ID,ATL.DOMAIN_ID, ATL.GROUP_ID, V.SERVER_ID, V.TIME_STAMP, ATL.POLICY_MODE, ATL.DELETED, ATL.COMPUTER_NAME, ATL.COMPUTER_DOMAIN_NAME, ATL.USER_NAME, ATL.USER_DOMAIN_NAME, V.EVENT_ID FROM SEM_CLIENT ATL with (NOLOCK) INNER JOIN V_SERVER_ADMIN_LOG V with (NOLOCK) on ATL.CLIENT_ID=V.CLIENT_ID where V.EVENT_ID in (0x2006, 0x2007, 0x2008, 0x2009, 0x200A, 0x200C, 0x200D, 0x200E, 0x200F, 0x2010, 0x2014, 0x2015, 0x2016) UNION SELECT ATL.CLIENT_ID, ATL.COMPUTER_ID, ATL.DOMAIN_ID, ATL.GROUP_ID, V.SERVER_ID, V.TIME_STAMP, ATL.POLICY_MODE, ATL.DELETED, ATL.COMPUTER_NAME, ATL.COMPUTER_DOMAIN_NAME, ATL.USER_NAME, ATL.USER_DOMAIN_NAME, V.EVENT_ID FROM SEM_CLIENT ATL with (NOLOCK) INNER JOIN V_SERVER_CLIENT_LOG V with (NOLOCK) on ATL.CLIENT_ID=V.CLIENT_ID where V.EVENT_ID in (0x0E, 0x19, 0x1A) UNION SELECT DISTINCT ATL.CLIENT_ID, ATL.COMPUTER_ID, ATL.DOMAIN_ID, ATL.GROUP_ID, V.SERVER_ID, ATL.CREATION_TIME as TIME_STAMP, ATL.POLICY_MODE, ATL.DELETED, ATL.COMPUTER_NAME, ATL.COMPUTER_DOMAIN_NAME, ATL.USER_NAME, ATL.USER_DOMAIN_NAME, EVENT_ID = 12287 FROM SEM_CLIENT ATL with (NOLOCK) LEFT OUTER JOIN V_SERVER_ADMIN_LOG V with (NOLOCK) on ATL.CLIENT_ID=V.CLIENT_ID where ((ATL.CLIENT_ID NOT IN (SELECT SC.CLIENT_ID FROM SEM_CLIENT SC with (NOLOCK) INNER JOIN V_SERVER_CLIENT_LOG VSCL ON SC.CLIENT_ID=VSCL.CLIENT_ID where VSCL.EVENT_ID is not null and VSCL.EVENT_ID IN (0x1A))) AND (V.EVENT_ID is null)) 2015-02-25 15:45:51.960 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'COMMAND' AND C.NAME='RESULT') BEGIN ALTER TABLE COMMAND ADD RESULT NVARCHAR(MAX) NULL END 2015-02-25 15:45:52.007 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='ACTIVATION_CODE') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD ACTIVATION_CODE VARCHAR(256) END 2015-02-25 15:45:52.023 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='ACTIVATION_CODE_EXPIRE_DATE') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD ACTIVATION_CODE_EXPIRE_DATE DATETIME END 2015-02-25 15:45:52.039 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='PARTNER_ID') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD PARTNER_ID CHAR(32) END 2015-02-25 15:45:52.039 THREAD 19 INFO: /* New table for maintaining trusted partner records */ DROP TABLE OAUTH_TRUSTED_PARTNER 2015-02-25 15:45:52.054 THREAD 19 INFO: CREATE TABLE OAUTH_TRUSTED_PARTNER ( PARTNER_ID CHAR(32) NOT NULL, PARTNER_NAME VARCHAR(64) NOT NULL, PARTNER_CODE VARCHAR(256) NOT NULL, CONSTRAINT PK_OAUTH_TRUSTED_PARTNER PRIMARY KEY (PARTNER_ID), CONSTRAINT UK_OAUTH_TRUSTED_PARTNER_PARTNER_NAME UNIQUE (PARTNER_NAME) )ON [PRIMARY] 2015-02-25 15:45:52.054 THREAD 19 INFO: IF NOT EXISTS(SELECT 1 FROM OAUTH_TRUSTED_PARTNER WHERE PARTNER_NAME = 'Symantec Managed Security Services') INSERT INTO OAUTH_TRUSTED_PARTNER(PARTNER_ID, PARTNER_NAME, PARTNER_CODE) SELECT REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Symantec Managed Security Services' , CAST(NEWID() AS VARCHAR(36)) 2015-02-25 15:45:52.054 THREAD 19 INFO: Process SQL script file:MSSQLServer12RU4MP1ToFerrariPCCIndexes.sql 2015-02-25 15:45:52.054 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@1a9facf 2015-02-25 15:45:52.054 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SEM_CONTENT_DELETED_PLUS' ) BEGIN DROP INDEX SEM_CONTENT.I_SEM_CONTENT_DELETED_PLUS END 2015-02-25 15:45:52.054 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SEM_CONTENT_DELETED_PLUS' ) BEGIN DROP INDEX SEM_CONTENT.I_SEM_CONTENT_DELETED_PLUS END 2015-02-25 15:45:52.070 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SCANS_DELETED_PLUS' ) BEGIN DROP INDEX SCANS.I_SCANS_DELETED_PLUS END 2015-02-25 15:45:52.070 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_PACKET_LOG_1_ALERT' ) DROP INDEX AGENT_PACKET_LOG_1.I_AGENT_PACKET_LOG_1_ALERT 2015-02-25 15:45:52.085 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_PACKET_LOG_2_ALERT' ) DROP INDEX AGENT_PACKET_LOG_2.I_AGENT_PACKET_LOG_2_ALERT 2015-02-25 15:45:52.085 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_TRAFFIC_LOG_1_ALERT' ) DROP INDEX AGENT_TRAFFIC_LOG_1.I_AGENT_TRAFFIC_LOG_1_ALERT 2015-02-25 15:45:52.101 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_TRAFFIC_LOG_2_ALERT' ) DROP INDEX AGENT_TRAFFIC_LOG_2.I_AGENT_TRAFFIC_LOG_2_ALERT 2015-02-25 15:45:52.117 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_SYSTEM_LOG_1_ID' ) DROP INDEX SERVER_SYSTEM_LOG_1.I_SERVER_SYSTEM_LOG_1_ID 2015-02-25 15:45:52.132 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_SYSTEM_LOG_2_ID' ) DROP INDEX SERVER_SYSTEM_LOG_2.I_SERVER_SYSTEM_LOG_2_ID 2015-02-25 15:45:52.132 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_ADMIN_LOG_1_ID' ) DROP INDEX SERVER_ADMIN_LOG_1.I_SERVER_ADMIN_LOG_1_ID 2015-02-25 15:45:52.148 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_ADMIN_LOG_2_ID' ) DROP INDEX SERVER_ADMIN_LOG_2.I_SERVER_ADMIN_LOG_2_ID 2015-02-25 15:45:52.148 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_CLIENT_LOG_1_ID' ) DROP INDEX SERVER_CLIENT_LOG_1.I_SERVER_CLIENT_LOG_1_ID 2015-02-25 15:45:52.164 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_CLIENT_LOG_2_ID' ) DROP INDEX SERVER_CLIENT_LOG_2.I_SERVER_CLIENT_LOG_2_ID 2015-02-25 15:45:52.164 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_ENFORCER_LOG_1_ID' ) DROP INDEX SERVER_ENFORCER_LOG_1.I_SERVER_ENFORCER_LOG_1_ID 2015-02-25 15:45:52.179 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_ENFORCER_LOG_2_ID' ) DROP INDEX SERVER_ENFORCER_LOG_2.I_SERVER_ENFORCER_LOG_2_ID 2015-02-25 15:45:52.179 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_POLICY_LOG_1_ID' ) DROP INDEX SERVER_POLICY_LOG_1.I_SERVER_POLICY_LOG_1_ID 2015-02-25 15:45:52.195 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SERVER_POLICY_LOG_2_ID' ) DROP INDEX SERVER_POLICY_LOG_2.I_SERVER_POLICY_LOG_2_ID 2015-02-25 15:45:52.195 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_1_ID' ) DROP INDEX AGENT_SECURITY_LOG_1.I_AGENT_SECURITY_LOG_1_ID 2015-02-25 15:45:52.210 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_2_ID' ) DROP INDEX AGENT_SECURITY_LOG_2.I_AGENT_SECURITY_LOG_2_ID 2015-02-25 15:45:52.210 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SYSTEM_LOG_1_ID' ) DROP INDEX AGENT_SYSTEM_LOG_1.I_AGENT_SYSTEM_LOG_1_ID 2015-02-25 15:45:52.226 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SYSTEM_LOG_2_ID' ) DROP INDEX AGENT_SYSTEM_LOG_2.I_AGENT_SYSTEM_LOG_2_ID 2015-02-25 15:45:52.226 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_TRAFFIC_LOG_1_ID' ) DROP INDEX AGENT_TRAFFIC_LOG_1.I_AGENT_TRAFFIC_LOG_1_ID 2015-02-25 15:45:52.226 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_TRAFFIC_LOG_2_ID' ) DROP INDEX AGENT_TRAFFIC_LOG_2.I_AGENT_TRAFFIC_LOG_2_ID 2015-02-25 15:45:52.242 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_PACKET_LOG_1_ID' ) DROP INDEX AGENT_PACKET_LOG_1.I_AGENT_PACKET_LOG_1_ID 2015-02-25 15:45:52.242 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_PACKET_LOG_2_ID' ) DROP INDEX AGENT_PACKET_LOG_2.I_AGENT_PACKET_LOG_2_ID 2015-02-25 15:45:52.257 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_BEHAVIOR_LOG_1_ID' ) DROP INDEX AGENT_BEHAVIOR_LOG_1.I_AGENT_BEHAVIOR_LOG_1_ID 2015-02-25 15:45:52.257 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_BEHAVIOR_LOG_2_ID' ) DROP INDEX AGENT_BEHAVIOR_LOG_2.I_AGENT_BEHAVIOR_LOG_2_ID 2015-02-25 15:45:52.273 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ENFORCER_CLIENT_LOG_1_ID' ) DROP INDEX ENFORCER_CLIENT_LOG_1.I_ENFORCER_CLIENT_LOG_1_ID 2015-02-25 15:45:52.273 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ENFORCER_CLIENT_LOG_2_ID' ) DROP INDEX ENFORCER_CLIENT_LOG_2.I_ENFORCER_CLIENT_LOG_2_ID 2015-02-25 15:45:52.273 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ENFORCER_SYSTEM_LOG_1_ID' ) DROP INDEX ENFORCER_SYSTEM_LOG_1.I_ENFORCER_SYSTEM_LOG_1_ID 2015-02-25 15:45:52.289 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ENFORCER_SYSTEM_LOG_2_ID' ) DROP INDEX ENFORCER_SYSTEM_LOG_2.I_ENFORCER_SYSTEM_LOG_2_ID 2015-02-25 15:45:52.289 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'A_ALERTS_MOTHER_IDX_PLUS' ) DROP INDEX ALERTS.A_ALERTS_MOTHER_IDX_PLUS 2015-02-25 15:45:52.289 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_ACTUALACTION_IDX' ) DROP INDEX ALERTS.I_ALERTS_ACTUALACTION_IDX 2015-02-25 15:45:52.304 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_ACTUALACTION_IDX_PLUS' ) DROP INDEX ALERTS.I_ALERTS_ACTUALACTION_IDX_PLUS 2015-02-25 15:45:52.304 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_ALERT_IDX' ) DROP INDEX ALERTS.I_ALERTS_ALERT_IDX 2015-02-25 15:45:52.320 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_ALERTDATETIME_DEL' ) DROP INDEX ALERTS.I_ALERTS_ALERTDATETIME_DEL 2015-02-25 15:45:52.320 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_ALERTDATETIME_PLUS' ) DROP INDEX ALERTS.I_ALERTS_ALERTDATETIME_PLUS 2015-02-25 15:45:52.320 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_COMPUTER_IDX' ) DROP INDEX ALERTS.I_ALERTS_COMPUTER_IDX 2015-02-25 15:45:52.335 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_HPP_APP_IDX' ) DROP INDEX ALERTS.I_ALERTS_HPP_APP_IDX 2015-02-25 15:45:52.335 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_HPP_APP_IDX_PLUS' ) DROP INDEX ALERTS.I_ALERTS_HPP_APP_IDX_PLUS 2015-02-25 15:45:52.335 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_MOTHER_IDX' ) DROP INDEX ALERTS.I_ALERTS_MOTHER_IDX 2015-02-25 15:45:52.351 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_SOURCE' ) DROP INDEX ALERTS.I_ALERTS_SOURCE 2015-02-25 15:45:52.351 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_SOURCE_PLUS' ) DROP INDEX ALERTS.I_ALERTS_SOURCE_PLUS 2015-02-25 15:45:52.351 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_ALERTS_SOURCE_ALERT_IDX' ) DROP INDEX ALERTS.I_ALERTS_SOURCE_ALERT_IDX 2015-02-25 15:45:52.367 THREAD 19 INFO: CREATE INDEX I_ALERTS_SOURCE_ALERT_IDX ON ALERTS(ALERTDATETIME, COMPUTER_IDX, MOTHER_IDX, SOURCE, ALERT_IDX, DELETED) INCLUDE (NOOFVIRUSES, HPP_APP_IDX, ACTUALACTION_IDX, VIRUSNAME_IDX, ALERTINSERTTIME, IDX) 2015-02-25 15:45:52.429 THREAD 19 INFO: IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SEM_COMPUTER_DELETED' ) DROP INDEX SEM_COMPUTER.I_SEM_COMPUTER_DELETED 2015-02-25 15:45:52.429 THREAD 19 INFO: CREATE INDEX I_SEM_COMPUTER_DELETED ON SEM_COMPUTER(DELETED) INCLUDE (COMPUTER_ID, COMPUTER_NAME, IP_ADDR1) 2015-02-25 15:45:52.445 THREAD 19 INFO: Process SQL script file:MSSQLServerFerrariPCCToFerrariAlpha.sql 2015-02-25 15:45:52.445 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@11bcdee 2015-02-25 15:45:52.445 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='ACTIVATION_CODE') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD ACTIVATION_CODE VARCHAR(256) END 2015-02-25 15:45:52.445 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='ACTIVATION_CODE') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD ACTIVATION_CODE VARCHAR(256) END 2015-02-25 15:45:52.460 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='ACTIVATION_CODE_EXPIRE_DATE') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD ACTIVATION_CODE_EXPIRE_DATE DATETIME END 2015-02-25 15:45:52.476 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'OAUTH_CLIENT_DETAILS' AND C.NAME='PARTNER_ID') BEGIN ALTER TABLE OAUTH_CLIENT_DETAILS ADD PARTNER_ID CHAR(32) END 2015-02-25 15:45:52.492 THREAD 19 INFO: /* New table for maintaining trusted partner records */ DROP TABLE OAUTH_TRUSTED_PARTNER 2015-02-25 15:45:52.492 THREAD 19 INFO: CREATE TABLE OAUTH_TRUSTED_PARTNER ( PARTNER_ID CHAR(32) NOT NULL, PARTNER_NAME VARCHAR(64) NOT NULL, PARTNER_CODE VARCHAR(256) NOT NULL, CONSTRAINT PK_OAUTH_TRUSTED_PARTNER PRIMARY KEY (PARTNER_ID), CONSTRAINT UK_OAUTH_TRUSTED_PARTNER_PARTNER_NAME UNIQUE (PARTNER_NAME) )ON [PRIMARY] 2015-02-25 15:45:52.492 THREAD 19 INFO: DROP TABLE PROCESSING_MARKER 2015-02-25 15:45:52.492 THREAD 19 INFO: CREATE TABLE PROCESSING_MARKER ( TABLE_NAME NVARCHAR(256) NOT NULL, BATCH_SIZE BIGINT NOT NULL, LAST_STATUS TINYINT NULL,/* -1 FAILED, 0 SUCCEEDED, 1 RUNNING */ LAST_SERVER_ID CHAR(32) NOT NULL, LAST_START_VALUE BIGINT NOT NULL, LAST_END_VALUE BIGINT NULL, LAST_START_TIME BIGINT NOT NULL, LAST_DURATION_IN_SECONDS BIGINT NULL, LAST_ERROR_MSG NVARCHAR(2000) NULL, NEXT_START_VALUE BIGINT NULL )ON [PRIMARY] 2015-02-25 15:45:52.492 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_SECURITY_LOG_1' AND C.NAME='SEQ_ID') BEGIN ALTER TABLE AGENT_SECURITY_LOG_1 ADD SEQ_ID BIGINT not NULL IDENTITY NOT FOR REPLICATION END 2015-02-25 15:45:52.570 THREAD 19 INFO: IF NOT EXISTS (SELECT C.NAME FROM SYSOBJECTS S, SYSCOLUMNS C WHERE C.ID = S.ID AND S.NAME = 'AGENT_SECURITY_LOG_2' AND C.NAME='SEQ_ID') BEGIN ALTER TABLE AGENT_SECURITY_LOG_2 ADD SEQ_ID BIGINT not NULL IDENTITY NOT FOR REPLICATION END 2015-02-25 15:45:52.679 THREAD 19 INFO: drop view V_AGENT_SECURITY_LOG 2015-02-25 15:45:52.679 THREAD 19 INFO: CREATE VIEW V_AGENT_SECURITY_LOG as SELECT *, CASE WHEN LOCAL_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(LOCAL_HOST_IPV6))) > 0 THEN substring(LOCAL_HOST_IPV6, 1, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 5, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 9, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 13, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 17, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 21, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 25,4 ) + ':' + substring(LOCAL_HOST_IPV6, 29, 4 ) ELSE CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end & 0xFF as VARCHAR) END as LOCAL_HOST_IP_TEXT , CASE WHEN REMOTE_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(REMOTE_HOST_IPV6))) > 0 THEN substring(REMOTE_HOST_IPV6, 1, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 5, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 9, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 13, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 17, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 21, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 25,4 ) + ':' + substring(REMOTE_HOST_IPV6, 29, 4 ) ELSE CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end & 0xFF as VARCHAR) END as REMOTE_HOST_IP_TEXT FROM AGENT_SECURITY_LOG_1 UNION ALL SELECT *, CASE WHEN LOCAL_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(LOCAL_HOST_IPV6))) > 0 THEN substring(LOCAL_HOST_IPV6, 1, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 5, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 9, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 13, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 17, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 21, 4 ) + ':' + substring(LOCAL_HOST_IPV6, 25,4 ) + ':' + substring(LOCAL_HOST_IPV6, 29, 4 ) ELSE CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when LOCAL_HOST_IP < 0 then 0xFFFFFFFF + LOCAL_HOST_IP else LOCAL_HOST_IP end & 0xFF as VARCHAR) END as LOCAL_HOST_IP_TEXT , CASE WHEN REMOTE_HOST_IPV6 IS NOT NULL AND LEN(LTRIM(RTRIM(REMOTE_HOST_IPV6))) > 0 THEN substring(REMOTE_HOST_IPV6, 1, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 5, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 9, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 13, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 17, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 21, 4 ) + ':' + substring(REMOTE_HOST_IPV6, 25,4 ) + ':' + substring(REMOTE_HOST_IPV6, 29, 4 ) ELSE CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when REMOTE_HOST_IP < 0 then 0xFFFFFFFF + REMOTE_HOST_IP else REMOTE_HOST_IP end & 0xFF as VARCHAR) END as REMOTE_HOST_IP_TEXT FROM AGENT_SECURITY_LOG_2 2015-02-25 15:45:52.695 THREAD 19 INFO: IF NOT EXISTS(SELECT 1 FROM OAUTH_TRUSTED_PARTNER WHERE PARTNER_NAME = 'Symantec Managed Security Services') INSERT INTO OAUTH_TRUSTED_PARTNER(PARTNER_ID, PARTNER_NAME, PARTNER_CODE) SELECT REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Symantec Managed Security Services' , CAST(NEWID() AS VARCHAR(36)) 2015-02-25 15:45:52.695 THREAD 19 INFO: Process SQL script file:MSSQLServerFerrariPCCToFerrariAlphaIndex.sql 2015-02-25 15:45:52.695 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@8b378c 2015-02-25 15:45:52.695 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : IF NOT EXISTS (SELECT GROUPNAME FROM SYSFILEGROUPS WHERE GROUPNAME = 'FG_INDEX' ) BEGIN /* Create non clustered index to avoid performance side effect during upgrade, test result is unstable*/ IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_1_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_1_SEQ_ID ON AGENT_SECURITY_LOG_1 (SEQ_ID) IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_2_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_2_SEQ_ID ON AGENT_SECURITY_LOG_2 (SEQ_ID) IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'PK_PROCESSING_MARKER' ) ALTER TABLE PROCESSING_MARKER ADD CONSTRAINT PK_PROCESSING_MARKER PRIMARY KEY NONCLUSTERED (TABLE_NAME) END 2015-02-25 15:45:52.695 THREAD 19 INFO: IF NOT EXISTS (SELECT GROUPNAME FROM SYSFILEGROUPS WHERE GROUPNAME = 'FG_INDEX' ) BEGIN /* Create non clustered index to avoid performance side effect during upgrade, test result is unstable*/ IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_1_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_1_SEQ_ID ON AGENT_SECURITY_LOG_1 (SEQ_ID) IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_2_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_2_SEQ_ID ON AGENT_SECURITY_LOG_2 (SEQ_ID) IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'PK_PROCESSING_MARKER' ) ALTER TABLE PROCESSING_MARKER ADD CONSTRAINT PK_PROCESSING_MARKER PRIMARY KEY NONCLUSTERED (TABLE_NAME) END 2015-02-25 15:45:52.710 THREAD 19 INFO: IF EXISTS (SELECT GROUPNAME FROM SYSFILEGROUPS WHERE GROUPNAME = 'FG_INDEX' ) BEGIN /* Create non clustered index to avoid performance side effect during upgrade, test result is unstable*/ IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_1_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_1_SEQ_ID ON AGENT_SECURITY_LOG_1 (SEQ_ID) ON FG_INDEX IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_AGENT_SECURITY_LOG_2_SEQ_ID' ) CREATE INDEX I_AGENT_SECURITY_LOG_2_SEQ_ID ON AGENT_SECURITY_LOG_2 (SEQ_ID) ON FG_INDEX IF NOT EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'PK_PROCESSING_MARKER' ) ALTER TABLE PROCESSING_MARKER ADD CONSTRAINT PK_PROCESSING_MARKER PRIMARY KEY NONCLUSTERED (TABLE_NAME) ON FG_INDEX END 2015-02-25 15:45:52.757 THREAD 19 INFO: Process SQL script file:MSSQLServerFerrariAlphaToFerrariBeta.sql 2015-02-25 15:45:52.773 THREAD 19 INFO: Process SQL InputStream sqlis:sun.net.www.protocol.jar.JarURLConnection$JarURLInputStream@99169f 2015-02-25 15:45:52.773 THREAD 19 INFO: DatabaseUtilities> execCommandFromScript>> cmdSeperator : ;; , sqlStr : DROP TABLE AUDIT_LOG 2015-02-25 15:45:52.773 THREAD 19 INFO: DROP TABLE AUDIT_LOG 2015-02-25 15:45:52.773 THREAD 19 INFO: CREATE TABLE AUDIT_LOG( SEQ_ID BIGINT NOT NULL IDENTITY CONSTRAINT PK_AUDITLOG PRIMARY KEY CLUSTERED, TIMESTAMP BIGINT NOT NULL, METHOD varchar(256) NOT NULL, ARGUMENTS nvarchar(MAX) NOT NULL, ADMIN_ID binary(16) NOT NULL, /* defect 3508795, adjusting datatype and length of the column to match definition of fresh install*/ IP_ADDR varchar(128) NOT NULL )ON [PRIMARY] 2015-02-25 15:45:52.773 THREAD 19 INFO: DROP VIEW V_AUDIT_SYS_ADMIN 2015-02-25 15:45:52.773 THREAD 19 INFO: CREATE VIEW V_AUDIT_SYS_ADMIN as SELECT CAST(S.a.value('(./@Id)', 'nvarchar(64)') as binary(16)) AS Id, S.a.value('(./@Name)', 'nvarchar(128)') AS Name, S.a.value('(./@EmailAddress)', 'nvarchar(256)') AS EmailAddress FROM ( SELECT CAST(CAST(m.CONTENT AS VARBINARY(MAX)) AS XML) AS XmlBlob FROM BASIC_METADATA m WHERE TYPE = 'SysAdministratorArray') AS x CROSS APPLY x.XmlBlob.nodes('/SysAdministratorArray/SemAdministrator') S(a) 2015-02-25 15:45:52.773 THREAD 19 INFO: DROP VIEW V_AUDIT_LIMITED_ADMIN 2015-02-25 15:45:52.773 THREAD 19 INFO: CREATE VIEW V_AUDIT_LIMITED_ADMIN as SELECT CAST(S.a.value('(./@Id)', 'nvarchar(64)') as binary(16)) AS Id, S.a.value('(./@Name)', 'nvarchar(128)') AS Name, S.a.value('(./@EmailAddress)', 'nvarchar(256)') AS EmailAddress FROM ( SELECT CAST(CAST(m.CONTENT AS VARBINARY(MAX)) AS XML) AS XmlBlob FROM BASIC_METADATA m WHERE TYPE = 'AdminContext') AS x CROSS APPLY x.XmlBlob.nodes('/AdminContext/SemAdministrator') S(a) 2015-02-25 15:45:52.773 THREAD 19 INFO: DROP VIEW V_AUDIT_LOG 2015-02-25 15:45:52.773 THREAD 19 INFO: CREATE VIEW V_AUDIT_LOG as select A.TIMESTAMP, A.METHOD, A.ARGUMENTS, A.IP_ADDR, X.NAME, X.EMAILADDRESS FROM AUDIT_LOG A LEFT JOIN (select * from V_AUDIT_SYS_ADMIN UNION ALL select * from V_AUDIT_LIMITED_ADMIN) as X ON A.ADMIN_ID=X.ID 2015-02-25 15:45:52.789 THREAD 19 INFO: ALTER PROCEDURE S_UPDATE_VIRUS_CATEGORY (@InputXml XML) AS BEGIN SET NOCOUNT ON; DECLARE @trancount INT; SET @trancount = @@TRANCOUNT; BEGIN TRY IF @trancount = 0 BEGIN TRANSACTION ELSE SAVE TRANSACTION S_UPDATE_VIRUS_CATEGORY; /* Populate Virus information from XML input */ DECLARE @VirusTable TABLE (n NVARCHAR(256), c INT, d DATETIME) DECLARE @DefaultDate DATETIME, @DefaultCategory INT SELECT @DefaultDate = '1970-01-01', @DefaultCategory=1 INSERT INTO @VirusTable (n, c, d) SELECT T.r.value('(n/text())[1]', 'nvarchar(256)') AS n, (case when T.r.value('(c/text())[1]', 'int') = null or T.r.value('(c/text())[1]', 'int') = 0 then @DefaultCategory else T.r.value('(c/text())[1]', 'int') end) as c, ISNULL(T.r.value('(d/text())[1]', 'datetime'), @DefaultDate) as d FROM @InputXml.nodes('/savfeed/r') AS T(r) /* Handle invalid input */ IF NOT EXISTS(SELECT 1 FROM @VirusTable) RAISERROR('Invalid XML data for Virus Information!', 16, 1); /* Update category and discovered column values */ UPDATE v SET CATEGORY = t.c, DISCOVERED = t.d FROM VIRUS v INNER JOIN @VirusTable t ON v.VIRUSNAME = t.n WHERE (V.TYPE in( 0,2,3) OR V.TYPE is null) /* Handle NOT-FOUND entries */ UPDATE v SET CATEGORY = @DefaultCategory, DISCOVERED = @DefaultDate FROM VIRUS v with (UPDLOCK) LEFT OUTER JOIN @VirusTable t ON v.VIRUSNAME = t.n WHERE t.n IS NULL AND (V.TYPE in( 0,2,3) OR V.TYPE is null) /* Handle MAXCATEGORY column */ UPDATE v SET MAXCATEGORY = CATEGORY FROM VIRUS v WHERE CATEGORY > MAXCATEGORY AND (v.TYPE in( 0,2,3) OR V.TYPE is null) DONE: IF @trancount = 0 COMMIT; END TRY BEGIN CATCH DECLARE @error INT, @message VARCHAR(4000), @xstate INT; SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE(); IF @xstate = -1 ROLLBACK; IF @xstate = 1 AND @trancount = 0 ROLLBACK; IF @xstate = 1 AND @trancount > 0 ROLLBACK TRANSACTION S_UPDATE_VIRUS_CATEGORY; RAISERROR ('S_UPDATE_VIRUS_CATEGORY: %d: %s', 16, 1, @error, @message); END CATCH END 2015-02-25 15:45:52.914 THREAD 19 SEVERE: SQL Exception: 2015-02-25 15:45:52.914 THREAD 19 SEVERE: SQL Command: ALTER PROCEDURE S_UPDATE_VIRUS_CATEGORY (@InputXml XML) AS BEGIN SET NOCOUNT ON; DECLARE @trancount INT; SET @trancount = @@TRANCOUNT; BEGIN TRY IF @trancount = 0 BEGIN TRANSACTION ELSE SAVE TRANSACTION S_UPDATE_VIRUS_CATEGORY; /* Populate Virus information from XML input */ DECLARE @VirusTable TABLE (n NVARCHAR(256), c INT, d DATETIME) DECLARE @DefaultDate DATETIME, @DefaultCategory INT SELECT @DefaultDate = '1970-01-01', @DefaultCategory=1 INSERT INTO @VirusTable (n, c, d) SELECT T.r.value('(n/text())[1]', 'nvarchar(256)') AS n, (case when T.r.value('(c/text())[1]', 'int') = null or T.r.value('(c/text())[1]', 'int') = 0 then @DefaultCategory else T.r.value('(c/text())[1]', 'int') end) as c, ISNULL(T.r.value('(d/text())[1]', 'datetime'), @DefaultDate) as d FROM @InputXml.nodes('/savfeed/r') AS T(r) /* Handle invalid input */ IF NOT EXISTS(SELECT 1 FROM @VirusTable) RAISERROR('Invalid XML data for Virus Information!', 16, 1); /* Update category and discovered column values */ UPDATE v SET CATEGORY = t.c, DISCOVERED = t.d FROM VIRUS v INNER JOIN @VirusTable t ON v.VIRUSNAME = t.n WHERE (V.TYPE in( 0,2,3) OR V.TYPE is null) /* Handle NOT-FOUND entries */ UPDATE v SET CATEGORY = @DefaultCategory, DISCOVERED = @DefaultDate FROM VIRUS v with (UPDLOCK) LEFT OUTER JOIN @VirusTable t ON v.VIRUSNAME = t.n WHERE t.n IS NULL AND (V.TYPE in( 0,2,3) OR V.TYPE is null) /* Handle MAXCATEGORY column */ UPDATE v SET MAXCATEGORY = CATEGORY FROM VIRUS v WHERE CATEGORY > MAXCATEGORY AND (v.TYPE in( 0,2,3) OR V.TYPE is null) DONE: IF @trancount = 0 COMMIT; END TRY BEGIN CATCH DECLARE @error INT, @message VARCHAR(4000), @xstate INT; SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE(); IF @xstate = -1 ROLLBACK; IF @xstate = 1 AND @trancount = 0 ROLLBACK; IF @xstate = 1 AND @trancount > 0 ROLLBACK TRANSACTION S_UPDATE_VIRUS_CATEGORY; RAISERROR ('S_UPDATE_VIRUS_CATEGORY: %d: %s', 16, 1, @error, @message); END CATCH END 2015-02-25 15:45:52.914 THREAD 19 SEVERE: SQLState: S0002 2015-02-25 15:45:52.914 THREAD 19 SEVERE: Message: Invalid object name 'S_UPDATE_VIRUS_CATEGORY'. 2015-02-25 15:45:52.914 THREAD 19 SEVERE: Vendor: 208 2015-02-25 15:45:52.914 THREAD 19 SEVERE: java.sql.SQLException: Invalid object name 'S_UPDATE_VIRUS_CATEGORY'. 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.DatabaseUtilities.execCommandFromScript(DatabaseUtilities.java:2084) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.DbHelper.execCommandFromScript(DbHelper.java:248) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.SqlDbHelper.upgrade(SqlDbHelper.java:1054) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.upgradeDatabaseSchema(Upgrade.java:3173) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.doUpgrade(Upgrade.java:617) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeTask.go(UpgradeTask.java:114) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeProgressPanel$2.construct(UpgradeProgressPanel.java:241) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.util.SwingWorker$2.run(SwingWorker.java:145) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at java.lang.Thread.run(Thread.java:745) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: java.sql.SQLException: java.sql.SQLException: Invalid object name 'S_UPDATE_VIRUS_CATEGORY'. 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.DatabaseUtilities.execCommandFromScript(DatabaseUtilities.java:2094) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.DbHelper.execCommandFromScript(DbHelper.java:248) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.SqlDbHelper.upgrade(SqlDbHelper.java:1054) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.upgradeDatabaseSchema(Upgrade.java:3173) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.doUpgrade(Upgrade.java:617) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeTask.go(UpgradeTask.java:114) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeProgressPanel$2.construct(UpgradeProgressPanel.java:241) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at com.sygate.scm.util.SwingWorker$2.run(SwingWorker.java:145) 2015-02-25 15:45:52.914 THREAD 19 SEVERE: at java.lang.Thread.run(Thread.java:745) 2015-02-25 15:45:52.976 THREAD 19 WARNING: Upgrade> updateTransactionLogFileSize>> Original log size is 2097152 2015-02-25 15:45:52.976 THREAD 19 SEVERE: java.sql.SQLException: Failed to upgrade schema: java.sql.SQLException: java.sql.SQLException: Invalid object name 'S_UPDATE_VIRUS_CATEGORY'. 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.server.db.util.SqlDbHelper.upgrade(SqlDbHelper.java:1078) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.upgradeDatabaseSchema(Upgrade.java:3173) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.Upgrade.doUpgrade(Upgrade.java:617) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeTask.go(UpgradeTask.java:114) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.server.upgrade.ui.UpgradeProgressPanel$2.construct(UpgradeProgressPanel.java:241) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at com.sygate.scm.util.SwingWorker$2.run(SwingWorker.java:145) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: at java.lang.Thread.run(Thread.java:745) 2015-02-25 15:45:52.976 THREAD 19 SEVERE: Upgrade.doUpgrade java.sql.SQLException: Failed to upgrade schema: java.sql.SQLException: java.sql.SQLException: Invalid object name 'S_UPDATE_VIRUS_CATEGORY'. 2015-02-25 15:45:52.976 THREAD 19 WARNING: Upgrade> doUpgrade>> Upgrade process completed in (ms) : 19985 2015-02-25 15:45:53.320 THREAD 19 INFO: AuditUtil> auditEvent>> The Upgrading is executed by windows user: sep.admin 2015-02-25 15:45:53.335 THREAD 15 INFO: MainFrame>> doInitFinalPanel> Upgrade.serviceInstallState = 0 2015-02-25 15:46:59.509 THREAD 15 INFO: UpgradeFinalPanel >> saveStage > Launching log file...