def parse_result(self, dsconfs, result): if result.stderr: log.debug('MSSQL error: {0}' + ''.join(result.stderr)) if result.exit_code != 0: for dsconf in dsconfs: dsconf.params['counter'] = dsconf counters = dsconf.params['counter'] log.info( 'Non-zero exit code ({0}) for counters, {1}, on {2}'.format( result.exit_code, counters, dsconf.device)) return # Parse values valuemap = {} for counterline in filter_sql_stdout(result.stdout): key, value = counterline.split(':', 1) if key.strip() == 'ckey': _counter = value.strip().lower() elif key.strip() == 'cvalue': valuemap[_counter] = value.strip() for dsconf in dsconfs: try: key = dsconf.params['resource'].lower() value = float(valuemap[key]) timestamp = int(time.mktime(time.localtime())) yield dsconf, value, timestamp except: log.debug("No value was returned for {0}".format( dsconf.params['resource']))
def parse_result(self, dsconfs, result): if result.stderr: log.debug('MSSQL error: {0}'.format('\n'.join(result.stderr))) log.debug('MSSQL results: {}'.format('\n'.join(result.stdout))) if result.exit_code != 0: for dsconf in dsconfs: dsconf.params['counter'] = dsconf counters = dsconf.params['counter'] log.debug( 'Non-zero exit code ({0}) for counters, {1}, on {2}'.format( result.exit_code, counters, dsconf.device)) return # Parse values self.valuemap = {} db_regex = re.compile('(.*):counter:(.*):value:(.*)') for counterline in filter_sql_stdout(result.stdout): try: databasename, _counter, value = db_regex.match( counterline).groups() databasename = databasename.strip() _counter = _counter.strip().lower() value = value.strip() except Exception: log.debug('MSSQL parse_result error in data: %s', counterline) continue if databasename not in self.valuemap: self.valuemap[databasename] = {} if _counter == 'databasestatus': self.valuemap[databasename]['status'] = value else: self.valuemap[databasename][_counter] = value for dsconf in dsconfs: timestamp = int(time.mktime(time.localtime())) if dsconf.params['resource'] == 'status': # no need to get status as it's handled differently yield dsconf, '', timestamp continue databasename = dsconf.params['contexttitle'] try: key = dsconf.params['resource'].lower() value = float(self.valuemap[databasename][key]) yield dsconf, value, timestamp except Exception: log.debug( "No value was returned for counter {0} on {1}".format( dsconf.params['resource'], databasename))
def parse_result(self, dsconfs, result): if result.stderr: log.debug('MSSQL error: {0}'.format('\n'.join(result.stderr))) log.debug('MSSQL results: {}'.format('\n'.join(result.stdout))) if result.exit_code != 0: for dsconf in dsconfs: dsconf.params['counter'] = dsconf counters = dsconf.params['counter'] log.debug( 'Non-zero exit code ({0}) for counters, {1}, on {2}' .format( result.exit_code, counters, dsconf.device)) return # Parse values self.valuemap = {} db_regex = re.compile('(.*):counter:(.*):value:(.*)') for counterline in filter_sql_stdout(result.stdout): try: databasename, _counter, value = db_regex.match(counterline).groups() databasename = databasename.strip() _counter = _counter.strip().lower() value = value.strip() except Exception: log.debug('MSSQL parse_result error in data: %s', counterline) continue if databasename not in self.valuemap: self.valuemap[databasename] = {} if _counter == 'databasestatus': self.valuemap[databasename]['status'] = value else: self.valuemap[databasename][_counter] = value for dsconf in dsconfs: timestamp = int(time.mktime(time.localtime())) if dsconf.params['resource'] == 'status': # no need to get status as it's handled differently yield dsconf, '', timestamp continue databasename = dsconf.params['contexttitle'] try: key = dsconf.params['resource'].lower() value = float(self.valuemap[databasename][key]) yield dsconf, value, timestamp except Exception: log.debug("No value was returned for counter {0} on {1}".format(dsconf.params['resource'], databasename))
def parse_result(self, dsconfs, result): if result.stderr: log.debug('MSSQL error: {0}'.format('\n'.join(result.stderr))) log.debug('MSSQL results: {}'.format('\n'.join(result.stdout))) if result.exit_code != 0: for dsconf in dsconfs: dsconf.params['counter'] = dsconf counters = dsconf.params['counter'] log.debug( 'Non-zero exit code ({0}) for counters, {1}, on {2}'.format( result.exit_code, counters, dsconf.device)) return # Parse values self.valuemap = {} for counterline in filter_sql_stdout(result.stdout): key, value = counterline.split(':', 1) if key.strip() == 'databasename': databasename = value.strip() if databasename not in self.valuemap: self.valuemap[databasename] = {} elif key.strip() == 'ckey': _counter = value.strip().lower() elif key.strip() == 'cvalue': self.valuemap[databasename][_counter] = value.strip() elif key.strip() == 'databasestatus': self.valuemap[databasename]['status'] = value.strip() for dsconf in dsconfs: if dsconf.params['resource'] == 'status': # no need to get status as it's handled differently continue timestamp = int(time.mktime(time.localtime())) databasename = dsconf.params['contexttitle'] try: key = dsconf.params['resource'].lower() value = float(self.valuemap[databasename][key]) yield dsconf, value, timestamp except Exception: log.debug( "No value was returned for counter {0} on {1}".format( dsconf.params['resource'], databasename))
def parse_result(self, dsconfs, result): if result.stderr: log.debug('MSSQL error: {0}' + ''.join(result.stderr)) if result.exit_code != 0: for dsconf in dsconfs: dsconf.params['counter'] = dsconf counters = dsconf.params['counter'] log.info( 'Non-zero exit code ({0}) for counters, {1}, on {2}' .format( result.exit_code, counters, dsconf.device)) return # Parse values valuemap = {} for counterline in filter_sql_stdout(result.stdout): key, value = counterline.split(':', 1) if key.strip() == 'databasename': databasename = value.strip() if databasename not in valuemap: valuemap[databasename] = {} elif key.strip() == 'ckey': _counter = value.strip().lower() elif key.strip() == 'cvalue': valuemap[databasename][_counter] = value.strip() for dsconf in dsconfs: try: key = dsconf.params['resource'].lower() databasename = dsconf.params['contexttitle'] value = float(valuemap[databasename][key]) timestamp = int(time.mktime(time.localtime())) yield dsconf, value, timestamp except: log.debug("No value was returned for {0}".format(dsconf.params['resource']))
def parse_result(self, dsconfs, result): log.debug('MSSQLJob results: {}'.format(result)) collectedResults = ParsedResults() if result.stderr: log.debug('MSSQL error: {0}' + ''.join(result.stderr)) if result.exit_code != 0: log.info( 'Non-zero exit code ({}) for job query status on {}'.format( result.exit_code, dsconfs[0].device)) return collectedResults # Parse values valuemap = {} jobname = 'Unknown' try: for jobline in filter_sql_stdout(result.stdout): for job in jobline.split('|'): key, value = job.split(':', 1) if key.strip() == 'job': jobname = value.strip() if jobname not in valuemap: valuemap[jobname] = {} else: valuemap[jobname][key] = value.strip() except ValueError: msg = 'Malformed data received for MSSQL Job {}'.format(jobname) collectedResults.events.append({ 'severity': ZenEventClasses.Error, 'eventClassKey': 'winrsCollection MSSQLJob', 'eventKey': dsconfs[0].eventKey if dsconfs[0].eventKey else self.key, 'summary': msg, 'device': dsconfs[0].device, 'query_results': result.stdout }) for dsconf in dsconfs: component = dsconf.params['contexttitle'] eventClass = dsconf.eventClass if dsconf.eventClass else "/Status" try: currentstate = { 'Succeeded': ZenEventClasses.Clear, 'Failed': dsconf.severity }.get(valuemap[component]['LastRunOutcome'], ZenEventClasses.Info) msg = 'LastRunOutcome for job "{}": {} at {}'.format( component, valuemap[component]['LastRunOutcome'], valuemap[component]['LastRunDate']) collectedResults.events.append({ 'eventClass': eventClass, 'severity': currentstate, 'eventClassKey': 'winrsCollection MSSQLJob', 'eventKey': dsconf.eventKey if dsconf.eventKey else self.key, 'summary': msg, 'device': dsconf.device, 'component': dsconf.component }) except Exception: msg = 'Missing or no data returned when querying job "{}"'.format( component) collectedResults.events.append({ 'eventClass': eventClass, 'severity': dsconf.severity, 'eventClassKey': 'winrsCollection MSSQLJob', 'eventKey': dsconf.eventKey, 'summary': msg, 'device': dsconf.device, 'component': dsconf.component }) return collectedResults
def collect(self, device, log): self.log = log # Check if the device is a cluster device. isCluster = True if 'Microsoft/Cluster' in device.getDeviceClassName \ else False dbinstance = prepare_zDBInstances(device.zDBInstances) username = device.windows_user password = device.windows_password dblogins = {} eventmessage = 'Error parsing zDBInstances' try: dbinstance = json.loads(dbinstance) users = [el.get('user') for el in filter(None, dbinstance)] if ''.join(users): for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=el.get('user') if el.get('user') else username, password=el.get('passwd') if el.get('passwd') else password, login_as_user=False if el.get('user') else True) else: for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict(username=username, password=password, login_as_user=True) results = {'clear': eventmessage} except (ValueError, TypeError, IndexError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) conn_info = conn_info._replace(timeout=device.zCollectorClientTimeout - 5) winrs = SQLCommander(conn_info) dbinstances = winrs.get_instances_names(isCluster) instances = yield dbinstances maps = {} if not instances: self.log.info('{}: No output while getting instance names.' ' zWinRMEnvelopeSize may not be large enough.' ' Increase the size and try again.'.format( self.name())) defer.returnValue(maps) maps['errors'] = {} self.log.debug( 'WinMSSQL modeler get_instances_names results: {}'.format( instances)) instance_oms = [] database_oms = [] backup_oms = [] jobs_oms = [] server_config = {} sqlhostname = '' for serverconfig in instances.stdout: key, value = serverconfig.split(':', 1) if key == 'instances': serverlist = {} else: serverlist = [] if not value: continue if key in server_config: serverlist = server_config[key] if key == 'instances': instance_version = value.split(':') if len(instance_version) > 1: serverlist[''.join( instance_version[:-1]).strip()] = ''.join( instance_version[-1:]).strip() else: serverlist[value.strip()] = '0' else: serverlist.append(value.strip()) server_config[key] = serverlist if not server_config.get('instances'): eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] # Set value for device sqlhostname property device_om = ObjectMap() device_om.sqlhostname = sqlhostname for instance, version in server_config['instances'].items(): owner_node = '' # Leave empty for local databases. # For cluster device, create a new a connection to each node, # which owns network instances. if isCluster: try: owner_node, sql_server, instance = instance.split('\\') device.windows_servername = owner_node.strip() conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) except ValueError: self.log.error( 'Owner node for DB Instance {0} was not found'.format( instance)) continue if instance not in dblogins: self.log.info( "DB Instance {0} found but was not set in zDBInstances. " "Using default credentials.".format(instance)) instance_title = instance if instance == 'MSSQLSERVER': instance_title = sqlserver = sqlhostname else: sqlserver = '{0}\{1}'.format(sqlhostname, instance) if isCluster: if instance == 'MSSQLSERVER': instance_title = sqlserver = sql_server.strip() else: sqlserver = '{0}\{1}'.format(sql_server.strip(), instance) om_instance = ObjectMap() om_instance.id = self.prepId(instance_title) if instance == 'MSSQLSERVER': om_instance.perfmon_instance = 'SQLServer' om_instance.title = '{}(MSSQLSERVER)'.format(instance_title) else: om_instance.perfmon_instance = 'MSSQL${}'.format(instance) om_instance.title = instance_title om_instance.instancename = instance om_instance.sql_server_version = version om_instance.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) instance_oms.append(om_instance) sqlConnection = [] # Look for specific instance creds first try: sqlusername = dblogins[instance]['username'] sqlpassword = dblogins[instance]['password'] login_as_user = dblogins[instance]['login_as_user'] except KeyError: # Try default MSSQLSERVER creds try: sqlusername = dblogins['MSSQLSERVER']['username'] sqlpassword = dblogins['MSSQLSERVER']['password'] login_as_user = dblogins['MSSQLSERVER']['login_as_user'] except KeyError: # Use windows auth sqlusername = username sqlpassword = password login_as_user = True # DB Connection Object sqlConnection.append( "$con = new-object " "('Microsoft.SqlServer.Management.Common.ServerConnection')" "'{0}', '{1}', '{2}';".format(sqlserver, sqlusername, sqlpassword)) if login_as_user: # Login using windows credentials sqlConnection.append("$con.LoginSecure=$true;") sqlConnection.append("$con.ConnectAsUser=$true;") # Omit domain part of username sqlConnection.append("$con.ConnectAsUserName='******';".format( sqlusername.split("\\")[-1])) sqlConnection.append( "$con.ConnectAsUserPassword='******';".format(sqlpassword)) else: sqlConnection.append("$con.Connect();") # Connect to Database Server sqlConnection.append( "$server = new-object " "('Microsoft.SqlServer.Management.Smo.Server') $con;") db_sqlConnection = [] # Get database information db_sqlConnection.append('write-host "====Databases";') db_sqlConnection.append( '$server.Databases | foreach {' 'write-host \"Name---\" $_,' '\"`tVersion---\" $_.Version,' '\"`tIsAccessible---\" $_.IsAccessible,' '\"`tID---\" $_.ID,' '\"`tOwner---\" $_.Owner,' '\"`tLastBackupDate---\" $_.LastBackupDate,' '\"`tCollation---\" $_.Collation,' '\"`tCreateDate---\" $_.CreateDate,' '\"`tDefaultFileGroup---\" $_.DefaultFileGroup,' '\"`tPrimaryFilePath---\" $_.PrimaryFilePath,' '\"`tLastLogBackupDate---\" $_.LastLogBackupDate,' '\"`tSystemObject---\" $_.IsSystemObject,' '\"`tRecoveryModel---\" $_.DatabaseOptions.RecoveryModel' '};') # Get SQL Backup Jobs information backup_sqlConnection = [] backup_sqlConnection.append('write-host "====Backups";') # Get database information backup_sqlConnection.append( 'try{$server.BackupDevices | foreach {' 'write-host \"Name---\" $_.Name,' '\"`tDeviceType---\" $_.BackupDeviceType,' '\"`tPhysicalLocation---\" $_.PhysicalLocation,' '\"`tStatus---\" $_.State' '}}catch{ continue };') # Get SQL Jobs information job_sqlConnection = [] job_sqlConnection.append('write-host "====Jobs";') job_sqlConnection.append("try {") job_sqlConnection.append("$server.JobServer.Jobs | foreach {") job_sqlConnection.append('write-host \"job_jobname---\" $_.Name,') job_sqlConnection.append('\"job_enabled---\" $_.IsEnabled,') job_sqlConnection.append('\"job_jobid---\" $_.JobID,') job_sqlConnection.append('\"job_description---\" $_.Description,') job_sqlConnection.append('\"job_datecreated---\" $_.DateCreated,') job_sqlConnection.append('\"job_username---\" $_.OwnerLoginName') job_sqlConnection.append("}}catch { continue; }") buffer_size = [ '$Host.UI.RawUI.BufferSize = New-Object ' 'Management.Automation.Host.Size (4096, 512);' ] instance_info = yield winrs.run_command( ''.join(buffer_size + getSQLAssembly( int(om_instance.sql_server_version.split('.')[0])) + sqlConnection + db_sqlConnection + backup_sqlConnection + job_sqlConnection)) self.log.debug( 'Modeling databases, backups, jobs results: {}'.format( instance_info)) check_username(instance_info, instance, log) maps['errors'][om_instance.id] = instance_info.stderr stdout = filter_sql_stdout(instance_info.stdout) try: db_index = stdout.index('====Databases') except ValueError: db_index = None try: backup_index = stdout.index('====Backups') except ValueError: backup_index = None try: job_index = stdout.index('====Jobs') except ValueError: job_index = None if db_index is not None and backup_index is not None: for stdout_line in stdout[db_index + 1:backup_index]: if stdout_line == 'assembly load error': break om_database = self.get_db_om(om_instance, instance, owner_node, sqlserver, stdout_line) if om_database: database_oms.append(om_database) if backup_index is not None and job_index is not None: for stdout_line in stdout[backup_index + 1:job_index]: om_backup = self.get_backup_om(om_instance, instance, stdout_line) if om_backup: backup_oms.append(om_backup) if job_index is not None: job_line = '' for stdout_line in stdout[job_index + 1:]: # account for newlines in description if not job_line: job_line = stdout_line else: job_line = '\n'.join((job_line, stdout_line)) if 'job_username---' not in stdout_line: continue om_job = self.get_job_om(device, sqlserver, om_instance, owner_node, job_line) if om_job: jobs_oms.append(om_job) job_line = '' maps['clear'] = eventmessage maps['databases'] = database_oms maps['instances'] = instance_oms maps['backups'] = backup_oms maps['jobs'] = jobs_oms maps['device'] = device_om defer.returnValue(maps)
def collect(self, device, log): # Check if the device is a cluster device. isCluster = True if 'Microsoft/Cluster' in device.getDeviceClassName \ else False dbinstance = prepare_zDBInstances(device.zDBInstances) username = device.windows_user password = device.windows_password login_as_user = False dblogins = {} eventmessage = 'Error parsing zDBInstances' try: dbinstance = json.loads(dbinstance) users = [el.get('user') for el in filter(None, dbinstance)] if ''.join(users): for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=el.get('user'), password=el.get('passwd')) else: login_as_user = True for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict(username=username, password=password) results = {'clear': eventmessage} except (ValueError, TypeError, IndexError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) dbinstances = winrs.get_instances_names(isCluster) instances = yield dbinstances maps = {} instance_oms = [] database_oms = [] backup_oms = [] jobs_oms = [] server_config = {} sqlhostname = '' for serverconfig in instances.stdout: key, value = serverconfig.split(':', 1) serverlist = [] if key in server_config: serverlist = server_config[key] serverlist.append(value.strip()) server_config[key] = serverlist else: serverlist.append(value.strip()) server_config[key] = serverlist if not server_config.get('instances'): eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] # Set value for device sqlhostname property device_om = ObjectMap() device_om.sqlhostname = sqlhostname for instance in server_config['instances']: owner_node = '' # Leave empty for local databases. # For cluster device, create a new a connection to each node, # which owns network instances. if isCluster: try: owner_node, sql_server, instance = instance.split('\\') device.windows_servername = owner_node.strip() conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) except ValueError: log.error( 'Owner node for DB Instance {0} was not found'.format( instance)) continue if instance not in dblogins: log.info( "DB Instance {0} found but was not set in zDBInstances". format(instance)) continue om_instance = ObjectMap() om_instance.id = self.prepId(instance) om_instance.title = instance om_instance.instancename = instance instance_oms.append(om_instance) if instance in dblogins: sqlConnection = [] if instance == 'MSSQLSERVER': sqlserver = sqlhostname else: sqlserver = '{0}\{1}'.format(sqlhostname, instance) if isCluster: sqlserver = '{0}\{1}'.format(sql_server.strip(), instance) sqlusername = dblogins[instance]['username'] sqlpassword = dblogins[instance]['password'] # DB Connection Object sqlConnection.append("$con = new-object " \ "('Microsoft.SqlServer.Management.Common.ServerConnection')" \ "'{0}', '{1}', '{2}';".format(sqlserver, sqlusername, sqlpassword)) if login_as_user: log.debug("Windows auth %s / %s" % (sqlusername, sqlpassword)) # Login using windows credentials sqlConnection.append("$con.LoginSecure=$true;") sqlConnection.append("$con.ConnectAsUser=$true;") # Omit domain part of username sqlConnection.append( "$con.ConnectAsUserName='******';".format( sqlusername.split("\\")[-1])) sqlConnection.append( "$con.ConnectAsUserPassword='******';".format( sqlpassword)) else: log.debug("DB auth %s / %s" % (sqlusername, sqlpassword)) sqlConnection.append("$con.Connect();") # Connect to Database Server sqlConnection.append("$server = new-object " \ "('Microsoft.SqlServer.Management.Smo.Server') $con;") db_sqlConnection = [] # Get database information db_sqlConnection.append('$server.Databases | foreach {' \ 'write-host \"Name---\" $_,' \ '\"`tVersion---\" $_.Version,' \ '\"`tIsAccessible---\" $_.IsAccessible,' \ '\"`tID---\" $_.ID,' \ '\"`tOwner---\" $_.Owner,' \ '\"`tLastBackupDate---\" $_.LastBackupDate,'\ '\"`tCollation---\" $_.Collation,'\ '\"`tCreateDate---\" $_.CreateDate,'\ '\"`tDefaultFileGroup---\" $_.DefaultFileGroup,'\ '\"`tPrimaryFilePath---\" $_.PrimaryFilePath,'\ '\"`tLastLogBackupDate---\" $_.LastLogBackupDate' \ '};') databases = yield winrs.run_command( ''.join(getSQLAssembly() + sqlConnection + db_sqlConnection)) check_username(databases, instance, log) for dbobj in filter_sql_stdout(databases.stdout): if dbobj == 'assembly load error': continue db = dbobj.split('\t') dbdict = {} for dbitem in db: try: key, value = dbitem.split('---') dbdict[key.lower()] = value.strip() except (ValueError): log.info( 'Error parsing returned values : {0}'.format( dbitem)) lastlogbackupdate = None if ('lastlogbackupdate' in dbdict) \ and (dbdict['lastlogbackupdate'][:8] != '1/1/0001'): lastlogbackupdate = dbdict['lastlogbackupdate'] lastbackupdate = None if ('lastbackupdate' in dbdict) \ and (dbdict['lastbackupdate'][:8] != '1/1/0001'): lastbackupdate = dbdict['lastbackupdate'] if ('id' in dbdict): om_database = ObjectMap() om_database.id = self.prepId(instance + dbdict['id']) om_database.title = dbdict['name'][1:-1] om_database.instancename = om_instance.id om_database.version = dbdict['version'] om_database.owner = dbdict['owner'] om_database.lastbackupdate = lastbackupdate om_database.lastlogbackupdate = lastlogbackupdate om_database.isaccessible = dbdict['isaccessible'] om_database.collation = dbdict['collation'] om_database.createdate = str(dbdict['createdate']) om_database.defaultfilegroup = dbdict[ 'defaultfilegroup'] om_database.primaryfilepath = dbdict['primaryfilepath'] om_database.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) database_oms.append(om_database) # Get SQL Backup Jobs information backup_sqlConnection = [] # Get database information backup_sqlConnection.append('$server.BackupDevices | foreach {' \ 'write-host \"Name---\" $_.Name,' \ '\"`tDeviceType---\" $_.BackupDeviceType,' \ '\"`tPhysicalLocation---\" $_.PhysicalLocation,' \ '\"`tStatus---\" $_.State' \ '};') backuplist = winrs.run_command( ''.join(getSQLAssembly() + sqlConnection + backup_sqlConnection)) backups = yield backuplist for backupobj in filter_sql_stdout(backups.stdout): backup = backupobj.split('\t') backupdict = {} for backupitem in backup: key, value = backupitem.split('---') backupdict[key.lower()] = value.strip() om_backup = ObjectMap() om_backup.id = self.prepId(instance + backupdict['name']) om_backup.title = backupdict['name'] om_backup.devicetype = backupdict['devicetype'] om_backup.physicallocation = backupdict['physicallocation'] om_backup.status = backupdict['status'] om_backup.instancename = om_instance.id backup_oms.append(om_backup) # Get SQL Jobs information jobsquery = ( "select s.name as jobname, s.job_id as jobid, " "s.enabled as enabled, s.date_created as datecreated, " # Replace each new line with a space in description. "replace(replace(s.description, char(13), char(32)), " "char(10), char(32)) as description, " "l.name as username from msdb..sysjobs s left join " "master.sys.syslogins l on s.owner_sid = l.sid") job_sqlConnection = [] job_sqlConnection.append("$db = $server.Databases[0];") job_sqlConnection.append( "$ds = $db.ExecuteWithResults('{0}');".format(jobsquery)) job_sqlConnection.append('$ds.Tables | Format-List;') jobslist = winrs.run_command( ''.join(getSQLAssembly() + sqlConnection + job_sqlConnection)) jobs = yield jobslist for job in filter_sql_stdout(jobs.stdout): # Make sure that the job description length does not go # beyond the buffer size (4096 characters). if ':' not in job: continue key, value = job.split(':', 1) if key.strip() == 'jobname': #New Job Record om_jobs = ObjectMap() om_jobs.instancename = om_instance.id om_jobs.title = value.strip() else: if key.strip() == 'jobid': om_jobs.jobid = value.strip() om_jobs.id = self.prepId(om_jobs.jobid) elif key.strip() == 'enabled': om_jobs.enabled = 'Yes'\ if value.strip() == '1' else 'No' elif key.strip() == 'description': om_jobs.description = value.strip() elif key.strip() == 'datecreated': om_jobs.datecreated = str(value) elif key.strip() == 'username': om_jobs.username = value.strip() jobs_oms.append(om_jobs) maps['clear'] = eventmessage maps['databases'] = database_oms maps['instances'] = instance_oms maps['backups'] = backup_oms maps['jobs'] = jobs_oms maps['device'] = device_om defer.returnValue(maps)
def collect(self, device, log): # Check if the device is a cluster device. isCluster = True if 'Microsoft/Cluster' in device.getDeviceClassName \ else False dbinstance = prepare_zDBInstances(device.zDBInstances) username = device.windows_user password = device.windows_password dblogins = {} eventmessage = 'Error parsing zDBInstances' try: dbinstance = json.loads(dbinstance) users = [el.get('user') for el in filter(None, dbinstance)] if ''.join(users): for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=el.get('user') if el.get('user') else username, password=el.get('passwd') if el.get('passwd') else password, login_as_user=False if el.get('user') else True ) else: for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=username, password=password, login_as_user=True ) results = {'clear': eventmessage} except (ValueError, TypeError, IndexError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) dbinstances = winrs.get_instances_names(isCluster) instances = yield dbinstances maps = {} instance_oms = [] database_oms = [] backup_oms = [] jobs_oms = [] server_config = {} sqlhostname = '' for serverconfig in instances.stdout: key, value = serverconfig.split(':', 1) serverlist = [] if not value: continue if key in server_config: serverlist = server_config[key] serverlist.append(value.strip()) server_config[key] = serverlist else: serverlist.append(value.strip()) server_config[key] = serverlist if not server_config.get('instances'): eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] # Set value for device sqlhostname property device_om = ObjectMap() device_om.sqlhostname = sqlhostname for instance in server_config['instances']: #clear_inst = prepare_instance(instance) owner_node = '' # Leave empty for local databases. # For cluster device, create a new a connection to each node, # which owns network instances. if isCluster: try: owner_node, sql_server, instance = instance.split('\\') device.windows_servername = owner_node.strip() conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) except ValueError: log.error('Owner node for DB Instance {0} was not found'.format( instance)) continue if instance not in dblogins: log.info("DB Instance {0} found but was not set in zDBInstances. " \ "Using default credentials.".format(instance)) instance_title = instance if instance == 'MSSQLSERVER': instance_title = sqlserver = sqlhostname else: sqlserver = '{0}\{1}'.format(sqlhostname, instance) if isCluster: if instance == 'MSSQLSERVER': instance_title = sqlserver = sql_server.strip() else: sqlserver = '{0}\{1}'.format(sql_server.strip(), instance) om_instance = ObjectMap() om_instance.id = self.prepId(instance_title) om_instance.title = instance_title om_instance.instancename = instance_title om_instance.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) instance_oms.append(om_instance) sqlConnection = [] # Look for specific instance creds first try: sqlusername = dblogins[instance]['username'] sqlpassword = dblogins[instance]['password'] login_as_user = dblogins[instance]['login_as_user'] except KeyError: # Try default MSSQLSERVER creds try: sqlusername = dblogins['MSSQLSERVER']['username'] sqlpassword = dblogins['MSSQLSERVER']['password'] login_as_user = dblogins['MSSQLSERVER']['login_as_user'] except KeyError: # Use windows auth sqlusername = username sqlpassword = password login_as_user = True # DB Connection Object sqlConnection.append("$con = new-object " \ "('Microsoft.SqlServer.Management.Common.ServerConnection')" \ "'{0}', '{1}', '{2}';".format(sqlserver, sqlusername, sqlpassword)) if login_as_user: log.debug("Windows auth %s / %s" % (sqlusername, sqlpassword)) # Login using windows credentials sqlConnection.append("$con.LoginSecure=$true;") sqlConnection.append("$con.ConnectAsUser=$true;") # Omit domain part of username sqlConnection.append("$con.ConnectAsUserName='******';".format(sqlusername.split("\\")[-1])) sqlConnection.append("$con.ConnectAsUserPassword='******';".format(sqlpassword)) else: log.debug("DB auth %s / %s" % (sqlusername, sqlpassword)) sqlConnection.append("$con.Connect();") # Connect to Database Server sqlConnection.append("$server = new-object " \ "('Microsoft.SqlServer.Management.Smo.Server') $con;") db_sqlConnection = [] # Get database information db_sqlConnection.append('write-host "====Databases";') db_sqlConnection.append('$server.Databases | foreach {' \ 'write-host \"Name---\" $_,' \ '\"`tVersion---\" $_.Version,' \ '\"`tIsAccessible---\" $_.IsAccessible,' \ '\"`tID---\" $_.ID,' \ '\"`tOwner---\" $_.Owner,' \ '\"`tLastBackupDate---\" $_.LastBackupDate,'\ '\"`tCollation---\" $_.Collation,'\ '\"`tCreateDate---\" $_.CreateDate,'\ '\"`tDefaultFileGroup---\" $_.DefaultFileGroup,'\ '\"`tPrimaryFilePath---\" $_.PrimaryFilePath,'\ '\"`tLastLogBackupDate---\" $_.LastLogBackupDate,' \ '\"`tSystemObject---\" $_.IsSystemObject,' \ '\"`tRecoveryModel---\" $_.DatabaseOptions.RecoveryModel' \ '};') # Get SQL Backup Jobs information backup_sqlConnection = [] backup_sqlConnection.append('write-host "====Backups";') # Get database information backup_sqlConnection.append('$server.BackupDevices | foreach {' \ 'write-host \"Name---\" $_.Name,' \ '\"`tDeviceType---\" $_.BackupDeviceType,' \ '\"`tPhysicalLocation---\" $_.PhysicalLocation,' \ '\"`tStatus---\" $_.State' \ '};') # Get SQL Jobs information jobsquery = ( "select s.name as jobname, s.job_id as jobid, " "s.enabled as enabled, s.date_created as datecreated, " # Replace each new line with a space in description. "replace(replace(s.description, char(13), char(32)), " "char(10), char(32)) as description, " "l.name as username from msdb..sysjobs s left join " "master.sys.syslogins l on s.owner_sid = l.sid" ) job_sqlConnection = [] job_sqlConnection.append('write-host "====Jobs";') job_sqlConnection.append("$db = $server.Databases[0];") job_sqlConnection.append("$ds = $db.ExecuteWithResults('{0}');".format(jobsquery)) job_sqlConnection.append('$ds.Tables | Format-List;') instance_info = yield winrs.run_command( ''.join(getSQLAssembly() + sqlConnection + db_sqlConnection + \ backup_sqlConnection + job_sqlConnection) ) log.debug('Modeling databases, backups, jobs results: {}'.format(instance_info)) check_username(instance_info, instance, log) in_databases=False in_backups = False in_jobs = False for stdout_line in filter_sql_stdout(instance_info.stdout): if stdout_line == 'assembly load error': break if stdout_line == '====Databases': in_databases = True in_backups = False in_jobs = False continue elif stdout_line == '====Backups': in_databases = False in_backups = True in_jobs = False continue elif stdout_line == '====Jobs': in_databases = False in_backups = False in_jobs = True continue if in_databases: dbobj = stdout_line #for dbobj in filter_sql_stdout(databases.stdout): db = dbobj.split('\t') dbdict = {} for dbitem in db: try: key, value = dbitem.split('---') dbdict[key.lower()] = value.strip() except (ValueError): log.info('Error parsing returned values : {0}'.format( dbitem)) lastlogbackupdate = None if ('lastlogbackupdate' in dbdict) \ and (dbdict['lastlogbackupdate'][:8] != '1/1/0001'): lastlogbackupdate = dbdict['lastlogbackupdate'] lastbackupdate = None if ('lastbackupdate' in dbdict) \ and (dbdict['lastbackupdate'][:8] != '1/1/0001'): lastbackupdate = dbdict['lastbackupdate'] if ('id' in dbdict): om_database = ObjectMap() om_database.id = self.prepId(instance + dbdict['id']) om_database.title = dbdict['name'][1:-1] om_database.instancename = om_instance.id om_database.version = dbdict['version'] om_database.owner = dbdict['owner'] om_database.lastbackupdate = lastbackupdate om_database.lastlogbackupdate = lastlogbackupdate om_database.isaccessible = dbdict['isaccessible'] om_database.collation = dbdict['collation'] om_database.createdate = str(dbdict['createdate']) om_database.defaultfilegroup = dbdict['defaultfilegroup'] om_database.primaryfilepath = dbdict['primaryfilepath'] om_database.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) om_database.systemobject = dbdict['systemobject'] om_database.recoverymodel = dbdict['recoverymodel'] om_database.status= 'Up' if dbdict['isaccessible'] == 'True' else 'Down' database_oms.append(om_database) elif in_backups: backupobj = stdout_line backup = backupobj.split('\t') backupdict = {} for backupitem in backup: key, value = backupitem.split('---') backupdict[key.lower()] = value.strip() om_backup = ObjectMap() om_backup.id = self.prepId(instance + backupdict['name']) om_backup.title = backupdict['name'] om_backup.devicetype = backupdict['devicetype'] om_backup.physicallocation = backupdict['physicallocation'] om_backup.status = backupdict['status'] om_backup.instancename = om_instance.id backup_oms.append(om_backup) elif in_jobs: job = stdout_line # Make sure that the job description length does not go # beyond the buffer size (4096 characters). if ':' not in job: continue key, value = job.split(':', 1) if key.strip() == 'jobname': #New Job Record om_jobs = ObjectMap() om_jobs.instancename = om_instance.id om_jobs.title = value.strip() om_jobs.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) else: if key.strip() == 'jobid': om_jobs.jobid = value.strip() om_jobs.id = self.prepId(om_jobs.jobid) elif key.strip() == 'enabled': om_jobs.enabled = 'Yes'\ if value.strip() == '1' else 'No' elif key.strip() == 'description': om_jobs.description = value.strip() elif key.strip() == 'datecreated': om_jobs.datecreated = str(value) elif key.strip() == 'username': om_jobs.username = value.strip() jobs_oms.append(om_jobs) maps['clear'] = eventmessage maps['databases'] = database_oms maps['instances'] = instance_oms maps['backups'] = backup_oms maps['jobs'] = jobs_oms maps['device'] = device_om defer.returnValue(maps)
def collect(self, device, log): self.log = log # Check if the device is a cluster device. isCluster = True if 'Microsoft/Cluster' in device.getDeviceClassName \ else False dbinstance = prepare_zDBInstances(device.zDBInstances) username = device.windows_user password = device.windows_password dblogins = {} eventmessage = 'Error parsing zDBInstances' try: dbinstance = json.loads(dbinstance) users = [el.get('user') for el in filter(None, dbinstance)] if ''.join(users): for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=el.get('user') if el.get('user') else username, password=el.get('passwd') if el.get('passwd') else password, login_as_user=False if el.get('user') else True ) else: for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=username, password=password, login_as_user=True ) results = {'clear': eventmessage} except (ValueError, TypeError, IndexError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) conn_info = conn_info._replace(timeout=device.zCollectorClientTimeout - 5) winrs = SQLCommander(conn_info, log) dbinstances = winrs.get_instances_names(isCluster) instances = yield dbinstances maps = {} if not instances: log.info('{}: No output while getting instance names.' ' zWinRMEnvelopeSize may not be large enough.' ' Increase the size and try again.'.format(self.name())) defer.returnValue(maps) maps['errors'] = {} log.debug('WinMSSQL modeler get_instances_names results: {}'.format(instances)) instance_oms = [] database_oms = [] backup_oms = [] jobs_oms = [] server_config = {} sqlhostname = '' for serverconfig in instances.stdout: key, value = serverconfig.split(':', 1) if key == 'instances': serverlist = {} else: serverlist = [] if not value: continue if key in server_config: serverlist = server_config[key] if key == 'instances': instance_version = value.split(':') if len(instance_version) > 1: serverlist[''.join(instance_version[:-1]).strip()] = ''.join(instance_version[-1:]).strip() else: serverlist[value.strip()] = '0' else: serverlist.append(value.strip()) server_config[key] = serverlist if not server_config.get('instances'): eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] # Set value for device sqlhostname property device_om = ObjectMap() device_om.sqlhostname = sqlhostname for instance, version in server_config['instances'].items(): owner_node = '' # Leave empty for local databases. ip_address = None # Leave empty for local databases. # For cluster device, create a new a connection to each node, # which owns network instances. if isCluster: try: owner_node, ip_address, sql_server, instance = instance.split('\\') owner_node = owner_node.strip() sql_server = sql_server.strip() instance = instance.strip() ip_address = ip_address.strip() conn_info = conn_info._replace(hostname=owner_node) if ip_address: conn_info = conn_info._replace(ipaddress=ip_address) else: conn_info = conn_info._replace(ipaddress=owner_node) winrs = SQLCommander(conn_info, log) except ValueError: log.error('Malformed data returned for instance {}'.format( instance)) continue if instance not in dblogins: log.debug("DB Instance {0} found but was not set in zDBInstances. " "Using default credentials.".format(instance)) instance_title = instance if instance == 'MSSQLSERVER': instance_title = sqlserver = sqlhostname else: sqlserver = '{0}\{1}'.format(sqlhostname, instance) if isCluster: if instance == 'MSSQLSERVER': instance_title = sqlserver = sql_server else: sqlserver = '{0}\{1}'.format(sql_server, instance) om_instance = ObjectMap() om_instance.id = self.prepId(instance_title) if instance == 'MSSQLSERVER': om_instance.perfmon_instance = 'SQLServer' om_instance.title = '{}(MSSQLSERVER)'.format(instance_title) else: om_instance.perfmon_instance = 'MSSQL${}'.format(instance) om_instance.title = instance_title om_instance.instancename = instance om_instance.sql_server_version = version om_instance.cluster_node_server = '{0}//{1}'.format( owner_node, sqlserver) om_instance.owner_node_ip = ip_address instance_oms.append(om_instance) # Look for specific instance creds first try: sqlusername = dblogins[instance]['username'] sqlpassword = dblogins[instance]['password'] login_as_user = dblogins[instance]['login_as_user'] except KeyError: # Try default MSSQLSERVER creds try: sqlusername = dblogins['MSSQLSERVER']['username'] sqlpassword = dblogins['MSSQLSERVER']['password'] login_as_user = dblogins['MSSQLSERVER']['login_as_user'] except KeyError: # Use windows auth sqlusername = username sqlpassword = password login_as_user = True sql_version = int(om_instance.sql_server_version.split('.')[0]) sqlConnection = SqlConnection(sqlserver, sqlusername, sqlpassword, login_as_user, sql_version).sqlConnection db_sqlConnection = [] # Get database information # smo optimization for faster loading db_sqlConnection.append("$ob = New-Object Microsoft.SqlServer.Management.Smo.Database;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") db_sqlConnection.append('write-host "====Databases";') db_sqlConnection.append('$server.Databases | foreach {' 'write-host \"Name---\" $_,' '\"`tVersion---\" $_.Version,' '\"`tIsAccessible---\" $_.IsAccessible,' '\"`tID---\" $_.ID,' '\"`tOwner---\" $_.Owner,' '\"`tLastBackupDate---\" $_.LastBackupDate,' '\"`tCollation---\" $_.Collation,' '\"`tCreateDate---\" $_.CreateDate,' '\"`tDefaultFileGroup---\" $_.DefaultFileGroup,' '\"`tPrimaryFilePath---\" $_.PrimaryFilePath,' '\"`tLastLogBackupDate---\" $_.LastLogBackupDate,' '\"`tSystemObject---\" $_.IsSystemObject,' '\"`tRecoveryModel---\" $_.DatabaseOptions.RecoveryModel' '};') # Get SQL Backup Jobs information backup_sqlConnection = [] # smo optimization for faster loading backup_sqlConnection.append("$ob = New-Object Microsoft.SqlServer.Management.Smo.BackupDevice;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") backup_sqlConnection.append('write-host "====Backups";') # Get database information backup_sqlConnection.append('try{$server.BackupDevices | foreach {' 'write-host \"Name---\" $_.Name,' '\"`tDeviceType---\" $_.BackupDeviceType,' '\"`tPhysicalLocation---\" $_.PhysicalLocation,' '\"`tStatus---\" $_.State' '}}catch{ continue };') # Get SQL Jobs information job_sqlConnection = [] # smo optimization for faster loading job_sqlConnection.append("$ob = New-Object Microsoft.SqlServer.Management.Smo.Agent.Job;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") job_sqlConnection.append('write-host "====Jobs";') job_sqlConnection.append("try {") job_sqlConnection.append("$server.JobServer.Jobs | foreach {") job_sqlConnection.append('write-host \"job_jobname---\" $_.Name,') job_sqlConnection.append('\"job_enabled---\" $_.IsEnabled,') job_sqlConnection.append('\"job_jobid---\" $_.JobID,') job_sqlConnection.append('\"job_description---\" $_.Description,') job_sqlConnection.append('\"job_datecreated---\" $_.DateCreated,') job_sqlConnection.append('\"job_username---\" $_.OwnerLoginName') job_sqlConnection.append("}}catch { continue; }") version_sqlConnection = [] if isCluster: version_sqlConnection.append("write-host \"====Version\";") version_sqlConnection.append("$dbmaster = $server.Databases['master'];") version_sqlConnection.append('$query = \\"SELECT SERVERPROPERTY(\'productversion\') as version\\";') version_sqlConnection.append("$res = $dbmaster.ExecuteWithResults($query);") version_sqlConnection.append("write-host $res.tables[0].rows[0].version;") instance_info = yield winrs.run_command( ''.join(getSQLAssembly(int(om_instance.sql_server_version.split('.')[0])) + sqlConnection + db_sqlConnection + backup_sqlConnection + job_sqlConnection + version_sqlConnection) ) log.debug('Modeling databases, backups, jobs results: {}'.format(instance_info)) check_username(instance_info, instance, log) maps['errors'][om_instance.id] = instance_info.stderr stdout = filter_sql_stdout(instance_info.stdout) try: db_index = stdout.index('====Databases') except ValueError: db_index = None try: backup_index = stdout.index('====Backups') except ValueError: backup_index = None try: job_index = stdout.index('====Jobs') except ValueError: job_index = None try: version_index = stdout.index('====Version') except ValueError: version_index = None if db_index is not None and backup_index is not None: for stdout_line in stdout[db_index + 1:backup_index]: if stdout_line == 'assembly load error': break om_database = self.get_db_om(om_instance, instance, owner_node, sqlserver, stdout_line) if om_database: database_oms.append(om_database) if backup_index is not None and job_index is not None: for stdout_line in stdout[backup_index + 1:job_index]: om_backup = self.get_backup_om(om_instance, instance, stdout_line) if om_backup: backup_oms.append(om_backup) if job_index is not None: job_line = '' for stdout_line in stdout[job_index + 1:]: # account for newlines in description if not job_line: job_line = stdout_line else: job_line = '\n'.join((job_line, stdout_line)) if 'job_username---' not in stdout_line: continue om_job = self.get_job_om(device, sqlserver, om_instance, owner_node, job_line) if om_job: jobs_oms.append(om_job) job_line = '' if isCluster and version_index is not None: try: om_instance.sql_server_version = stdout[version_index + 1].strip() except Exception: log.debug('Version not found for om_instance %s', om_instance.id) maps['clear'] = eventmessage maps['databases'] = database_oms maps['instances'] = instance_oms maps['backups'] = backup_oms maps['jobs'] = jobs_oms maps['device'] = device_om defer.returnValue(maps)
def collect(self, device, log): # Check if the device is a cluster device. isCluster = True if 'Microsoft/Cluster' in device.getDeviceClassName \ else False dbinstance = prepare_zDBInstances(device.zDBInstances) username = device.windows_user password = device.windows_password dblogins = {} eventmessage = 'Error parsing zDBInstances' try: dbinstance = json.loads(dbinstance) users = [el.get('user') for el in filter(None, dbinstance)] if ''.join(users): for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict( username=el.get('user') if el.get('user') else username, password=el.get('passwd') if el.get('passwd') else password, login_as_user=False if el.get('user') else True) else: for el in filter(None, dbinstance): dblogins[el.get('instance')] = dict(username=username, password=password, login_as_user=True) results = {'clear': eventmessage} except (ValueError, TypeError, IndexError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) dbinstances = winrs.get_instances_names(isCluster) instances = yield dbinstances maps = {} instance_oms = [] database_oms = [] backup_oms = [] jobs_oms = [] server_config = {} sqlhostname = '' for serverconfig in instances.stdout: key, value = serverconfig.split(':', 1) if key == 'instances': serverlist = {} else: serverlist = [] if not value: continue if key in server_config: serverlist = server_config[key] if key == 'instances': instance_version = value.split(':') serverlist[''.join(instance_version[:-1]).strip()] = ''.join( instance_version[-1:]).strip() else: serverlist.append(value.strip()) server_config[key] = serverlist if not server_config.get('instances'): eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] # Set value for device sqlhostname property device_om = ObjectMap() device_om.sqlhostname = sqlhostname for instance, version in server_config['instances'].items(): owner_node = '' # Leave empty for local databases. # For cluster device, create a new a connection to each node, # which owns network instances. if isCluster: try: owner_node, sql_server, instance = instance.split('\\') device.windows_servername = owner_node.strip() conn_info = self.conn_info(device) winrs = SQLCommander(conn_info) except ValueError: log.error( 'Owner node for DB Instance {0} was not found'.format( instance)) continue if instance not in dblogins: log.info( "DB Instance {0} found but was not set in zDBInstances. " "Using default credentials.".format(instance)) instance_title = instance if instance == 'MSSQLSERVER': instance_title = sqlserver = sqlhostname else: sqlserver = '{0}\{1}'.format(sqlhostname, instance) if isCluster: if instance == 'MSSQLSERVER': instance_title = sqlserver = sql_server.strip() else: sqlserver = '{0}\{1}'.format(sql_server.strip(), instance) om_instance = ObjectMap() om_instance.id = self.prepId(instance_title) om_instance.title = instance_title om_instance.instancename = instance_title om_instance.sql_server_version = version om_instance.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) instance_oms.append(om_instance) sqlConnection = [] # Look for specific instance creds first try: sqlusername = dblogins[instance]['username'] sqlpassword = dblogins[instance]['password'] login_as_user = dblogins[instance]['login_as_user'] except KeyError: # Try default MSSQLSERVER creds try: sqlusername = dblogins['MSSQLSERVER']['username'] sqlpassword = dblogins['MSSQLSERVER']['password'] login_as_user = dblogins['MSSQLSERVER']['login_as_user'] except KeyError: # Use windows auth sqlusername = username sqlpassword = password login_as_user = True # DB Connection Object sqlConnection.append("$con = new-object " \ "('Microsoft.SqlServer.Management.Common.ServerConnection')" \ "'{0}', '{1}', '{2}';".format(sqlserver, sqlusername, sqlpassword)) if login_as_user: # Login using windows credentials sqlConnection.append("$con.LoginSecure=$true;") sqlConnection.append("$con.ConnectAsUser=$true;") # Omit domain part of username sqlConnection.append("$con.ConnectAsUserName='******';".format( sqlusername.split("\\")[-1])) sqlConnection.append( "$con.ConnectAsUserPassword='******';".format(sqlpassword)) else: sqlConnection.append("$con.Connect();") # Connect to Database Server sqlConnection.append("$server = new-object " \ "('Microsoft.SqlServer.Management.Smo.Server') $con;") db_sqlConnection = [] # Get database information db_sqlConnection.append('write-host "====Databases";') db_sqlConnection.append('$server.Databases | foreach {' \ 'write-host \"Name---\" $_,' \ '\"`tVersion---\" $_.Version,' \ '\"`tIsAccessible---\" $_.IsAccessible,' \ '\"`tID---\" $_.ID,' \ '\"`tOwner---\" $_.Owner,' \ '\"`tLastBackupDate---\" $_.LastBackupDate,'\ '\"`tCollation---\" $_.Collation,'\ '\"`tCreateDate---\" $_.CreateDate,'\ '\"`tDefaultFileGroup---\" $_.DefaultFileGroup,'\ '\"`tPrimaryFilePath---\" $_.PrimaryFilePath,'\ '\"`tLastLogBackupDate---\" $_.LastLogBackupDate,' \ '\"`tSystemObject---\" $_.IsSystemObject,' \ '\"`tRecoveryModel---\" $_.DatabaseOptions.RecoveryModel' \ '};') # Get SQL Backup Jobs information backup_sqlConnection = [] backup_sqlConnection.append('write-host "====Backups";') # Get database information backup_sqlConnection.append('$server.BackupDevices | foreach {' \ 'write-host \"Name---\" $_.Name,' \ '\"`tDeviceType---\" $_.BackupDeviceType,' \ '\"`tPhysicalLocation---\" $_.PhysicalLocation,' \ '\"`tStatus---\" $_.State' \ '};') # Get SQL Jobs information job_sqlConnection = [] job_sqlConnection.append('write-host "====Jobs";') job_sqlConnection.append("if ($server.JobServer -ne $null) {") job_sqlConnection.append( "foreach ($job in $server.JobServer.Jobs) {") job_sqlConnection.append("write-host 'jobname:'$job.Name;") job_sqlConnection.append("write-host 'enabled:'$job.IsEnabled;") job_sqlConnection.append("write-host 'jobid:'$job.JobID;") job_sqlConnection.append( "write-host 'description:'$job.Description;") job_sqlConnection.append( "write-host 'datecreated:'$job.DateCreated;") job_sqlConnection.append( "write-host 'username:'******''.join( getSQLAssembly( int(om_instance.sql_server_version.split('.')[0])) + sqlConnection + db_sqlConnection + backup_sqlConnection + job_sqlConnection)) log.debug('Modeling databases, backups, jobs results: {}'.format( instance_info)) check_username(instance_info, instance, log) in_databases = False in_backups = False in_jobs = False for stdout_line in filter_sql_stdout(instance_info.stdout): if stdout_line == 'assembly load error': break if stdout_line == '====Databases': in_databases = True in_backups = False in_jobs = False continue elif stdout_line == '====Backups': in_databases = False in_backups = True in_jobs = False continue elif stdout_line == '====Jobs': in_databases = False in_backups = False in_jobs = True continue if in_databases: dbobj = stdout_line #for dbobj in filter_sql_stdout(databases.stdout): db = dbobj.split('\t') dbdict = {} for dbitem in db: try: key, value = dbitem.split('---') dbdict[key.lower()] = value.strip() except (ValueError): log.info( 'Error parsing returned values : {0}'.format( dbitem)) lastlogbackupdate = None if ('lastlogbackupdate' in dbdict) \ and (dbdict['lastlogbackupdate'][:8] != '1/1/0001'): lastlogbackupdate = dbdict['lastlogbackupdate'] lastbackupdate = None if ('lastbackupdate' in dbdict) \ and (dbdict['lastbackupdate'][:8] != '1/1/0001'): lastbackupdate = dbdict['lastbackupdate'] if ('id' in dbdict): om_database = ObjectMap() om_database.id = self.prepId(instance + dbdict['id']) om_database.title = dbdict['name'][1:-1] om_database.instancename = om_instance.id om_database.version = dbdict['version'] om_database.owner = dbdict['owner'] om_database.lastbackupdate = lastbackupdate om_database.lastlogbackupdate = lastlogbackupdate om_database.isaccessible = dbdict['isaccessible'] om_database.collation = dbdict['collation'] om_database.createdate = str(dbdict['createdate']) om_database.defaultfilegroup = dbdict[ 'defaultfilegroup'] om_database.primaryfilepath = dbdict['primaryfilepath'] om_database.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) om_database.systemobject = dbdict['systemobject'] om_database.recoverymodel = dbdict['recoverymodel'] database_oms.append(om_database) elif in_backups: backupobj = stdout_line backup = backupobj.split('\t') backupdict = {} for backupitem in backup: key, value = backupitem.split('---') backupdict[key.lower()] = value.strip() om_backup = ObjectMap() om_backup.id = self.prepId(instance + backupdict['name']) om_backup.title = backupdict['name'] om_backup.devicetype = backupdict['devicetype'] om_backup.physicallocation = backupdict['physicallocation'] om_backup.status = backupdict['status'] om_backup.instancename = om_instance.id backup_oms.append(om_backup) elif in_jobs: job = stdout_line # Make sure that the job description length does not go # beyond the buffer size (4096 characters). if ':' not in job: continue key, value = job.split(':', 1) if key.strip() == 'jobname': #New Job Record om_jobs = ObjectMap() om_jobs.instancename = om_instance.id om_jobs.title = value.strip() om_jobs.cluster_node_server = '{0}//{1}'.format( owner_node.strip(), sqlserver) else: if key.strip() == 'jobid': om_jobs.jobid = value.strip() om_jobs.id = self.prepId(om_jobs.jobid) elif key.strip() == 'enabled': om_jobs.enabled = 'Yes'\ if value.strip() == 'True' else 'No' elif key.strip() == 'description': om_jobs.description = value.strip() elif key.strip() == 'datecreated': om_jobs.datecreated = str(value) elif key.strip() == 'username': om_jobs.username = value.strip() jobs_oms.append(om_jobs) maps['clear'] = eventmessage maps['databases'] = database_oms maps['instances'] = instance_oms maps['backups'] = backup_oms maps['jobs'] = jobs_oms maps['device'] = device_om defer.returnValue(maps)
def parse_result(self, dsconfs, result): log.debug('MSSQLJob results: {}'.format(result)) collectedResults = ParsedResults() if result.stderr: log.debug('MSSQL error: {0}' + ''.join(result.stderr)) if result.exit_code != 0: log.info( 'Non-zero exit code ({}) for job query status on {}' .format( result.exit_code, dsconfs[0].device)) return collectedResults # Parse values valuemap = {} jobname = 'Unknown' try: for jobline in filter_sql_stdout(result.stdout): for job in jobline.split('|'): key, value = job.split(':', 1) if key.strip() == 'job': jobname = value.strip() if jobname not in valuemap: valuemap[jobname] = {} else: valuemap[jobname][key] = value.strip() except ValueError: msg = 'Malformed data received for MSSQL Job {}'.format(jobname) collectedResults.events.append({ 'severity': ZenEventClasses.Error, 'eventClassKey': 'winrsCollection MSSQLJob', 'eventKey': dsconfs[0].eventKey if dsconfs[0].eventKey else self.key, 'summary': msg, 'device': dsconfs[0].device, 'query_results': result.stdout }) for dsconf in dsconfs: component = dsconf.params['contexttitle'] eventClass = dsconf.eventClass if dsconf.eventClass else "/Status" try: currentstate = { 'Succeeded': ZenEventClasses.Clear, 'Failed': dsconf.severity }.get(valuemap[component]['LastRunOutcome'], ZenEventClasses.Info) msg = 'LastRunOutcome for job "{}": {} at {}'.format( component, valuemap[component]['LastRunOutcome'], valuemap[component]['LastRunDate']) collectedResults.events.append({ 'eventClass': eventClass, 'severity': currentstate, 'eventClassKey': 'winrsCollection MSSQLJob', 'eventKey': dsconf.eventKey if dsconf.eventKey else self.key, 'summary': msg, 'device': dsconf.device, 'component': dsconf.component}) collectedResults.events.append({ 'severity': ZenEventClasses.Clear, 'eventClassKey': 'winrsCollectionMSSQLJob', 'eventKey': dsconf.eventKey if dsconf.eventKey else self.key, 'summary': 'Successful MSSQL Job collection', 'device': dsconf.device, 'component': dsconf.component}) except Exception: msg = 'Missing or no data returned when querying job "{}"'.format(component) collectedResults.events.append({ 'severity': dsconf.severity, 'eventClassKey': 'winrsCollectionMSSQLJob', 'eventKey': dsconf.eventKey if dsconf.eventKey else self.key, 'summary': msg, 'device': dsconf.device, 'component': dsconf.component }) return collectedResults