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 "
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 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)
