def build_command_line(self, sqlConnection): pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " # We should not be running this per database. Bad performance problems when there are # a lot of databases. Run script per instance counters_sqlConnection = [] counters_sqlConnection.append("if ($server.Databases -ne $null) {") counters_sqlConnection.append( "$dbMaster = $server.Databases['master'];") counters_sqlConnection.append("foreach ($db in $server.Databases){") counters_sqlConnection.append( "$db_name = '';" "$sp = $db.Name.split($([char]39)); " "if($sp.length -ge 2){ " "foreach($i in $sp){ " "if($i -ne $sp[-1]){ $db_name += $i + [char]39 + [char]39;}" "else { $db_name += $i;}" "}} else { $db_name = $db.Name;}") counters_sqlConnection.append( "$query = 'select instance_name as databasename, " "counter_name as ckey, cntr_value as cvalue from " "sys.dm_os_performance_counters where instance_name = '" " +[char]39+$db_name+[char]39;") counters_sqlConnection.append( "$ds = $dbMaster.ExecuteWithResults($query);") counters_sqlConnection.append( 'if($ds.Tables[0].rows.count -gt 0) {$ds.Tables| Format-List;}' 'Write-Host "databasename:"$db_name;' '$status = $db.Status;write-host "databasestatus:"$status;}}') script = "\"& {{{}}}\"".format( ''.join([BUFFER_SIZE] + getSQLAssembly(sqlConnection.version) + sqlConnection.sqlConnection + counters_sqlConnection)) return pscommand, script
def build_command_line(self, counters, sqlserver, sqlusername, sqlpassword, database, login_as_user): #SQL Command opening database = re.sub('[\']', '\' +[char]39 + [char]39+ \'', database) pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " sqlConnection = [] # Need to modify query where clause. # Currently all counters are retrieved for each database # 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;") counters_sqlConnection = [] counters_sqlConnection.append("$query = 'select instance_name as databasename, " \ "counter_name as ckey, cntr_value as cvalue from " \ "sys.dm_os_performance_counters where instance_name = '" \ " + [char]39 + '{0}' + [char]39;".format( database )) """ # Additional work needs to be done here to limit query ) and " \ "counter_name in ({1})".format(database, counters_args) """ counters_sqlConnection.append("if ($server.Databases -ne $null) {") counters_sqlConnection.append("$db = $server.Databases[0];") counters_sqlConnection.append("$ds = $db.ExecuteWithResults($query);") counters_sqlConnection.append("$ds.Tables | Format-List;") counters_sqlConnection.append("if($ds.Tables[0].rows.count -gt 0) {$ds.Tables| Format-List;}" \ "else { Write-Host 'databasename:{dbname}';}".replace('{dbname}', database)) counters_sqlConnection.append("}") command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + counters_sqlConnection)) return command
def build_command_line(self, sqlConnection): pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " jobs_sqlConnection = [] jobs_sqlConnection.append("if ($server.JobServer -ne $null) {") jobs_sqlConnection.append("foreach ($job in $server.JobServer.Jobs) {") jobs_sqlConnection.append("write-host 'job:'$job.Name") jobs_sqlConnection.append("'|IsEnabled:'$job.IsEnabled") jobs_sqlConnection.append("'|LastRunDate:'$job.LastRunDate") jobs_sqlConnection.append("'|LastRunOutcome:'$job.LastRunOutcome") jobs_sqlConnection.append("'|CurrentRunStatus:'$job.CurrentRunStatus;") jobs_sqlConnection.append("}}") script = "\"& {{{}}}\"".format( ''.join([BUFFER_SIZE] + getSQLAssembly(sqlConnection.version) + sqlConnection.sqlConnection + jobs_sqlConnection)) return pscommand, script
def build_command_line(self, sqlConnection): pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " # We should not be running this per database. Bad performance problems when there are # a lot of databases. Run script per instance counters_sqlConnection = [] # smo optimization for faster loading counters_sqlConnection.append( "$ob = New-Object Microsoft.SqlServer.Management.Smo.Database;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") counters_sqlConnection.append( "$ob = New-Object Microsoft.SqlServer.Management.Smo.Table;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") counters_sqlConnection.append("if ($server.Databases -ne $null) {") counters_sqlConnection.append( "$dbMaster = $server.Databases['master'];") counters_sqlConnection.append("foreach ($db in $server.Databases){") counters_sqlConnection.append( "$db_name = '';" "$sp = $db.Name.split($([char]39)); " "if($sp.length -ge 2){ " "foreach($i in $sp){ " "if($i -ne $sp[-1]){ $db_name += $i + [char]39 + [char]39;}" "else { $db_name += $i;}" "}} else { $db_name = $db.Name;}" "Write-Host '{{db_name}} :counter: databasestatus :value: {{status}}'.replace" "('{{db_name}}', $db_name).replace('{{status}}', $db.Status);}") counters_sqlConnection.append( "$query = 'select RTRIM(instance_name), " "RTRIM(counter_name), RTRIM(cntr_value) from " "sys.dm_os_performance_counters where instance_name in " "(select name from sys.databases)';") counters_sqlConnection.append( "$ds = $dbMaster.ExecuteWithResults($query);") counters_sqlConnection.append( "if($ds.Tables[0].rows.count -gt 0) {$ds.Tables[0].rows" "| % {write-host $_.Column1':counter:'$_.Column2':value:'$_.Column3;} } }" ) script = "\"& {{{}}}\"".format( ''.join([BUFFER_SIZE] + getSQLAssembly(sqlConnection.version) + sqlConnection.sqlConnection + counters_sqlConnection)) return pscommand, script
def build_command_line(self, counters, sqlserver, sqlusername, sqlpassword, database): #SQL Command opening pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " sqlConnection = [] # Need to modify query where clause. # Currently all counters are retrieved for each database # DB Connection Object sqlConnection.append("$con = new-object " \ "('Microsoft.SqlServer.Management.Common.ServerConnection')" \ "'{0}', '{1}', '{2}';".format(sqlserver, sqlusername, sqlpassword)) sqlConnection.append("$con.Connect();") # Connect to Database Server sqlConnection.append("$server = new-object " \ "('Microsoft.SqlServer.Management.Smo.Server') $con;") counters_sqlConnection = [] counters_sqlConnection.append("$query = 'select instance_name as databasename, " \ "counter_name as ckey, cntr_value as cvalue from " \ "sys.dm_os_performance_counters where instance_name = '" \ " + [char]39 + '{0}' + [char]39;".format( database )) """ # Additional work needs to be done here to limit query ) and " \ "counter_name in ({1})".format(database, counters_args) """ counters_sqlConnection.append("$db = $server.Databases[0];") counters_sqlConnection.append("$ds = $db.ExecuteWithResults($query);") counters_sqlConnection.append("$ds.Tables | Format-List;") command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + counters_sqlConnection)) return command
def build_command_line(self, instance, sqlusername, sqlpassword, login_as_user, is_cluster): pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " # We should not be running this per database. Bad performance problems when there are # a lot of databases. Run script per instance sqlConnection = [] # Need to modify query where clause. # Currently all counters are retrieved for each database # DB Connection Object sqlConnection.append("$con = new-object " \ "('Microsoft.SqlServer.Management.Common.ServerConnection')" \ "'{}', '{}', '{}';".format(instance, 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;") counters_sqlConnection = [] counters_sqlConnection.append("if ($server.Databases -ne $null) {") counters_sqlConnection.append("foreach ($db in $server.Databases){") counters_sqlConnection.append("$query = 'select instance_name as databasename, " \ "counter_name as ckey, cntr_value as cvalue from " \ "sys.dm_os_performance_counters where instance_name = '" \ " +[char]39+$db.Name+[char]39;") counters_sqlConnection.append("$ds = $db.ExecuteWithResults($query);") counters_sqlConnection.append('if($ds.Tables[0].rows.count -gt 0) {$ds.Tables| Format-List;}' \ 'else { Write-Host "databasename:"$db.Name};}') counters_sqlConnection.append("}") command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + counters_sqlConnection)) return command
def build_command_line(self, sqlConnection): pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " # We should not be running this per database. Bad performance problems when there are # a lot of databases. Run script per instance counters_sqlConnection = [] # smo optimization for faster loading counters_sqlConnection.append("$ob = New-Object Microsoft.SqlServer.Management.Smo.Database;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") counters_sqlConnection.append("$ob = New-Object Microsoft.SqlServer.Management.Smo.Table;" "$def = $server.GetDefaultInitFields($ob.GetType());" "$server.SetDefaultInitFields($ob.GetType(), $def);") counters_sqlConnection.append("if ($server.Databases -ne $null) {") counters_sqlConnection.append("$dbMaster = $server.Databases['master'];") counters_sqlConnection.append("foreach ($db in $server.Databases){") counters_sqlConnection.append("$db_name = '';" "$sp = $db.Name.split($([char]39)); " "if($sp.length -ge 2){ " "foreach($i in $sp){ " "if($i -ne $sp[-1]){ $db_name += $i + [char]39 + [char]39;}" "else { $db_name += $i;}" "}} else { $db_name = $db.Name;}" "Write-Host '{{db_name}} :counter: databasestatus :value: {{status}}'.replace" "('{{db_name}}', $db_name).replace('{{status}}', $db.Status);}") counters_sqlConnection.append("$query = 'select RTRIM(instance_name), " "RTRIM(counter_name), RTRIM(cntr_value) from " "sys.dm_os_performance_counters where instance_name in " "(select name from sys.databases)';") counters_sqlConnection.append("$ds = $dbMaster.ExecuteWithResults($query);") counters_sqlConnection.append("if($ds.Tables[0].rows.count -gt 0) {$ds.Tables[0].rows" "| % {write-host $_.Column1':counter:'$_.Column2':value:'$_.Column3;} } }") script = "\"& {{{}}}\"".format( ''.join([BUFFER_SIZE] + getSQLAssembly(sqlConnection.version) + sqlConnection.sqlConnection + counters_sqlConnection)) return pscommand, script
def collect(self, device, log): # Sample data for zDBInstanceLogin # MSSQLSERVER;ZenossInstance2 # Sample data for zDBInstancePassword # sa:Sup3rPa$$;sa:WRAAgf4234@#$ dbinstance = device.zDBInstances dbinstancepassword = device.zDBInstancesPassword password = device.zWinRMPassword dblogins = {} eventmessage = 'Error parsing zDBInstances or zDBInstancesPassword' try: if len(dbinstance) > 0 and len(dbinstancepassword) > 0: arrInstance = dbinstance.split(';') arrPassword = dbinstancepassword.split(';') i = 0 for instance in arrInstance: dbuser, dbpass = arrPassword[i].split(':', 1) i = i + 1 dblogins[instance] = { 'username': dbuser, 'password': dbpass } else: arrInstance = dbinstance.split(';') for instance in arrInstance: dblogins[instance] = { 'username': '******', 'password': password } results = {'clear': eventmessage} except (IndexError, ValueError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) winrs = create_single_shot_command(conn_info) #sqlserver = 'SQL1\ZENOSSINSTANCE2' #sqlusername = '******' #sqlpassword = '******' # Base command line setup for powershell pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " psInstances = [] psInstances.append("$hostname=hostname;") # Get registry key for instances # 32/64 Bit 2008 psInstances.append( "if (get-itemproperty \'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\')" ) psInstances.append( "{$instances = get-itemproperty \'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\';}" ) # 2003 psInstances.append( "if (get-itemproperty \'HKLM:\Software\Microsoft\Microsoft SQL Server\')" ) psInstances.append( "{$instances = get-itemproperty \'HKLM:\Software\Microsoft\Microsoft SQL Server\';}" ) psInstances.append( "$instances.InstalledInstances | foreach {write-host \"instances:\"$_};" ) psInstances.append("write-host \"hostname:\"$hostname;") command = "{0} \"& {{{1}}}\"".format(pscommand, ''.join(psInstances)) dbinstances = winrs.run_command(command) 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 server_config['instances'][0] == '': eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] for instance in server_config['instances']: 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) 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)) 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' \ '};') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + db_sqlConnection)) instancedatabases = winrs.run_command(command) databases = yield instancedatabases for dbobj in 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)) if dbdict['lastlogbackupdate'][:8] == '1/1/0001': lastlogbackupdate = None else: lastlogbackupdate = dbdict['lastlogbackupdate'] if dbdict['lastbackupdate'][:8] == '1/1/0001': lastbackupdate = None else: lastbackupdate = dbdict['lastbackupdate'] 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'] 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' \ '};') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + backup_sqlConnection)) backuplist = winrs.run_command(command) backups = yield backuplist for backupobj in 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, " \ "s.description 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;') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + job_sqlConnection)) jobslist = winrs.run_command(command) jobs = yield jobslist for job in jobs.stdout: 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 = value.strip() 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 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) 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 collect(self, device, log): # Sample data for zDBInstanceLogin # MSSQLSERVER;ZenossInstance2 # Sample data for zDBInstancePassword # sa:Sup3rPa$$;sa:WRAAgf4234@#$ dbinstance = device.zDBInstances dbinstancepassword = device.zDBInstancesPassword password = device.zWinRMPassword dblogins = {} eventmessage = 'Error parsing zDBInstances or zDBInstancesPassword' try: if len(dbinstance) > 0 and len(dbinstancepassword) > 0: arrInstance = dbinstance.split(';') arrPassword = dbinstancepassword.split(';') i = 0 for instance in arrInstance: dbuser, dbpass = arrPassword[i].split(':', 1) i = i + 1 dblogins[instance] = {'username': dbuser, 'password': dbpass} else: arrInstance = dbinstance.split(';') for instance in arrInstance: dblogins[instance] = {'username': '******', 'password': password} results = {'clear': eventmessage} except (IndexError, ValueError): # Error with dbinstance names or password results = {'error': eventmessage} defer.returnValue(results) conn_info = self.conn_info(device) winrs = create_single_shot_command(conn_info) #sqlserver = 'SQL1\ZENOSSINSTANCE2' #sqlusername = '******' #sqlpassword = '******' # Base command line setup for powershell pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \ "-OutputFormat TEXT -Command " psInstances = [] psInstances.append("$hostname=hostname;") # Get registry key for instances # 32/64 Bit 2008 psInstances.append("if (get-itemproperty \'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\')") psInstances.append("{$instances = get-itemproperty \'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\';}") # 2003 psInstances.append("if (get-itemproperty \'HKLM:\Software\Microsoft\Microsoft SQL Server\')") psInstances.append("{$instances = get-itemproperty \'HKLM:\Software\Microsoft\Microsoft SQL Server\';}") psInstances.append("$instances.InstalledInstances | foreach {write-host \"instances:\"$_};") psInstances.append("write-host \"hostname:\"$hostname;") command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(psInstances)) dbinstances = winrs.run_command(command) 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 server_config['instances'][0] == '': eventmessage = 'No MSSQL Servers are installed but modeler is enabled' results = {'error': eventmessage} defer.returnValue(results) sqlhostname = server_config['hostname'][0] for instance in server_config['instances']: 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) 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)) 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' \ '};') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + db_sqlConnection)) instancedatabases = winrs.run_command(command) databases = yield instancedatabases for dbobj in 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)) if dbdict['lastlogbackupdate'][:8] == '1/1/0001': lastlogbackupdate = None else: lastlogbackupdate = dbdict['lastlogbackupdate'] if dbdict['lastbackupdate'][:8] == '1/1/0001': lastbackupdate = None else: lastbackupdate = dbdict['lastbackupdate'] 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'] 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' \ '};') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + backup_sqlConnection)) backuplist = winrs.run_command(command) backups = yield backuplist for backupobj in 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, " \ "s.description 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;') command = "{0} \"& {{{1}}}\"".format( pscommand, ''.join(getSQLAssembly() + sqlConnection + job_sqlConnection)) jobslist = winrs.run_command(command) jobs = yield jobslist for job in jobs.stdout: 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 = value.strip() 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 defer.returnValue(maps)