def remove_corrupt_counters(self):
'''
Remove counters which return an error.
'''
LOG.debug('Performing check for corrupt counters')
dsconf0 = self.config.datasources[0]
winrs = create_single_shot_command(createConnectionInfo(dsconf0))
counter_list = sorted(
set(self.ps_counter_map.keys())-set(CORRUPT_COUNTERS[dsconf0.device]))
corrupt_counters = yield self.search_corrupt_counters(winrs, counter_list, [])
# Add newly found corrupt counters to the previously checked ones.
if corrupt_counters:
CORRUPT_COUNTERS[dsconf0.device].extend(corrupt_counters)
# Remove the error counters from the counter map.
for counter in CORRUPT_COUNTERS[dsconf0.device]:
if self.ps_counter_map.get(counter):
LOG.debug("Counter '{0}' not found. Removing".format(counter))
del self.ps_counter_map[counter]
# Rebuild the command.
self._build_commandlines()
defer.returnValue(None)
def remove_corrupt_counters(self):
'''
Remove counters which return an error.
'''
LOG.debug('Performing check for corrupt counters')
dsconf0 = self.config.datasources[0]
winrs = create_single_shot_command(createConnectionInfo(dsconf0))
counter_list = sorted(
set(self.ps_counter_map.keys()) -
set(CORRUPT_COUNTERS[dsconf0.device]))
corrupt_counters = yield self.search_corrupt_counters(
winrs, counter_list, [])
# Add newly found corrupt counters to the previously checked ones.
if corrupt_counters:
CORRUPT_COUNTERS[dsconf0.device].extend(corrupt_counters)
# Remove the error counters from the counter map.
for counter in CORRUPT_COUNTERS[dsconf0.device]:
if self.ps_counter_map.get(counter):
LOG.debug("Counter '{0}' not found. Removing".format(counter))
del self.ps_counter_map[counter]
# Rebuild the command.
self._build_commandlines()
defer.returnValue(None)
def _execute_command(self, device, command):
"""
Create WinRS client and run the command remotely.
"""
winrs = create_single_shot_command(self._conn_info(device))
result = winrs.run_command(str(command))
result.addCallback(lambda res: self._on_success(device, res, command))
result.addErrback(lambda err: self._on_error(device, err))
def _execute_command(self, device, command):
"""
Create WinRS client and run the command remotely.
"""
winrs = create_single_shot_command(self._conn_info(device))
result = winrs.run_command(str(command))
result.addCallback(lambda res: self._on_success(device, res, command))
result.addErrback(lambda err: self._on_error(device, err))
def __init__(self, conn_info):
self.winrs = create_single_shot_command(conn_info)
def collect(self, device, log):
maps = {}
conn_info = self.conn_info(device)
winrs = create_single_shot_command(conn_info)
# Collection for cluster nodes
pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \
"-OutputFormat TEXT -Command "
psClusterCommands = []
psClusterCommands.append("import-module failoverclusters;")
psClusterHosts = []
psClusterHosts.append(
"$domain='.' + (gwmi WIN32_ComputerSystem).Domain;")
psClusterHosts.append("get-clusternode | foreach {$_.Name + $domain};")
command = "{0} \"& {{{1}}}\"".format(
pscommand, ''.join(psClusterCommands + psClusterHosts))
clusternodes = winrs.run_command(command)
clusternode = yield clusternodes
# Collection for cluster groups
psResources = []
clustergroupitems = ('$_.Name', '$_.IsCoreGroup', '$_.OwnerNode',
'$_.State', '$_.Description', '$_.Id',
'$_.Priority')
psResources.append('get-clustergroup | foreach {{{0}}};'.format(
" + '|' + ".join(clustergroupitems)))
command = "{0} \"& {{{1}}}\"".format(
pscommand, ''.join(psClusterCommands + psResources))
resources = winrs.run_command(command)
resource = yield resources
# Collection for cluster applications
psApplications = []
clusterappitems = ('$_.Name', '$_.OwnerGroup', '$_.OwnerNode',
'$_.State', '$_.Description')
# For some reason if I add the $_.Id the data is returned corrupted.
psApplications.append('get-clusterresource | foreach {{{0}}};'.format(
" + '|' + ".join(clusterappitems)))
command = "{0} \"& {{{1}}}\"".format(
pscommand, ''.join(psClusterCommands + psApplications))
clusterapps = winrs.run_command(command)
clusterapp = yield clusterapps
maps['apps'] = clusterapp.stdout
maps['resources'] = resource.stdout
maps['nodes'] = clusternode.stdout
defer.returnValue(maps)
def __init__(self, conn_info):
self.winrs = create_single_shot_command(conn_info)
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, config):
dsconf0 = config.datasources[0]
conn_info = createConnectionInfo(dsconf0)
strategy = queryUtility(IStrategy, dsconf0.params['strategy'])
if not strategy:
raise WindowsShellException("No strategy chosen for {0}".format(
dsconf0.datasource))
counters = [dsconf.params['resource'] for dsconf in config.datasources]
if dsconf0.params['strategy'] == 'powershell MSSQL':
dbinstances = dsconf0.zDBInstances
username = dsconf0.windows_user
password = dsconf0.windows_password
dblogins, login_as_user = parseDBUserNamePass(
dbinstances, username, password)
instance = dsconf0.params['instancename']
dbname = dsconf0.params['contexttitle']
try:
instance_login = dblogins[instance]
except KeyError:
raise WindowsShellException(
"zDBInstances don't contain credentials for %s" % instance)
if instance == 'MSSQLSERVER':
sqlserver = dsconf0.config_key
else:
sqlserver = '{0}\{1}'.format(dsconf0.sqlhostname, instance)
# Use the owner node's hostname to get monitoring data for
# databases of network instances for cluster devices.
if dsconf0.cluster_node_server:
owner_node, server = dsconf0.cluster_node_server.split('//')
if owner_node:
conn_info = conn_info._replace(hostname=owner_node)
sqlserver = server
command_line = strategy.build_command_line(
counters,
sqlserver=sqlserver,
sqlusername=instance_login['username'],
sqlpassword=instance_login['password'],
database=dbname,
login_as_user=login_as_user)
elif dsconf0.params['strategy'] in ('powershell Cluster Services'
'powershell Cluster Resources'):
resource = dsconf0.params['contexttitle']
if not resource:
return
componenttype = dsconf0.params['resource']
command_line = strategy.build_command_line(resource, componenttype)
elif dsconf0.params['strategy'] == 'Custom Command':
check_datasource(dsconf0)
script = dsconf0.params['script']
usePowershell = dsconf0.params['usePowershell']
command_line = strategy.build_command_line(script, usePowershell)
else:
command_line = strategy.build_command_line(counters)
command = create_single_shot_command(conn_info)
try:
results = yield command.run_command(command_line)
except UnauthorizedError:
results = ShellResult()
except Exception, e:
if "Credentials cache file" in str(e):
results = ShellResult()
results.stderr = ['Credentials cache file not found']
else:
raise e
def collect(self, device, log):
maps = {}
conn_info = self.conn_info(device)
winrs = create_single_shot_command(conn_info)
# Collection for cluster nodes
pscommand = "powershell -NoLogo -NonInteractive -NoProfile " \
"-OutputFormat TEXT -Command "
psClusterCommands = []
psClusterCommands.append("import-module failoverclusters;")
psClusterHosts = []
psClusterHosts.append("$domain='.' + (gwmi WIN32_ComputerSystem).Domain;")
psClusterHosts.append("get-clusternode | foreach {$_.Name + $domain};")
command = "{0} \"& {{{1}}}\"".format(
pscommand,
''.join(psClusterCommands + psClusterHosts))
clusternodes = winrs.run_command(command)
clusternode = yield clusternodes
# Collection for cluster groups
psResources = []
clustergroupitems = ('$_.Name', '$_.IsCoreGroup', '$_.OwnerNode', '$_.State',
'$_.Description', '$_.Id', '$_.Priority')
psResources.append('get-clustergroup | foreach {{{0}}};'.format(
" + '|' + ".join(clustergroupitems)
))
command = "{0} \"& {{{1}}}\"".format(
pscommand,
''.join(psClusterCommands + psResources))
resources = winrs.run_command(command)
resource = yield resources
# Collection for cluster applications
psApplications = []
clusterappitems = ('$_.Name', '$_.OwnerGroup', '$_.OwnerNode', '$_.State',
'$_.Description')
# For some reason if I add the $_.Id the data is returned corrupted.
psApplications.append('get-clusterresource | foreach {{{0}}};'.format(
" + '|' + ".join(clusterappitems)
))
command = "{0} \"& {{{1}}}\"".format(
pscommand,
''.join(psClusterCommands + psApplications))
clusterapps = winrs.run_command(command)
clusterapp = yield clusterapps
maps['apps'] = clusterapp.stdout
maps['resources'] = resource.stdout
maps['nodes'] = clusternode.stdout
defer.returnValue(maps)
def collect(self, config):
dsconf0 = config.datasources[0]
conn_info = createConnectionInfo(dsconf0)
strategy = queryUtility(IStrategy, dsconf0.params['strategy'])
if not strategy:
raise WindowsShellException(
"No strategy chosen for {0}".format(dsconf0.datasource)
)
counters = [dsconf.params['resource'] for dsconf in config.datasources]
if dsconf0.params['strategy'] == 'powershell MSSQL':
dbinstances = dsconf0.zDBInstances
username = dsconf0.windows_user
password = dsconf0.windows_password
dblogins = parseDBUserNamePass(
dbinstances, username, password
)
instance = dsconf0.params['instancename']
try:
instance_login = dblogins[instance]
except KeyError:
log.debug("zDBInstances does not contain credentials for %s. " \
"Using default credentials" % instance
)
try:
instance_login = dblogins['MSSQLSERVER']
except KeyError:
instance_login = {'username':dsconf0.windows_user, \
'password':dsconf0.windows_password,
'login_as_user':True}
if instance == 'MSSQLSERVER':
instance_name = dsconf0.sqlhostname
else:
instance_name = '{0}\{1}'.format(dsconf0.sqlhostname, instance)
if dsconf0.cluster_node_server:
owner_node, server = dsconf.cluster_node_server.split('//')
if owner_node:
conn_info = conn_info._replace(hostname=owner_node)
instance_name = server
else:
if instance == 'MSSQLSERVER':
instance_name = dsconf0.sqlhostname
else:
instance_name = '{0}\{1}'.format(dsconf0.sqlhostname, instance)
command_line = strategy.build_command_line(
instance=instance_name,
sqlusername=instance_login['username'],
sqlpassword=instance_login['password'],
login_as_user=instance_login['login_as_user'],
is_cluster=True if owner_node else False)
elif dsconf0.params['strategy'] in ('powershell Cluster Services'
'powershell Cluster Resources'):
resource = dsconf0.params['contexttitle']
if not resource:
return
componenttype = dsconf0.params['resource']
command_line = strategy.build_command_line(resource, componenttype)
elif dsconf0.params['strategy'] == 'Custom Command':
check_datasource(dsconf0)
script = dsconf0.params['script']
usePowershell = dsconf0.params['usePowershell']
command_line = strategy.build_command_line(script, usePowershell)
elif dsconf0.params['strategy'] == 'DCDiag':
testparms = [dsconf.params['script'] for dsconf in config.datasources if dsconf.params['script']]
command_line = strategy.build_command_line(counters, testparms)
else:
command_line = strategy.build_command_line(counters)
command = create_single_shot_command(conn_info)
try:
results = yield command.run_command(command_line)
except UnauthorizedError:
results = ShellResult()
except Exception, e:
if "Credentials cache file" in str(e):
results = ShellResult()
results.stderr = ['Credentials cache file not found']
else:
raise e
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)
