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"]);
}
/*
* Execute the data upload process
*/
public void UploadData()
{
logger = new CollectorLogger(SourceServerInstance, CollectionSetUid, ItemId);
String displayName;
String[] names = this.GetType().Namespace.Split('.');
displayName = names[names.Length - 1];
if (verbose)
{
logger.logMessage("--------------------------------");
}
if (verbose)
{
logger.logMessage(" " + displayName);
}
if (verbose)
{
logger.logMessage("--------------------------------");
}
if (verbose)
{
logger.logMessage("Copyright© sqlconsulting.it 2014");
}
if (verbose)
{
logger.logMessage("-");
}
if (verbose)
{
logger.logMessage("Loading configuration");
}
//
// Load Configuration
//
CollectorConfig cfg = null;
Guid CollectorTypeUid = new Guid("00000000-0000-0000-0000-000000000000");
if (this.GetType().Namespace.Equals("Sqlconsulting.DataCollector.ExtendedXEReaderUploader"))
{
cfg = new XEReaderCollectorConfig();
CollectorTypeUid = XEReaderCollectionItemConfig.CollectorTypeUid;
}
else if (this.GetType().Namespace.Equals("Sqlconsulting.DataCollector.ExtendedTSQLUploader"))
{
cfg = new TSQLCollectorConfig();
CollectorTypeUid = TSQLCollectionItemConfig.CollectorTypeUid;
}
cfg.readFromDatabase(SourceServerInstance, CollectionSetUid, ItemId);
//String collectorId = CollectionSetUid + "_" + ItemId.ToString();
if (verbose)
{
logger.logMessage("Updating source info");
}
//
// Update Source Info
//
int source_id = updateDataSource(cfg.MDWInstance, cfg.MDWDatabase, CollectionSetUid, cfg.MachineName, cfg.InstanceName, cfg.DaysUntilExpiration);
int snapshot_id = -1;
foreach (CollectionItemConfig item in cfg.collectionItems)
{
String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + item.Index;
//
// Create the target table
//
String targetTable = createTargetTable(cfg, item);
Boolean tableCreated = (targetTable != null);
foreach (String fileName in System.IO.Directory.GetFiles(cfg.CacheDirectory))
{
System.IO.FileInfo destFile = new System.IO.FileInfo(fileName);
//if (verbose) logger.logMessage("Processing " + destFile.FullName);
//if (verbose) logger.logMessage("Searching " + collectorId);
if (destFile.Name.Contains(collectorId + "_") && destFile.Extension.ToLowerInvariant().Equals(".cache"))
{
if (verbose)
{
logger.logMessage("Uploading " + destFile.FullName);
}
DataTable collectedData = null;
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
//
// Deserialize from the binary file
//
using (System.IO.FileStream fs = new System.IO.FileStream(destFile.FullName, System.IO.FileMode.Open))
{
collectedData = (DataTable)fm.Deserialize(fs);
fs.Close();
}
//
// Load the snapshot_id
//
if (snapshot_id < 0)
{
if (verbose)
{
logger.logMessage("Creating snapshot");
}
snapshot_id = createSnapshot(cfg.MDWInstance, cfg.MDWDatabase, CollectionSetUid, CollectorTypeUid, cfg.MachineName, cfg.InstanceName, LogId);
}
//
// Add the snapshot_id column to the DataTable
//
DataColumn cl_sn = new DataColumn("snapshot_id", typeof(Int32));
cl_sn.DefaultValue = snapshot_id;
collectedData.Columns.Add(cl_sn);
//
// Check again if table needs to be created
//
if (!tableCreated)
{
targetTable = createTargetTable(cfg, item, collectedData);
tableCreated = true;
}
if (verbose)
{
logger.logMessage("Writing to server... " + targetTable);
}
CollectorUtils.WriteDataTable(cfg.MDWInstance, cfg.MDWDatabase, targetTable, collectedData);
if (verbose)
{
logger.logMessage("Deleting file");
}
destFile.Delete();
}
}
}
logger.cleanupLogFiles(cfg.DaysUntilExpiration);
}
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"]);
}