this.ConcatinateServerName = function (server, tcpPort) {
var dataSource = server;
if ((tcpPort !== "") && (tcpPort !== "0")) {
dataSource = dataSource + "," + tcpPort;
}
return dataSource;
};
this.BuildServerName = function (strServer) {
var tcp = "";
var ip = "";
var pathArray = strServer.split("\\");
var instanceName = "MSSQLSERVER";
if (pathArray.length > 1) {
instanceName = pathArray[1];
}
var serverName = strServer;
var oWMIcommon = new WMIProvider("root\\Microsoft\\SqlServer\\ComputerManagement12");
var oQuery = oWMIcommon.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND PropertyName = 'ListenOnAllIPs'");
if (oQuery.Count > 0) {
var e = new Enumerator(oQuery);
e.moveFirst();
var isListenAll = e.item();
if (isListenAll.PropertyNumVal === 1) {
oQuery = oWMIcommon.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal != ''");
if (oQuery.Count > 0) {
e = new Enumerator(oQuery);
e.moveFirst();
tcp = e.item().PropertyStrVal;
if ((tcp !== "0") && (tcp !== "")) {
serverName = serverName + "," + tcp;
}
}
} else {
oQuery = oWMIcommon.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName != '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1");
if (oQuery.Count > 0) {
var ipAddressName = oQuery.ItemIndex(0).IPAddressName;
oQuery = oWMIcommon.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = '" + this.EscapeWQLString(ipAddressName) + "' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal != ''");
if (oQuery.Count > 0) {
e = new Enumerator(oQuery);
e.moveFirst();
tcp = e.item().PropertyStrVal;
}
oQuery = oWMIcommon.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = '" + this.EscapeWQLString(ipAddressName) + "' AND PropertyName = 'IpAddress' AND PropertyStrVal != ''");
if (oQuery.Count > 0) {
e = new Enumerator(oQuery);
e.moveFirst();
ip = e.item().PropertyStrVal;
}
if (ip !== "") {
serverName = ip;
}
if (tcp !== "") {
serverName = serverName + "," + tcp;
}
}
}
}
return serverName;
};
this.CheckConnectionTarget = function (dbConnection, serverName, instanceName) {
var destinationTestQuery = "select SERVERPROPERTY('MachineName') as ServerName, @@servicename as InstanceName";
var queryResult = dbConnection.ExecuteQuery(destinationTestQuery);
queryResult.MoveFirst();
if (!queryResult.EOF) {
var queryServerName = queryResult("ServerName").Value.toUpperCase();
var queryInstanceName = queryResult("InstanceName").Value.toUpperCase();
var serverNameWithoutDomain = serverName;
var dotPosition = serverName.indexOf(".");
if (dotPosition > -1) {
serverNameWithoutDomain = serverName.substring(0, dotPosition);
}
if (serverNameWithoutDomain.toUpperCase() == queryServerName && instanceName.toUpperCase() == queryInstanceName) {
return;
}
}
throw new Exception("Connection target check failed: connected to " + serverName + "\\" + instanceName + ", but got " + queryServerName + "\\" + queryInstanceName + ".");
};
this.SmartConnect = function (serverName, databaseName, tcpPort, machineName, instanceName) {
var dbMasterConnection;
var connectionString;
//try to use SQL server browser
connectionString = this.BuildConnectionStringWithPort(serverName, databaseName, "");
try {
dbMasterConnection = new ADODB(connectionString, "sqloledb", 10);
this.CheckConnectionTarget(dbMasterConnection, machineName, instanceName);
} catch (e) {
//use original tcp port and try to connect again
connectionString = this.BuildConnectionStringWithPort(serverName, databaseName, tcpPort);
try {
dbMasterConnection = new ADODB(connectionString, "sqloledb", 10);
this.CheckConnectionTarget(dbMasterConnection, machineName, instanceName);
} catch (eInt) {
//get fresh tcp port and try to connect again
connectionString = this.BuildConnectionString(serverName, databaseName);
dbMasterConnection = new ADODB(connectionString, "sqloledb", 30);
this.CheckConnectionTarget(dbMasterConnection, machineName, instanceName);
}
}
return dbMasterConnection;
};
};
Function.prototype.setAlias = function (funcName) {
this.getAlias = function () {
return funcName;
};
};
Error.prototype.toString = function () {
if (this.message) {
var parts = [];
parts.push(this.message);
parts.push('\n');
if (this.number) {
parts.push("Error Number : ");
parts.push(this.number);
parts.push('\n');
parts.push("Error Code : ");
parts.push(this.number & 0xFFFF);
parts.push('\n');
parts.push("Win32 Facility : ");
parts.push((this.number >> 16 & 0x1FFF));
parts.push('\n');
}
if (this.description) {
parts.push("Error Description : ");
parts.push(this.description);
}
parts.push('\n');
return parts.join('');
} else {
return this.toString();
}
};
var Exception = function (message, innerException) {
arguments.callee.setAlias('Exception.constructor');
this.message = message;
this.innerException = innerException;
var parts = [];
var collectArguments = function (caller) {
parts.push('(');
for (var i = 0; i < caller.length; i++) {
if (typeof (caller[i]) !== 'undefined') {
parts.push(caller[i] === null ? 'null' : (typeof (caller[i].toString) === 'undefined' ? 'Object' : caller[i].toString()));
parts.push(',');
}
}
parts.pop();
parts.push(')');
parts.push(',\n');
};
var collectCallStack = function (caller) {
arguments.callee.setAlias('Exception.collectCallStack');
parts.push(caller.callee.getAlias ? caller.callee.getAlias() : 'anonymous');
collectArguments(caller);
var nextCaller = caller.caller;
if (nextCaller) {
collectCallStack(nextCaller);
}
};
collectCallStack(arguments);
this.callStack = parts.join('');
this.toString = function () {
arguments.callee.setAlias('Exception.toStringFull');
var toStringParts = [];
toStringParts.push(this.message);
toStringParts.push('\n');
if (this.innerException) {
if (this.innerException.number || this.innerException.description) {
toStringParts.push('Inner exception: \n');
if (this.innerException.number) {
toStringParts.push("Error Number : ");
toStringParts.push(this.innerException.number);
toStringParts.push('\n');
toStringParts.push("Error Code : ");
toStringParts.push(this.innerException.number & 0xFFFF);
toStringParts.push('\n');
toStringParts.push("Win32 Facility : ");
toStringParts.push((this.innerException.number >> 16 & 0x1FFF));
toStringParts.push('\n');
}
if (this.innerException.description) {
toStringParts.push("Error Description : ");
toStringParts.push(this.innerException.description);
toStringParts.push('\n');
}
}
}
toStringParts.push('Call stack:');
toStringParts.push(this.callStack);
return toStringParts.join('');
};
};
var OpsMgrAPI = function () {
arguments.callee.setAlias('MOMAPI.constructor');
var scriptAPI;
try {
scriptAPI = new ActiveXObject('MOM.ScriptAPI');
} catch (e) {
throw new Exception("Application cannot create MOM.ScriptAPI ActiveX object.", e);
}
this.ScriptAPI = scriptAPI;
};
var Logger = function () {
arguments.callee.setAlias('Logger.constructor');
this.common = new Common();
this.messageType = {
error: 1,
warning: 2,
information: 4
};
this.Close = function () {
arguments.callee.setAlias('ADODB.Close');
try {
if (connection) {
connection.Close();
}
}
catch (e) {
throw new Exception("Can't close connection.", e);
}
};
};
var Registry = function () {
arguments.callee.setAlias('Registry.constructor');
var registry;
try {
registry = new ActiveXObject("WScript.Shell");
} catch (e) {
throw new Exception("Can't create ActiveXObject 'WScript.Shell'", e);
}
this.Write = function (key, value, type) {
arguments.callee.setAlias('Registry.Write');
try {
if (type) {
registry.RegWrite(key, value, type);
} else {
registry.RegWrite(key, value);
}
} catch (e) {
throw new Exception("Can't write value '" + value.toString() + "' by registry key '" + key.toString() + "'.", e);
}
};
this.Read = function (key) {
arguments.callee.setAlias('Registry.Read');
var value;
try {
value = registry.RegRead(key);
} catch (e) {
throw new Exception("Can't read value from registry key '" + key.toString() + "'.", e);
}
return value;
};
this.IsKeyExists = function (key) {
arguments.callee.setAlias('Registry.IsKeyExists');
var result;
try {
registry.RegRead(key);
result = true;
} catch (e) {
result = false;
}
return result;
};
this.Delete = function (key) {
arguments.callee.setAlias('Registry.Delete');
var value;
try {
value = registry.RegDelete(key);
} catch (e) {
throw new Exception("Can't delete registry key '" + key.toString() + "'.", e);
}
return value;
};
};
var WMIProvider = function (wmiNamespace, computerName) {
arguments.callee.setAlias('WMIProvider.constructor');
this.wmiNamespace = wmiNamespace;
this.computerName = (!computerName) ? computerName : ".";
var sWbemLocator;
try {
sWbemLocator = new ActiveXObject("WbemScripting.SWbemLocator");
} catch (e) {
throw new Exception("Cannot create 'WbemScripting.SWbemLocator' object.", e);
}
this.SWbemLocator = sWbemLocator;
var wmiService;
try {
wmiService = this.SWbemLocator.ConnectServer(this.computerName, this.wmiNamespace);
} catch (e) {
throw new Exception("Cannot connect to WMI of computer '" + this.computerName + "' by namespace '" + this.wmiNamespace + "'.", e);
}
this.WMIService = wmiService;
this.ExecQuery = function (query) {
arguments.callee.setAlias('WMIProvider.ExecQuery');
var result = null;
try {
result = this.WMIService.ExecQuery(query, "WQL", 0x0);
} catch (e) {
throw new Exception("Cannot execute WMI query '" + query + "'.", e);
}
return result;
};
};
var DBHelper = function () {
arguments.callee.setAlias('DBHelper.constructor');
this.EVENT_ID = 4201;
this.DatabaseState = {
emergencyMode: 32768,
loading: 22,
normal: 0,
offline: 512,
recovering: 192,
standby: 1024,
suspect: 256
};
this.common = new Common();
this.logger = new Logger();
this.IsRunningInstanceSQLServer = function (sqlInstanceName) {
arguments.callee.setAlias('IsRunningInstanceSQLServer');
var isRunning = false;
try {
var wmiProvider = new WMIProvider("root\\Microsoft\\SqlServer\\ComputerManagement12");
var sqlService = wmiProvider.ExecQuery("select state from SqlService WHERE (ServiceName = '" + this.common.EscapeWQLString(sqlInstanceName) + "' OR ServiceName = 'MSSQL$" + this.common.EscapeWQLString(sqlInstanceName) + "') and SQLServiceType ='1'");
if (sqlService.Count > 0) {
var e = new Enumerator(sqlService);
e.moveFirst();
var sqlServiceObject = e.item();
this.GetDatabaseExcludeList = function (instanceName) {
arguments.callee.setAlias('GetDatabaseExcludeList');
var databaseExcludeList = "";
try {
var opsMgrAPI = new OpsMgrAPI();
var registryKey = opsMgrAPI.ScriptAPI.GetScriptStateKeyPath(this.common.ManagementGroupID);
this.logger.LogDebug(this.EVENT_ID, "ScriptStateKeyPath: " + registryKey);
var registry = new Registry();
var databaseExcludeListKey = "HKEY_LOCAL_MACHINE\\" + registryKey + "\\" + instanceName + "\\DatabaseExcludeList";
if (registry.IsKeyExists(databaseExcludeListKey)) {
databaseExcludeList = registry.Read(databaseExcludeListKey);
databaseExcludeList = databaseExcludeList.replace(/(^\s+)|(\s+$)/g, "");
if (databaseExcludeList !== '*') {
var excludeDatabases = databaseExcludeList.split(",");
for (var i = 0; i < excludeDatabases.length; i++) {
excludeDatabases[i] = excludeDatabases[i].replace(/(^\s+)|(\s+$)/g, "");
}
databaseExcludeList = excludeDatabases.join(",");
}
}
} catch (e) {
throw new Exception("Can't read database exclude list from registry.", e);
}
return databaseExcludeList;
};
// Returns -1: If DB is not in AlwaysOn
// Returns 0: If DB is in AlwaysOn and replica allow connections is NO
// Returns 1: If DB is in AlwaysOn and replica allow connections is YES
this.AlwaysOnReplicaAllowConnections = function (aDbConnection, aDatabaseID) {
var query = " SELECT columns.id, " +
" CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')";
var result = aDbConnection.ExecuteQuery(query);
if (!result.EOF) {
result.MoveFirst();
if (result("HasAlwaysOn").Value === 1) {
query = " SELECT d.name, d.database_id, " +
" CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica, " +
" ar.secondary_role_allow_connections " +
" FROM sys.databases d " +
" JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id " +
" JOIN sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/" +
" WHERE d.database_id = ? ";
result = aDbConnection.ExecuteQueryWithParams(query, aDatabaseID);
if (!result.EOF) {
var ServicesHelper = function () {
arguments.callee.setAlias('ServicesHelper.constructor');
this.EVENT_ID = 4201;
this.IsServiceUnavailable = "ServiceUnavailable";
this.logger = new Logger();
this.common = new Common();
this.GetServiceUnavailableRegistryKey = function (serviceName) {
arguments.callee.setAlias('GetRegistryKeyForService');
var opsMgrAPI = new OpsMgrAPI();
var registryKey = opsMgrAPI.ScriptAPI.GetScriptStateKeyPath(this.common.ManagementGroupID);
var key = "HKEY_LOCAL_MACHINE\\" + registryKey + "\\" + serviceName + "\\";
return key;
};
this.ReturnServiceStateToOpsMgr = function (serviceState) {
var opsMgrAPI = new OpsMgrAPI();
this.logger.LogDebug(this.EVENT_ID, "Return to OpsMgr state of service = " + serviceState);
var propertyBag = opsMgrAPI.ScriptAPI.CreatePropertyBag();
propertyBag.AddValue("ServiceState", serviceState);
opsMgrAPI.ScriptAPI.AddItem(propertyBag);
opsMgrAPI.ScriptAPI.ReturnItems();
};
};
//#Include File:DiskInfo.js
var PathHelper = function () { };
PathHelper.GetFileName = function (filePath) {
var pos = filePath.lastIndexOf(filePath.charAt(filePath.indexOf(":") + 1));
return filePath.substring(pos + 1);
};
PathHelper.GetPath = function (filePath) {
return filePath.substring(0, filePath.lastIndexOf(filePath.charAt(filePath.indexOf(":") + 1)) + 1);
};
PathHelper.GetLetter = function (filePath) {
return filePath.substring(0, filePath.indexOf(":") + 1);
};
var Point = function () {
this.name = "";
this.caption = "";
this.deviceID = "";
};
var DiskVolume = function () {
this.fileSystem = "";
this.capacity = "";
this.freeSpace = "";
this.name = "";
this.deviceID = "";
this.label = "";
this.caption = "";
this.paths = new Array();
};
var DiskVolumeInformation = function () {
// this.serverName;
this.oWMI = new WMIProvider("root\\cimv2");
this.clusterWMI = null;
this.mountpoints = new Array();
this.volumes = new Array();
this.Initialize = function (serverName) {
this.serverName = serverName;
this.volumes = new Array();
this.mountpoints = new Array();
getMountPoints.call(this);
getVolumes.call(this);
mergeMountPointsIntoVolumeList.call(this);
};
var getMountPoints = function () {
var colItems = this.oWMI.ExecQuery("select Directory, Volume from Win32_Mountpoint");
if (colItems.Count > 0) {
var dictMountpoints = new ActiveXObject("Scripting.Dictionary");
var items = new Enumerator(colItems);
items.moveFirst();
while (!items.atEnd()) {
var item = items.item();
var point = new Point();
point.caption = item.Directory.substring(item.Directory.indexOf('"') + 1, item.Directory.lastIndexOf('"')).replace(/\\\\/g, "\\").toLocaleUpperCase();
point.deviceID = item.Volume.substring(item.Volume.indexOf('"') + 1, item.Volume.lastIndexOf('"')).replace(/\\\\/g, "\\");
point.name = point.deviceID + point.caption;
dictMountpoints.add(point.name, point);
items.moveNext();
}
if (dictMountpoints.Count > 0) {
this.mountpoints = (new VBArray(dictMountpoints.Items())).toArray();
this.mountpoints.sort(function (a, b) {
if (a.caption.length > b.caption.length) {
return -1;
}
return 1;
});
}
}
};
var getVolumes = function () {
var colItems = this.oWMI.ExecQuery("select DeviceID, Name, Caption, Label, FileSystem, Capacity, FreeSpace from Win32_Volume");
if (colItems.Count > 0) {
var dicVolumes = new ActiveXObject("Scripting.Dictionary");
var items = new Enumerator(colItems);
items.moveFirst();
while (!items.atEnd()) {
var item = items.item();
var vol = new DiskVolume();
vol.capacity = item.Capacity;
vol.fileSystem = item.FileSystem;
vol.freeSpace = item.FreeSpace;
vol.name = item.Name;
vol.deviceID = item.DeviceID;
vol.label = item.Label;
vol.caption = item.Caption.toLocaleUpperCase();
vol.paths[vol.paths.length] = vol.caption;
dicVolumes.add(vol.deviceID, vol);
items.moveNext();
}
this.volumes = (new VBArray(dicVolumes.Items())).toArray();
}
};
var searchVolumeDeviceId = function (compareDeviceID) {
for (var i in this.volumes) {
if (this.volumes[i].deviceID.localeCompare(compareDeviceID) === 0) {
return this.volumes[i];
}
}
return null;
};
var searchVolumeCaption = function (compareCaption) {
for (var i in this.volumes) {
for (var j = 0; j < this.volumes[i].paths.length; j++) {
if (this.volumes[i].paths[j].localeCompare(compareCaption) === 0) {
return true;
}
}
}
return false;
};
this.MountPoint = function (compareCaption) {
var cap = compareCaption.toLocaleUpperCase();
for (var i in this.mountpoints) {
if (cap.indexOf(this.mountpoints[i].caption) === 0) {
return this.mountpoints[i].caption;
}
}
return null;
};
var mergeMountPointsIntoVolumeList = function () {
if (this.mountpoints.length > 0) {
for (var i in this.mountpoints) {
//Adding backslash in the end of the path is necessary for
//matching the path stored in volumes class, also it prevents
//invalid equality among folders with long composite names like
//"C:\Program Files" and "C:\Program"
var varCaption = this.mountpoints[i].caption;
if (varCaption.charAt(varCaption.length - 1) !== '\\') {
varCaption += "\\";
}
//Search for Mount Points which are not listed among Volumes
var found = searchVolumeCaption.call(this, varCaption);
if (!found) {
//Search for the equivalent Volume
var volume = searchVolumeDeviceId.call(this, this.mountpoints[i].deviceID);
if (volume != null) {
volume.paths[volume.paths.length] = varCaption;
}
}
}
}
};
this.GetVolumeByPath = function /* @dynamic */(path) {
try {
//first check mounted points
if (this.mountpoints.length > 0) {
var caption = path.toLocaleUpperCase();
for (var i in this.mountpoints) {
if (caption.indexOf(this.mountpoints[i].caption) === 0) {
return searchVolumeDeviceId.call(this, this.mountpoints[i].deviceID);
}
}
}
//get drive letter from path and search by volume
if (this.volumes.length > 0) {
var letter = path.substring(0, path.indexOf(":") + 1).toLocaleUpperCase();
if (letter.charAt(letter.length - 1) !== '\\') {
letter += "\\";
}
for (i in this.volumes) {
if (this.volumes[i].name.localeCompare(letter) === 0) {
return this.volumes[i];
}
}
}
}
catch (e) {
}
return null;
}; //local:
//\\?\[drive_spec]:\ or //[drive_letter]:\
//share:
//\\[server]\[sharename]\ or \\?\[server]\[sharename]\ or \\?\UNC\[server]\[sharename]\
//Blobs are addressable using the following URL format:
//http://<storage account>.blob.core.windows.net/<container>/<blob>
//https://<storage account>.blob.core.windows.net/<container>/<blob>
this.GetStorageTypeByPath = function (path) {
var pattern;
var result;
//if it is blob
pattern = /^(https?:\/\/)?([\da-z\.-]+)\.([a-z\.]{2,6})([\/\w \.-]*)*\/?$/g;
result = path.match(pattern);
if (result != null) {
return StorageType.Blob;
}
//local
pattern = /(\\{2}\?\\)?([a-zA-Z]\:\\([^\\]*\\?)+)/g;
result = pattern.exec(path);
if (result != null) {
return StorageType.Local;
}
//share
pattern = /\\{2}(\?\\)?([^\\]*\\?)+/g;
result = path.match(pattern);
if (result != null) {
return StorageType.Share;
}
return StorageType.Unknown;
};
this.GetClusterSharedVolume = function (volume) {
try {
if (this.clusterWMI === null) {
this.clusterWMI = new WMIProvider("root\\MSCluster");
}
var path = volume.deviceID.replace(/\\/g, "\\\\");
var query = "select Caption,Description,InstallDate,Name,Status,Flags,Characteristics,Path,VolumeLabel," +
" SerialNumber,MaximumComponentLength,FileSystemFlags,FileSystem,TotalSize,FreeSpace,PartitionNumber,VolumeGuid" +
" from MSCluster_DiskPartition Where Path=\"" + path + "\"";
var claster = this.clusterWMI.ExecQuery(query);
var items = new Enumerator(claster);
items.moveFirst();
if (!items.atEnd()) {
return items.item();
}
}
catch (e) {
}
return null;
};
this.Print = function () {
WScript.StdErr.WriteLine("mountpoints " + this.mountpoints.length.toString());
if (this.mountpoints.length > 0) {
for (var i in this.mountpoints) {
WScript.StdErr.WriteLine("[caption:] " + this.mountpoints[i].caption);
WScript.StdErr.WriteLine(" name: " + this.mountpoints[i].name);
WScript.StdErr.WriteLine(" deviceID: " + this.mountpoints[i].deviceID);
}
}
WScript.StdErr.WriteLine(" ");
WScript.StdErr.WriteLine("volumes " + this.volumes.length.toString());
if (this.volumes.length > 0) {
for (i in this.volumes) {
WScript.StdErr.WriteLine("[name:] " + this.volumes[i].name);
WScript.StdErr.WriteLine("deviceID: " + this.volumes[i].deviceID);
WScript.StdErr.WriteLine("label: " + this.volumes[i].label);
WScript.StdErr.WriteLine("caption: " + this.volumes[i].caption);
WScript.StdErr.WriteLine("Capacity: " + this.volumes[i].capacity);
WScript.StdErr.WriteLine("fileSystem: " + this.volumes[i].fileSystem);
WScript.StdErr.WriteLine("freeSpace: " + this.volumes[i].freeSpace);
for (var j = 0; j < this.volumes[i].paths.length; j++) {
WScript.StdErr.WriteLine(" path" + j.toString() + ": " + this.volumes[i].paths[j]);
}
}
}
};
};//#Include File:DiscoverSQL2014Files.js
//' Parameters that should be passed to this script
//' 0 MPElement ID ($MPElement$)
//' 1 Target Id for ME this rule is running against ($Target/Id$)
//' 2 Computer (FQDN) that SQL instance is hosted on
//' 3 SQL Connection String for the instance that the DBs are being discovered on
//' 4 SQL Instance that this rule is being run for
//' 5 Pre-discovered TCP port
var DiscoverSQL2014Files = function () {
arguments.callee.setAlias('DiscoverSQL2014Files.constructor');
this.EVENT_ID = 4201;
this.DISCOVERY_ERROR_EVENT_ID = 7104;
this.common = new Common();
this.dbHelper = new DBHelper();
this.logger = new Logger();
this.MainFunc = function (sourceId, managedEntityId, targetComputer, serverName, instanceName, tcpPort) {
arguments.callee.setAlias('Main');
var dbMasterConnection;
var opsMgrAPI = new OpsMgrAPI();
var diskVol = new DiskVolumeInformation();
diskVol.Initialize(serverName);
var databaseExcludeList = this.dbHelper.GetDatabaseExcludeList(instanceName, this.common);
if (databaseExcludeList === '*') {
opsMgrAPI.ScriptAPI.Return(oSQLDiscoveryData);
return;
}
var conditionDatabaseExcludeList = '';
if (databaseExcludeList !== '') {
conditionDatabaseExcludeList = "," + databaseExcludeList + ",";
}
var query = ";with qry(n, str) as " +
" (select len(list.str) - len(replace(list.str, ',', '')) - 1 as n, " +
" substring(list.str, 2, len(list.str)) as str " +
" from (select ? str) as list where len(list.str) >= 2 " +
" union all " +
" select (n - 1) as n, " +
" substring(str, 1 + charindex(',', str), len(str)) as str " +
" from qry " +
" where n > 1), " +
" split as (select substring(str, 1, charindex(',', str) - 1) item from qry) " +
" " +
" select database_id, name from sys.databases WHERE (state = ? OR state = ?) and source_database_id is null AND name NOT IN (select * from split)";
var dbSet = dbMasterConnection.ExecuteQueryWithParams(query, conditionDatabaseExcludeList, this.dbHelper.DatabaseState.normal, this.dbHelper.DatabaseState.standby);
for (; !dbSet.EOF; dbSet.MoveNext()) {
var databaseName = dbSet("name").Value;
var databaseID = dbSet("database_id").Value;
var dbGroupConnection;
try {
var dbFileSet = dbMasterConnection.ExecuteQueryWithParams(" SELECT f.data_space_id as fileGroupId, " +
" f.file_id as fileId, " +
" f.name as fileName, " +
" f.physical_name as filePath, " +
" f.type as fileType " +
" FROM sys.master_files f " +
" WHERE f.type <= 2 " +
" AND f.database_id = ? ", databaseID);
for (; !dbFileSet.EOF; dbFileSet.MoveNext()) {
var fileType = dbFileSet("fileType").Value;
var fileGroupId = dbFileSet("fileGroupId").Value;
var fileId = dbFileSet("fileId").Value;
var fileName = dbFileSet("fileName").Value;
var filePath = dbFileSet("filePath").Value;
var fileGroupName = "";
var fileGroupType = "";
if (fileGroupId > 0) {
dbGroupConnection = this.common.SmartConnect(serverName, databaseName, tcpPort, targetComputer, instanceName);
var dbGroupSet = dbGroupConnection.ExecuteQueryWithParams("SELECT fg.name as fileGroupName, " +
" fg.data_space_id as fileGroupId, " +
" fg.type as fileGroupType " +
" FROM sys.filegroups fg where fg.data_space_id = ? ; ", fileGroupId);
if (!dbGroupSet.EOF) {
fileGroupName = dbGroupSet("fileGroupName").Value;
fileGroupType = dbGroupSet("fileGroupType").Value;
}
if (dbGroupConnection) {
dbGroupConnection.Close();
}
}
Home
CHT 
Show References: