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