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"]); }