public static Dictionary <String, Object> EndTransaction(String txnName, BrowserWindow browser) { LOGGER.Debug("Started collecting data for Transaction : " + txnName.Replace(" ", "_") + " with status 1"); Dictionary <String, Object> rtnValue = new Dictionary <string, object>(); try { rtnValue = CollectorUtils.extractData(txnName.Replace(" ", "_"), browser.Uri.ToString(), browser, 1); } catch (Exception e) { LOGGER.Error("Exception collecting data for Transaction : " + txnName.Replace(" ", "_") + " with status 1 at " + e); } LOGGER.Debug("Completed collecting data for Transaction : " + txnName.Replace(" ", "_") + " with status 1"); return(rtnValue); }
private void FillCombo(ComboBox cb, String sql, Boolean nullable) { cb.DropDownStyle = ComboBoxStyle.DropDownList; DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); cb.Items.Clear(); if (nullable) { cb.Items.Add(new ComboboxItem(null, "")); } foreach (DataRow dr in dt.Rows) { cb.Items.Add(new ComboboxItem(dr[0], dr[1].ToString())); } cb.SelectedIndex = 0; }
private void fillValues(Guid collectionSetUid) { String sql = @" SELECT * FROM msdb.dbo.syscollector_collection_sets WHERE collection_set_uid = '{0}' "; sql = String.Format(sql, collectionSetUid.ToString()); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); foreach (DataRow dr in dt.Rows) { textBox1.Text = dr["collection_set_id"].ToString(); textBox7.Text = dr["collection_set_uid"].ToString(); textBox2.Text = dr["name"].ToString(); ComboSetValue(comboBox1, Convert.ToInt32(dr["collection_mode"])); if (!(dr["proxy_id"] == DBNull.Value)) { ComboSetValue(comboBox2, Convert.ToInt32(dr["proxy_id"])); } ComboSetValue(comboBox3, dr["schedule_uid"]); ComboSetValue(comboBox4, Convert.ToInt32(dr["logging_level"])); textBox3.Text = dr["days_until_expiration"].ToString(); textBox4.Text = dr["description"].ToString(); checkBox1.Checked = Boolean.Parse(dr["is_system"].ToString()); if (Convert.ToInt32(dr["is_running"]) == 0) { if (textBox1.Text != "") { textBox5.Text = "Not running"; button1.Enabled = true; button2.Enabled = false; } } else { if (textBox1.Text != "") { textBox5.Text = "Running"; button1.Enabled = false; button2.Enabled = true; } } } }
private void FillTablesCombo() { String sql = @" SELECT nds.v.value('.','sysname') AS OutputTable FROM msdb.dbo.syscollector_collection_items AS ci CROSS APPLY parameters.nodes('//OutputTable') AS nds(v) WHERE collection_item_id = {0} "; sql = String.Format(sql, collectionItemId); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); foreach (DataRow dr in dt.Rows) { comboBox1.Items.Add(dr["OutputTable"]); } }
public XMLEditor(String text, String type) { InitializeComponent(); _textControl = new ICSharpCode.TextEditor.TextEditorControl(); this.Controls.Add(_textControl); _textControl.Dock = DockStyle.Fill; _textControl.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy(type); this.Text = type + " Editor"; if (type.Equals("XML")) { _textControl.Text = CollectorUtils.FormatXMLDocument(text); } else { _textControl.Text = text; } }
private TreeNode[] PopulateCollectorTypes() { String sql = @" SELECT name, collection_package_name, upload_package_name, is_system, collector_type_uid FROM msdb.dbo.syscollector_collector_types ORDER BY is_system DESC, name "; DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); List <TreeNode> nodes = new List <TreeNode>(); foreach (DataRow dr in dt.Rows) { TreeNode tn = new TreeNode(dr["name"].ToString()); tn.Tag = dr["collector_type_uid"]; tn.ImageKey = "DataCollection"; tn.SelectedImageKey = "DataCollection"; nodes.Add(tn); } return(nodes.ToArray()); }
private TreeNode[] PopulateCollectionSets() { String sql = @" SELECT name, description, is_system, collection_set_uid, collection_set_id FROM msdb.dbo.syscollector_collection_sets ORDER BY is_system DESC, name "; DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); List <TreeNode> nodes = new List <TreeNode>(); foreach (DataRow dr in dt.Rows) { TreeNode tn = new TreeNode(dr["name"].ToString(), PopulateCollectionItems(Int32.Parse(dr["collection_set_id"].ToString()))); tn.Tag = dr["collection_set_uid"]; tn.ImageKey = "steps"; tn.SelectedImageKey = "steps"; nodes.Add(tn); } return(nodes.ToArray()); }
private void fillValues(int collectionItemId) { String sql = @" SELECT * FROM msdb.dbo.syscollector_collection_items WHERE collection_item_id = '{0}' "; sql = String.Format(sql, collectionItemId); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); foreach (DataRow dr in dt.Rows) { textBox1.Text = dr["collection_set_id"].ToString(); textBox7.Text = dr["collection_item_id"].ToString(); textBox2.Text = dr["name"].ToString(); ComboSetValue(comboBox1, (Guid)dr["collector_type_uid"]); textBox3.Text = dr["frequency"].ToString(); textBox4.Text = CollectorUtils.FormatXMLDocument(dr["parameters"].ToString()); } }
/* * Controls that the session is running. If it's not running */ private void CheckSession(XEReaderCollectionItemConfig itm) { String sql_check_session = @" SELECT name, state = CASE WHEN EXISTS ( SELECT 1 FROM sys.dm_xe_sessions AS x WHERE name = sess.name ) THEN 'ON' ELSE 'OFF' END FROM sys.server_event_sessions AS sess WHERE name = '{0}' "; sql_check_session = String.Format(sql_check_session, itm.SessionName); DataTable sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session); if (sess.Rows.Count <= 0) { // create the session CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", itm.SessionDefinition); // repeat the check to see if // the session has been started sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session); } if (sess.Rows[0]["state"].ToString().Equals("OFF")) { // TODO: start the session String sql_startSession = @" ALTER EVENT SESSION [{0}] ON SERVER STATE = start; "; sql_startSession = String.Format(sql_startSession, itm.SessionName); CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", sql_startSession); } }
private TreeNode[] PopulateCollectionItems(int collection_set_id) { String sql = @" SELECT collection_item_id, name FROM msdb.dbo.syscollector_collection_items WHERE collection_set_id = '{0}' ORDER BY name "; sql = String.Format(sql, collection_set_id); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); List <TreeNode> nodes = new List <TreeNode>(); foreach (DataRow dr in dt.Rows) { TreeNode tn = new TreeNode(dr["name"].ToString()); tn.Tag = dr["collection_item_id"]; tn.ImageKey = "package"; tn.SelectedImageKey = "package"; nodes.Add(tn); } return(nodes.ToArray()); }
private void WriteCacheFile(DataTable collectedData, CollectionItemConfig itm) { String ts = DateTime.Now.ToString("yyyyMMddHHmmss"); String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index; String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache"); if (verbose) { logger.logMessage("Saving to cache file " + destFile); } // // Save data to a binary cache file // if (File.Exists(destFile)) { File.Delete(destFile); } System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); using (FileStream fs = new FileStream(destFile, FileMode.CreateNew)) { fm.Serialize(fs, collectedData); fs.Close(); } if (verbose) { logger.logMessage("File saved successfully"); } collectedData.Rows.Clear(); }
private void fillValues(Guid collectorTypeUid) { String sql = @" SELECT * FROM msdb.dbo.syscollector_collector_types WHERE collector_type_uid = '{0}' "; sql = String.Format(sql, collectorTypeUid.ToString()); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql); foreach (DataRow dr in dt.Rows) { textBox1.Text = dr["collector_type_uid"].ToString(); textBox2.Text = dr["name"].ToString(); textBox3.Text = CollectorUtils.FormatXMLDocument(dr["parameter_schema"].ToString()); textBox4.Text = CollectorUtils.FormatXMLDocument(dr["parameter_formatter"].ToString()); textBox5.Text = dr["collection_package_path"].ToString(); button1.Tag = dr["collection_package_id"].ToString(); textBox6.Text = dr["upload_package_path"].ToString(); button2.Tag = dr["upload_package_id"].ToString(); checkBox1.Checked = Boolean.Parse(dr["is_system"].ToString()); } }
private void checkCollectionSetEnabled() { String sql = @" SELECT is_running FROM msdb.dbo.syscollector_collection_sets WHERE collection_set_uid = '{0}' "; sql = String.Format(sql, this.CollectionSetUid); while (true) { Boolean is_running = (Boolean)CollectorUtils.GetScalar(SourceServerInstance, "master", sql); if (!is_running) { stopCollector(); } else { Thread.Sleep(60000); } } }
/* * Creates the target table from the * output definition of the query */ protected override String createTargetTable( CollectorConfig cfg, CollectionItemConfig itm ) { String TableName = itm.OutputTable; TSQLCollectionItemConfig itemCfg = (TSQLCollectionItemConfig)itm; String CollectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index; // // checks if the table exists and which schema it belongs // String checkedTableName = checkTable(cfg, itm); if (checkedTableName != null) { return(checkedTableName); } String statement = @" IF NOT EXISTS ( SELECT * FROM sys.servers WHERE NAME = 'LOOPBACK' ) BEGIN DECLARE @srv nvarchar(4000); SET @srv = @@SERVERNAME; -- gather this server name -- Create the linked server EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N'SQLServ', -- it's not a typo: it can't be 'SQLServer' @provider = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000 @datasrc = @srv; -- Set the authentication to 'current security context' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOOPBACK', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL; END USE tempdb; GO IF OBJECT_ID('{0}') IS NOT NULL DROP PROCEDURE [{0}] GO CREATE PROCEDURE [{0}] AS BEGIN SET NOCOUNT ON; {1} END GO IF SCHEMA_ID('custom_snapshots') IS NULL EXEC('CREATE SCHEMA [custom_snapshots]') IF OBJECT_ID('custom_snapshots.{2}') IS NOT NULL DROP TABLE [custom_snapshots].[{2}] GO SELECT TOP 0 *, CAST(NULL AS sysname) AS _database_name, CAST(NULL AS datetimeoffset(7)) AS _collection_time, CAST(NULL AS int) AS _snapshot_id INTO tempdb.[custom_snapshots].[{2}] FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF; EXEC tempdb.dbo.[{0}]'); DROP PROCEDURE [{0}]; GO IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'database_name' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[database_name]', '__database_name', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_database_name]', 'database_name', 'COLUMN'; IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'collection_time' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[collection_time]', '__collection_time', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_collection_time]', 'collection_time', 'COLUMN'; IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'snapshot_id' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[snapshot_id]', '__snapshot_id', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_snapshot_id]', 'snapshot_id', 'COLUMN'; " ; statement = String.Format(statement, CollectorId, itemCfg.Query, itm.OutputTable); CollectorUtils.InvokeSqlBatch(SourceServerInstance, "tempdb", statement); String scriptText = CollectorUtils.ScriptTable(SourceServerInstance, "tempdb", TableName); CollectorUtils.InvokeSqlCmd(cfg.MDWInstance, cfg.MDWDatabase, scriptText); return("[custom_snapshots].[" + TableName + "]"); }
public void CollectData( String SourceServerInstance, Guid CollectionSetUid, int ItemId ) { CollectorLogger logger = new CollectorLogger(SourceServerInstance, CollectionSetUid, ItemId); DataTable collectedData = null; if (verbose) { logger.logMessage("--------------------------------"); } if (verbose) { logger.logMessage(" ExtendedTSQLCollector "); } if (verbose) { logger.logMessage("--------------------------------"); } if (verbose) { logger.logMessage("Copyright© sqlconsulting.it 2014"); } if (verbose) { logger.logMessage("-"); } if (verbose) { logger.logMessage("Loading configuration"); } // // Load Configuration // TSQLCollectorConfig cfg = new TSQLCollectorConfig(); cfg.readFromDatabase(SourceServerInstance, CollectionSetUid, ItemId); if (verbose) { logger.logMessage("Entering collection items loop"); } foreach (CollectionItemConfig item in cfg.collectionItems) { TSQLCollectionItemConfig itm = (TSQLCollectionItemConfig)item; if (verbose) { logger.logMessage("Processing item n. " + itm.Index); } if (verbose) { logger.logMessage("Processing query " + itm.Query); } collectedData = null; String ts = DateTime.Now.ToString("yyyyMMddHHmmss"); String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index; String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache"); // // Iterate through the enabled databases // if (verbose) { logger.logMessage("Entering databases loop"); } foreach (String currentDatabase in cfg.Databases) { if (verbose) { logger.logMessage("Processing database " + currentDatabase); } // // Execute the query in the collection item // DataTable dt = CollectorUtils.GetDataTable(SourceServerInstance, currentDatabase, itm.Query); // // Add computed columns // if (dt.Columns.Contains("database_name")) { dt.Columns["database_name"].ColumnName = "__database_name"; } DataColumn cl_db = new DataColumn("database_name", typeof(String)); cl_db.DefaultValue = currentDatabase; dt.Columns.Add(cl_db); if (dt.Columns.Contains("collection_time")) { dt.Columns["collection_time"].ColumnName = "__collection_time"; } DataColumn cl_dt = new DataColumn("collection_time", typeof(DateTime)); cl_dt.DefaultValue = DateTime.Now; dt.Columns.Add(cl_dt); // // Merge collected data in a single DataTable // if (collectedData != null) { collectedData.Merge(dt); } else { collectedData = dt; collectedData.DataSet.RemotingFormat = System.Data.SerializationFormat.Binary; collectedData.RemotingFormat = System.Data.SerializationFormat.Binary; } } if (verbose) { logger.logMessage("Saving to cache file " + destFile); } // // Save data to a binary cache file // if (File.Exists(destFile)) { File.Delete(destFile); } System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); using (FileStream fs = new FileStream(destFile, FileMode.CreateNew)) { fm.Serialize(fs, collectedData); fs.Close(); } if (verbose) { logger.logMessage("File saved successfully"); } } logger.cleanupLogFiles(cfg.DaysUntilExpiration); }
private Boolean deleteItem(TreeNode selectedNode) { String sql; int ConnectionTimeout = 15; int QueryTimeout = 600; String ConnectionString = String.Format("Server={0};Database={1};Integrated Security=True;Connect Timeout={2}", Manager.ServerName, "msdb", ConnectionTimeout); try { if (selectedNode.Parent == tnCollectionSets) { // delete collection set using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); sql = "dbo.sp_syscollector_delete_collection_set"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = QueryTimeout; SqlCommandBuilder.DeriveParameters(cmd); DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", String.Format("SELECT collection_set_id FROM syscollector_collection_sets WHERE collection_set_uid = '{0}'", selectedNode.Tag)); int collection_set_id = Convert.ToInt32(dt.Rows[0]["collection_set_id"]); cmd.Parameters["@collection_set_id"].Value = collection_set_id; cmd.ExecuteNonQuery(); } return(true); } if (selectedNode.Parent == tnCollectorTypes) { // delete collector type using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); sql = "dbo.sp_syscollector_delete_collector_type"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = QueryTimeout; SqlCommandBuilder.DeriveParameters(cmd); cmd.Parameters["@collector_type_uid"].Value = new Guid(selectedNode.Tag.ToString()); cmd.ExecuteNonQuery(); } return(true); } if (selectedNode.Parent.Parent == tnCollectionSets) { // delete collection item using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); sql = "dbo.sp_syscollector_delete_collection_item"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = QueryTimeout; SqlCommandBuilder.DeriveParameters(cmd); cmd.Parameters["@collection_item_id"].Value = Convert.ToInt32(selectedNode.Tag.ToString()); cmd.ExecuteNonQuery(); } return(true); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(false); }
private void btnSave_Click(object sender, EventArgs e) { String verb = "create"; if (textBox7.Text.Length == 0) { verb = "create"; } else { verb = "update"; } String sql = @"dbo.sp_syscollector_{0}_collection_item"; sql = String.Format(sql, verb); int ConnectionTimeout = 15; int QueryTimeout = 600; String ConnectionString = String.Format("Server={0};Database={1};Integrated Security=True;Connect Timeout={2}", Manager.ServerName, "msdb", ConnectionTimeout); int collection_item_id = -1; int collection_set_id; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = QueryTimeout; SqlCommandBuilder.DeriveParameters(cmd); if (verb.Equals("update")) { collection_item_id = Convert.ToInt32(textBox7.Text); collection_set_id = Convert.ToInt32(textBox1.Text); CollectorUtils.InvokeSqlCmd(Manager.ServerName, "msdb", "UPDATE syscollector_collection_items SET collector_type_uid = '" + ((ComboboxItem)comboBox1.SelectedItem).Value.ToString() + "' WHERE collection_item_id = " + collection_item_id); cmd.Parameters["@collection_item_id"].Value = collection_item_id; cmd.Parameters["@new_name"].Value = textBox2.Text; } else { collection_set_id = Convert.ToInt32(textBox1.Text); cmd.Parameters["@collection_set_id"].Value = collection_set_id; cmd.Parameters["@name"].Value = textBox2.Text; cmd.Parameters["@collector_type_uid"].Value = ((ComboboxItem)comboBox1.SelectedItem).Value; cmd.Parameters["@collection_item_id"].Direction = ParameterDirection.Output; } cmd.Parameters["@frequency"].Value = Convert.ToInt32(textBox3.Text); cmd.Parameters["@parameters"].Value = textBox4.Text; cmd.ExecuteNonQuery(); if (verb.Equals("create")) { collection_item_id = Convert.ToInt32(cmd.Parameters["@collection_item_id"].Value.ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } } fillValues(collection_item_id); _main.RefreshTreeView(); }