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();
}
