protected String checkTable(CollectorConfig cfg, CollectionItemConfig itm)
{
String sqlCheck = @"
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) AS targetTable
FROM [{0}].sys.tables
WHERE name = '{1}'
AND schema_id IN (SCHEMA_ID('custom_snapshots'), SCHEMA_ID('snapshots'))
ORDER BY CASE SCHEMA_NAME(schema_id)
WHEN 'custome_snapshots' THEN 1
WHEN 'snapshots' THEN 2
END ";
sqlCheck = String.Format(sqlCheck, cfg.MDWDatabase, itm.OutputTable);
DataTable data = CollectorUtils.GetDataTable(cfg.MDWInstance, cfg.MDWDatabase, sqlCheck);
// table is not missing
if (data.Rows.Count > 0)
{
return(data.Rows[0]["targetTable"].ToString());
}
else
{
if (verbose)
{
logger.logMessage("Creating target table " + itm.OutputTable);
}
return(null);
}
}
protected int createSnapshot(
String TargetServerInstance,
String TargetDatabase,
Guid collection_set_uid,
Guid collector_type_uid,
String machine_name,
String instance_name,
int log_id
)
{
String qry = @"
DECLARE @snapshot_id int;
EXEC [core].[sp_create_snapshot]
@collection_set_uid = '{0}',
@collector_type_uid = '{1}',
@machine_name = '{2}',
@named_instance = '{3}',
@log_id = {4},
@snapshot_id = @snapshot_id OUTPUT;
SELECT @snapshot_id AS snapshot_id;
" ;
if (instance_name.Trim().Equals(""))
{
instance_name = "MSSQLSERVER";
}
qry = String.Format(qry, collection_set_uid, collector_type_uid, machine_name, instance_name, log_id);
DataTable data = CollectorUtils.GetDataTable(TargetServerInstance, TargetDatabase, qry);
return(Convert.ToInt32(data.Rows[0]["snapshot_id"]));
}
private int updateDataSource(
String TargetServerInstance,
String TargetDatabase,
Guid collection_set_uid,
String machine_name,
String named_instance,
int days_until_expiration
)
{
String qry = @"
DECLARE @src_id int;
EXEC [core].[sp_update_data_source]
@collection_set_uid = '{0}',
@machine_name = '{1}',
@named_instance = '{2}',
@days_until_expiration = {3},
@source_id = @src_id OUTPUT;
SELECT @src_id AS src_id;
" ;
if (named_instance.Trim().Equals(""))
{
named_instance = "MSSQLSERVER";
}
qry = String.Format(qry, collection_set_uid, machine_name, named_instance, days_until_expiration);
DataTable data = CollectorUtils.GetDataTable(TargetServerInstance, TargetDatabase, qry);
return(Convert.ToInt32(data.Rows[0]["src_id"]));
}
public virtual void readFromDatabase(String ServerInstance)
{
String qry = @"
SELECT *
FROM [msdb].[dbo].[syscollector_config_store]
PIVOT(
MAX(parameter_value)
FOR parameter_name IN (
[CacheDirectory]
,[CacheWindow]
,[CollectorEnabled]
,[MDWDatabase]
,[MDWInstance]
)
) AS p
" ;
DataTable data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
DataRow row = data.Rows[0];
CacheDirectory = row["CacheDirectory"].ToString();
CacheWindow = Convert.ToInt32(row["CacheWindow"]);
CollectorEnabled = Convert.ToBoolean(row["CollectorEnabled"]);
MDWDatabase = row["MDWDatabase"].ToString();
MDWInstance = row["MDWInstance"].ToString();
if (String.IsNullOrEmpty(CacheDirectory))
{
CacheDirectory = System.Environment.GetEnvironmentVariable("temp");
}
qry = @"
SELECT CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS MachineName
,ISNULL(CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)),'') AS InstanceName
" ;
data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
row = data.Rows[0];
MachineName = row["MachineName"].ToString();
InstanceName = row["InstanceName"].ToString();
}
public override void readFromDatabase(
String ServerInstance,
Guid CollectionSetUid,
int ItemId
)
{
readFromDatabase(ServerInstance);
String qry = @"
DECLARE @x xml;
DECLARE @fre int;
SELECT @x = parameters,
@fre = frequency
FROM msdb.dbo.syscollector_collection_items
WHERE collection_set_id = (
SELECT collection_set_id
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
)
AND collection_item_id = {1}
AND collector_type_uid = '{2}';
;WITH XMLNAMESPACES('DataCollectorType' AS ns)
SELECT x.value('Value[1]','varchar(max)') AS query,
x.value('OutputTable[1]', 'varchar(max)') AS outputTable,
@fre AS frequency
FROM @x.nodes('/ns:TSQLQueryCollector/Query') Q(x)
ORDER BY outputTable;
" ;
qry = String.Format(qry, CollectionSetUid, ItemId, TSQLCollectionItemConfig.CollectorTypeUid);
DataTable data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
int i = 1;
foreach (DataRow currentRow in data.Rows)
{
TSQLCollectionItemConfig cic = new TSQLCollectionItemConfig();
cic.Query = currentRow["query"].ToString();
cic.OutputTable = currentRow["outputTable"].ToString();
cic.Frequency = Int32.Parse(currentRow["frequency"].ToString());
cic.Index = i;
i++;
collectionItems.Add(cic);
}
qry = @"
DECLARE @x xml;
SELECT @x = parameters
FROM msdb.dbo.syscollector_collection_items
WHERE collection_set_id = (
SELECT collection_set_id
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
)
AND collection_item_id = {1}
DECLARE @selectedDatabases TABLE (
name sysname
)
;WITH XMLNAMESPACES('DataCollectorType' AS ns)
INSERT INTO @selectedDatabases
SELECT x.value('.','varchar(max)') AS database_name
FROM @x.nodes('/ns:TSQLQueryCollector/Databases/Database') Q(x)
DECLARE @useSystemDB bit
DECLARE @UseUserDB bit
;WITH XMLNAMESPACES('DataCollectorType' AS ns)
SELECT @useSystemDB =
CASE LOWER(@x.value('(/ns:TSQLQueryCollector/Databases/@UseSystemDatabases)[1]','varchar(5)'))
WHEN 'true' THEN 1 WHEN 'false' THEN 0 ELSE NULL
END,
@useUserDB =
CASE LOWER(@x.value('(/ns:TSQLQueryCollector/Databases/@UseUserDatabases)[1]','varchar(5)'))
WHEN 'true' THEN 1 WHEN 'false' THEN 0 ELSE NULL
END
-- delete non-existing databases
DELETE t
FROM @selectedDatabases AS t
WHERE name NOT IN (
SELECT name
FROM sys.databases
)
INSERT INTO @selectedDatabases
SELECT name
FROM sys.databases
WHERE 1 =
CASE
WHEN @useSystemDB = 1 AND name IN ('master','model','msdb','distribution') THEN 1
WHEN @UseUserDB = 1 AND name NOT IN ('master','model','msdb','distribution') THEN 1
END
AND NOT EXISTS (
SELECT *
FROM @selectedDatabases
)
INSERT INTO @selectedDatabases
SELECT 'master'
WHERE NOT EXISTS (
SELECT *
FROM @selectedDatabases
)
SELECT *
FROM @selectedDatabases
" ;
qry = String.Format(qry, CollectionSetUid, ItemId);
data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
i = 1;
foreach (DataRow currentRow in data.Rows)
{
Databases.Add(currentRow["name"].ToString());
}
qry = @"
SELECT days_until_expiration
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
" ;
qry = String.Format(qry, CollectionSetUid);
data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
DaysUntilExpiration = Convert.ToInt32(data.Rows[0]["days_until_expiration"]);
}
public override void readFromDatabase(
String ServerInstance,
Guid CollectionSetUid,
int ItemId
)
{
readFromDatabase(ServerInstance);
String qry = @"
DECLARE @x xml;
DECLARE @fre int;
DECLARE @is_running bit;
SELECT @x = parameters,
@fre = frequency,
@is_running = is_running
FROM msdb.dbo.syscollector_collection_items AS ci
CROSS APPLY (
SELECT collection_set_id, is_running
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
) AS cs
WHERE ci.collection_set_id = cs.collection_set_id
AND collection_item_id = {1}
AND collector_type_uid = '{2}';
;WITH XMLNAMESPACES('DataCollectorType' AS ns)
SELECT
x.value('Name[1]','varchar(max)') AS xe_session_name,
x.value('OutputTable[1]', 'varchar(max)') AS outputTable,
x.value('Definition[1]', 'varchar(max)') AS xe_session_definition,
x.value('Filter[1]', 'varchar(max)') AS xe_session_filter,
x.value('ColumnsList[1]', 'varchar(max)') AS xe_session_columnslist,
@fre AS frequency,
@is_running AS is_enabled
FROM @x.nodes('/ns:ExtendedXEReaderCollector/Session') Q(x)
ORDER BY outputTable;
" ;
qry = String.Format(qry, CollectionSetUid, ItemId, XEReaderCollectionItemConfig.CollectorTypeUid);
DataTable data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
int i = 1;
foreach (DataRow currentRow in data.Rows)
{
XEReaderCollectionItemConfig cic = new XEReaderCollectionItemConfig();
cic.SessionName = currentRow["xe_session_name"].ToString();
cic.OutputTable = currentRow["outputTable"].ToString();
cic.Frequency = Int32.Parse(currentRow["frequency"].ToString());
cic.SessionDefinition = currentRow["xe_session_definition"].ToString();
cic.Filter = currentRow["xe_session_filter"].ToString();
cic.Columns = new List <String>(currentRow["xe_session_columnslist"].ToString().Split(','));
cic.Enabled = Boolean.Parse(currentRow["is_enabled"].ToString());
cic.Index = i;
i++;
collectionItems.Add(cic);
}
qry = @"
DECLARE @x xml;
SELECT @x = parameters
FROM msdb.dbo.syscollector_collection_items
WHERE collection_set_id = (
SELECT collection_set_id
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
)
AND collection_item_id = {1}
AND collector_type_uid = '{2}';
;WITH XMLNAMESPACES('DataCollectorType' AS ns)
SELECT
x.value('Sender[1]','varchar(max)') AS alert_sender,
x.value('Recipient[1]','varchar(max)') AS alert_recipient,
x.value('Filter[1]', 'varchar(max)') AS alert_filter,
x.value('ColumnsList[1]', 'varchar(max)') AS alert_columnslist,
x.value('Mode[1]', 'varchar(max)') AS alert_mode,
x.value('Importance[1]', 'varchar(max)') AS alert_importance_level,
x.value('Delay[1]', 'varchar(max)') AS alert_delay,
x.value('Subject[1]', 'varchar(max)') AS alert_subject,
x.value('Body[1]', 'varchar(max)') AS alert_body,
x.value('AttachmentFileName[1]', 'varchar(max)') AS alert_attachment_filename,
x.value('@WriteToERRORLOG[1]', 'varchar(max)') AS alert_write_to_errorlog,
x.value('@WriteToWindowsLog[1]', 'varchar(max)') AS alert_write_to_windowslog,
x.value('@Enabled[1]', 'varchar(max)') AS alert_enabled
FROM @x.nodes('/ns:ExtendedXEReaderCollector/Alert') Q(x);
" ;
qry = String.Format(qry, CollectionSetUid, ItemId, XEReaderCollectionItemConfig.CollectorTypeUid);
data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
i = 1;
foreach (DataRow currentRow in data.Rows)
{
AlertConfig a = new AlertConfig();
a.Sender = currentRow["alert_sender"].ToString();
a.Recipient = currentRow["alert_recipient"].ToString();
a.Filter = currentRow["alert_filter"].ToString();
a.Columns = new List <String>(currentRow["alert_columnslist"].ToString().Split(','));
a.Mode = (Sqlconsulting.DataCollector.Utils.AlertMode)
Enum.Parse(typeof(Sqlconsulting.DataCollector.Utils.AlertMode), currentRow["alert_mode"].ToString());
a.Importance = (Sqlconsulting.DataCollector.Utils.ImportanceLevel)
Enum.Parse(typeof(Sqlconsulting.DataCollector.Utils.ImportanceLevel), currentRow["alert_importance_level"].ToString());
a.Delay = Int32.Parse(currentRow["alert_delay"].ToString());
a.Subject = currentRow["alert_subject"].ToString();
a.WriteToErrorLog = Boolean.Parse(currentRow["alert_write_to_errorlog"].ToString());
a.WriteToWindowsLog = Boolean.Parse(currentRow["alert_write_to_windowslog"].ToString());
a.Enabled = Boolean.Parse(currentRow["alert_enabled"].ToString());
XEReaderCollectionItemConfig itmcfg = (XEReaderCollectionItemConfig)collectionItems[0];
itmcfg.Alerts.Add(a);
}
qry = @"
SELECT days_until_expiration
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_uid = '{0}'
" ;
qry = String.Format(qry, CollectionSetUid);
data = CollectorUtils.GetDataTable(ServerInstance, "msdb", qry);
DaysUntilExpiration = Convert.ToInt32(data.Rows[0]["days_until_expiration"]);
}