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