private void LoadGridData()
{
if (String.IsNullOrEmpty(comboBox1.Text))
{
return;
}
String sql = "SELECT SERVERPROPERTY('ServerName') AS SN";
String SN = (String)CollectorUtils.GetScalar(Manager.ServerName, "master", sql);
sql = @"
SELECT TOP(1) QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id))
FROM sys.tables
WHERE name = '{0}'
ORDER BY OBJECT_SCHEMA_NAME(object_id) DESC";
sql = String.Format(sql, comboBox1.Text);
String selectedTable = (String)CollectorUtils.GetScalar(MDWInstance, MDWDatabase, sql);
sql = @"
SELECT source.*
FROM core.snapshots AS sn
INNER JOIN {3} AS source
ON source.snapshot_id = sn.snapshot_id
WHERE sn.instance_name = '{0}'
AND sn.snapshot_time BETWEEN '{1}' AND '{2}'
ORDER BY snapshot_time;
";
DateTime dateFrom = DateTime.Parse(textBox1.Text);
DateTime dateTo = DateTime.Parse(textBox2.Text);
sql = String.Format(sql, SN, dateFrom.ToString("yyyy-MM-dd hh:mm:ss.fff"), dateTo.ToString("yyyy-MM-dd hh:mm:ss.fff"), selectedTable);
DataTable dt = CollectorUtils.GetDataTable(MDWInstance, MDWDatabase, sql);
dataGridView1.DataSource = dt;
}
private static void loadXECollectionItems(String ServerInstance, Boolean Verbose)
{
String sql = @"
SELECT cs.collection_set_uid,
ci.collection_item_id
FROM msdb.dbo.syscollector_collection_items AS ci
INNER JOIN msdb.dbo.syscollector_collection_sets AS cs
ON ci.collection_set_id = cs.collection_set_id
WHERE is_running = 1
AND collector_type_uid = '{0}';
";
sql = String.Format(sql, XEReaderCollectionItemConfig.CollectorTypeUid);
Boolean keepLooping = true;
try
{
while (keepLooping)
{
DataTable dt = CollectorUtils.GetDataTable(ServerInstance, "master", sql);
if (dt.Rows.Count == 0)
{
keepLooping = false;
break;
}
foreach (DataRow dr in dt.Rows)
{
Guid CollectionSetUid = new Guid(dr["collection_set_uid"].ToString());
int ItemId = Int32.Parse(dr["collection_item_id"].ToString());
CollectionItemTask t = CollectionItemTask.Create(ServerInstance, CollectionSetUid, ItemId, Verbose);
if (!runningTasks.ContainsKey(t.GetKey()))
{
t.Start();
runningTasks.TryAdd(t.GetKey(), t);
initializing = false;
}
}
Thread.Sleep(60000);
}
}
catch (Exception e)
{
initializing = false;
throw e;
}
}
private void FillTimeSlider()
{
if (String.IsNullOrEmpty(comboBox1.Text))
{
return;
}
String sql = @"
SELECT TOP(1) QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id))
FROM sys.tables
WHERE name = '{0}'
ORDER BY OBJECT_SCHEMA_NAME(object_id) DESC";
sql = String.Format(sql, comboBox1.Text);
String selectedTable = (String)CollectorUtils.GetScalar(MDWInstance, MDWDatabase, sql);
sql = @"
SELECT MIN(sn.snapshot_time) AS mintime, MAX(sn.snapshot_time) AS maxtime
FROM core.snapshots AS sn
INNER JOIN {0} AS source
ON source.snapshot_id = sn.snapshot_id
";
sql = String.Format(sql, selectedTable);
DataTable dt = CollectorUtils.GetDataTable(MDWInstance, MDWDatabase, sql);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
if (dr[0] == null || dr[1] == null)
{
return;
}
if (dr[0].GetType() == typeof(DBNull) || dr[1].GetType() == typeof(DBNull))
{
return;
}
DateTime min = ((DateTimeOffset)dr[0]).DateTime;
DateTime max = ((DateTimeOffset)dr[1]).DateTime;
dateRangeSlider1.Minimum = min;
dateRangeSlider1.Maximum = max;
dateRangeSlider1.LowerValue = min;
dateRangeSlider1.UpperValue = max;
dateRangeSlider1.LowerValue = min;
dateRangeSlider1.UpperValue = max;
}
}
private TreeNode[] PopulateFolder(Guid folderid)
{
String sql = @"
SELECT 'Package' AS Type, P.id, P.name
FROM msdb.dbo.sysssispackages AS P
WHERE P.folderid = '{0}'
UNION ALL
SELECT 'Folder' AS Type, F.folderid, F.foldername
FROM msdb.dbo.sysssispackagefolders AS F
WHERE F.parentfolderid = '{0}'
";
sql = String.Format(sql, folderid);
DataTable dt = CollectorUtils.GetDataTable(Manager.ServerName, "msdb", sql);
List <TreeNode> nodes = new List <TreeNode>();
foreach (DataRow dr in dt.Rows)
{
if (dr["Type"].ToString().Equals("Folder"))
{
TreeNode tn = null;
TreeNode[] children = PopulateFolder((Guid)dr["id"]);
if (children.Length > 0)
{
tn = new TreeNode(dr["name"].ToString(), children);
}
else
{
tn = new TreeNode(dr["name"].ToString());
}
tn.ImageKey = "folder";
tn.SelectedImageKey = "folder";
nodes.Add(tn);
}
else
{
TreeNode tn = new TreeNode(dr["name"].ToString());
tn.ImageKey = "package";
tn.SelectedImageKey = "package";
tn.Tag = dr["id"];
nodes.Add(tn);
}
}
return(nodes.ToArray());
}
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"]);
}
}
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 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());
}
}
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);
}
