//set up radio buttons for features, versions etc
private void SetupRadioButtons <T> (GroupBox gb, List <T> mylist)
{
Int32 Current_Y = Initial_Y;
foreach (T item in mylist)
{
RadioButton button = new RadioButton();
DiagItem evt = item as DiagItem;
Feature ft = item as Feature;
//we currently don't support public verioin of AS
if (ft != null && ft.Name == "AS" && DiagRuntime.IsPublicVersion == true)
{
continue;
}
//if this version is not enabled, don't display, for example 2005 is no longer supported
Version ver = evt as Version;
if (ver != null && ver.Enabled == false)
{
continue;
}
button.Text = evt.FriendlyName;
button.Width = 120;
button.Tag = item;
button.Location = new System.Drawing.Point(X, Current_Y);
gb.Controls.Add(button);
Current_Y += Y_Interval;
}
}
static void PopulateTemplateFeatureVersion(XPathNodeIterator iter, DiagItem evt)
{
XPathNodeIterator iterLocalFeatures = iter.Current.Select("Features/Feature");
evt.EnabledFeatures = GetLocalFeatureList(iterLocalFeatures);
XPathNodeIterator iterLocalVersions = iter.Current.Select("Versions/Version");
evt.EnabledVersions = GetLocalVersionList(iterLocalVersions);
XPathNodeIterator iterLocalTemplates = iter.Current.Select("Scenarios/Scenario");
evt.EnabledTemplate = GetLocalScenarioList(iterLocalTemplates);
}
void SetCustomDiag(XmlNode node)
{
// if SQL Xevent needs to be captured
if (m_Setting[Res.CollectXEvent] == "true")
{
DiagCategory XEventGroup = DiagFactory.GetCustomGroup(null, @"Templates\CustomDiagnostics_Xevent.xml");
//need to manuall handle taskname "Add File Target" for file size and file count
DiagItem taskItem = XEventGroup.DiagEventList.Find(x => x.Name == "Add File Target"); // for CustomTask Name is the same as TaskName
CustomTask task = taskItem as CustomTask;
task.Cmd = task.Cmd.Replace("1024x", m_Setting[Res.XEventMaxFileSize]).Replace("5x", m_Setting[Res.XEventFileCount]);
//manually check events for special handling
XEventGroup.CheckEvents(true);
m_Setting.CustomDiagCategoryList.Add(XEventGroup);
}
//speical handling Analysis Service (A_S)
string customDiagxml = m_Setting.CustomDiagCategoryList.GetCategoryXml();
if (m_Setting[Res.Feature] == "AS")
{
string ServerInstance = string.Empty;
string instance = string.Empty;
string InstanceName = m_Setting[Res.InstanceName];
string MachineName = m_Setting[Res.MachineName];
string ASTraceFileName = string.Empty;
if (!(InstanceName.ToUpper() == "MSSQLSERVER"))
{
ServerInstance = MachineName + @"\" + InstanceName;
ASTraceFileName = MachineName + "_" + InstanceName;
}
else
{
ServerInstance = MachineName;
ASTraceFileName = MachineName;
}
customDiagxml = customDiagxml.Replace("ASSERVERINSTANCE", ServerInstance);
customDiagxml = customDiagxml.Replace("ASTraceFileName", ASTraceFileName);
}
node.InnerXml = customDiagxml;
//CustomDiagnosticsNode.InnerXml = DiagTreeMgr.GetXmlText(tv_CustomDiag);
}
private void MakeManualBatchFiles()
{
if (m_Instance == "*")
{
return;
}
StreamWriter ManualStart = File.CreateText(m_tempDirectory + @"\ManualStart.txt");
StreamWriter ManualStop = File.CreateText(m_tempDirectory + @"\ManualStop.txt");
ManualStart.WriteLine("REM PURPOSE: This file is for cases where execution of PSSDIAG does not work for some reason. It allows you to manually collect some base information.");
ManualStart.WriteLine("REM This includes Perfmon, Perfstat scripts and some other configuration information for the instance (sp_configure, sys.databses, etc)");
ManualStart.WriteLine("REM INSTRUCTIONS:");
ManualStart.WriteLine("REM 1. Rename the file to ManualStart.cmd (change the extension)");
ManualStart.WriteLine("REM 2. Rename the ManualStop.txt to ManualStop.cmd (change the extension)");
ManualStart.WriteLine("REM 3. Execute from a Command Prompt by running ManualStart.cmd");
ManualStart.WriteLine("REM 4. When ready to stop, execute ManualStop.cmd from another Command Prompt window");
ManualStart.WriteLine("REM 5. Find the collected data in the \\Output folder");
ManualStart.WriteLine("");
ManualStop.WriteLine("REM PURPOSE: This file is for cases where execution of PSSDIAG does not work for some reason. This file stops manual collection of base information.");
ManualStop.WriteLine("REM INSTRUCTIONS:");
ManualStop.WriteLine("REM 1. Rename the file to ManualStop.cmd (change the extension)");
ManualStop.WriteLine("REM 2. When ready to stop collection, execute ManualStop.cmd from a new Command Prompt window");
ManualStop.WriteLine("REM 3. Find the collected data in the \\Output folder");
ManualStop.WriteLine("");
ManualStart.WriteLine("setlocal ENABLEEXTENSIONS");
ManualStart.WriteLine("set LaunchDir=%~dp0");
ManualStart.WriteLine("set servername=%computername%");
ManualStop.WriteLine("setlocal ENABLEEXTENSIONS");
ManualStop.WriteLine("set LaunchDir=%~dp0");
ManualStop.WriteLine("set servername=%computername%");
ManualStart.WriteLine("md \"%LaunchDir%\\output\\internal\"");
//need to test on a cluster and see if . does anything for VNN
//TODO: NEED TO EXCLUDE FROM HASH CALCULATION
ManualStart.WriteLine(string.Format(SqlCmdTemplate, m_Server_Instance, m_output_instance_prefix + "_msdiagprocs.out", "-i\"" + m_input_prefix + "msdiagprocs.sql" + "\""));
//make manual profile collector
if (m_userchoice[Res.CollectProfiler] == "true")
{
string strTraceEvents = "";
foreach (DiagItem diagItem in m_userchoice.ProfilerCategoryList.GetCheckedDiagItemList())
{
TraceEvent evt = diagItem as TraceEvent;
if (!string.IsNullOrEmpty(strTraceEvents))
{
strTraceEvents += ",";
}
strTraceEvents += evt.Id.ToString();
}
string MaxFileSize = m_userchoice[Res.ProfilerMaxFileSize];
string FileCount = m_userchoice[Res.ProfilerFileCount];
string strTraceStartQuery = string.Format(StartTraceTemplate, strTraceEvents, m_AppName, m_output_instance_prefix + "_sp_trace", MaxFileSize, FileCount);
string strTraceStopQuery = string.Format(StopTraceTemplate, m_AppName);
ManualStart.WriteLine("rem starting profiler trace");
string cmd = string.Format(SqlCmdTemplate, m_Server_Instance, m_internal_output_instance_prefix + "_sp_trace_start.out", "-Q\"" + strTraceStartQuery + "\"");
ManualStart.WriteLine(cmd);
ManualStop.WriteLine("rem stoping profile trace");
cmd = string.Format(SqlCmdTemplate, m_Server_Instance, m_internal_output_instance_prefix + "_sp_trace_stop.out", "-Q\"" + strTraceStopQuery + "\"");
ManualStop.WriteLine(cmd);
}
//make a copy
List <DiagCategory> checkedCategories = new List <DiagCategory>();
foreach (DiagCategory cat in m_userchoice.CustomDiagCategoryList.GetCheckedCategoryList())
{
checkedCategories.Add(cat);
}
// if SQL Xevent needs to be captured
if (m_userchoice[Res.CollectXEvent] == "true")
{
DiagCategory XEventGroup = DiagFactory.GetCustomGroup(null, @"Templates\CustomDiagnostics_Xevent.xml");
//need to manuall handle taskname "Add File Target" for file size and file count
DiagItem taskItem = XEventGroup.DiagEventList.Find(x => x.Name == "Add File Target"); // for CustomTask Name is the same as TaskName
CustomTask task = taskItem as CustomTask;
task.Cmd = task.Cmd.Replace("1024x", m_userchoice[Res.XEventMaxFileSize]).Replace("5x", m_userchoice[Res.XEventFileCount]);
XEventGroup.Name = "XEvent";
//manually check events for special handling
XEventGroup.CheckEvents(true);
checkedCategories.Add(XEventGroup);
}
foreach (DiagCategory cat in checkedCategories)
{
ManualStart.WriteLine("rem group: " + cat.Name);
foreach (DiagItem item in cat.GetCheckedEventList())
{
CustomTask task = item as CustomTask;
//currently do not handle anything other than TSQL script
if (null == task)
{
continue;
}
if (task.Type.ToUpper() == "TSQL_SCRIPT")
{
string cmd = "Start ";
if (task.GroupName == "XEvent")
{
cmd = ""; //make sure xevent is last group
}
cmd = cmd + string.Format(SqlCmdTemplate, m_Server_Instance, m_output_instance_prefix + "_" + task.Cmd + ".out", "-i\"" + m_input_prefix + task.Cmd + "\"");
if (task.Point.ToUpper() == "STARTUP")
{
ManualStart.WriteLine(cmd);
}
else
{
ManualStop.WriteLine(cmd);
}
}
/*
* //various issues exists for batch files.
* else if (task.Type.ToUpper() =="UTILITY")
* {
* // working example
* //start collecterrorlog.cmd jackli2014\sql16a 1 ^> "c:\temp\pssd\out5.txt" 2>&1 ^&^&exit
* //
* string cmd = "start " + ReplaceToken(task.Name, task.Cmd);
* cmd = cmd.Replace(">", "^>").Replace("2^>&1", "2>&1");
* cmd = cmd + " &exit";
* if (task.Point.ToUpper() == "STARTUP")
* {
* ManualStart.WriteLine(cmd);
* }
* else
* {
* ManualStop.WriteLine(cmd);
* }
*
*
* }*/
else if (task.Type.ToUpper() == "TSQL_COMMAND")
{
string cmd = "Start ";
if (task.GroupName == "XEvent")
{
cmd = ""; // can do this because it's the last event
}
cmd = cmd + string.Format(SqlCmdTemplate, m_Server_Instance, m_output_instance_prefix + "_" + task.TaskName + ".out", "-Q\"" + task.Cmd + "\"");
cmd = cmd.Replace(@"%output_path%%server%_%instance%", m_output_instance_prefix);
if (task.Point.ToUpper() == "STARTUP")
{
ManualStart.WriteLine(cmd);
}
else
{
ManualStop.WriteLine(cmd);
}
}
else
{
Logger.LogInfo("Manual run hasnot been implemented for task type " + task.Type);
}
//task.Cmd
}
}
ManualStart.WriteLine("rem starting Perfmon");
ManualStart.WriteLine(string.Format("logman stop {0}", LogManName));
ManualStart.WriteLine(string.Format("logman delete {0}", LogManName));
ManualStart.WriteLine(string.Format("logman CREATE COUNTER -n {0} -s {1} -cf {2} -f bin -si 00:00:15 -o {3} -ow ", LogManName, m_userchoice[Res.MachineName], "LogmanConfig.txt", @"output\pssdiag.blg"));
ManualStart.WriteLine(string.Format("logman start {0} ", LogManName));
ManualStop.WriteLine(string.Format("logman stop {0}", LogManName));
ManualStop.WriteLine(string.Format(SqlCmdTemplate, m_Server_Instance, m_output_instance_prefix + "_kill.out", "-Q tempdb.dbo.sp_killpssdiagSessions"));
//logman CREATE COUNTER -n SQLServer -s AlwaysOnN1 -cf C:\dsefiles\dse_counters.txt -f bin -si 00:00:15 -o C:\dsefiles\SQL.blg
ManualStart.Flush();
ManualStart.Close();
ManualStop.Flush();
ManualStop.Close();
}
/// <summary>
///
/// </summary>
/// <param name="XmlFile"> XML template file</param>
/// <param name="CatClause">select clause for Category of the Event such as PerfmonObject, EventType for trace, Category for XEvent</param>
/// <param name="EventClause">select clause for the event. eg. PerfmonCounter, Event etc</param>
/// <param name="evtType">Enum</param>
/// <returns>List of Categories</returns>
static List <DiagCategory> GetEventCategoryList(string XmlFile, string CatClause, string EventClause, EventType evtType)
{
List <DiagCategory> catList = new List <DiagCategory>();
XPathDocument traceDoc = new XPathDocument(XmlFile); //new XPathDocument("TraceEvents.xml");
XPathNavigator rootnav = traceDoc.CreateNavigator();
XPathNodeIterator iter = rootnav.Select(CatClause); //rootnav.Select("TraceEvents/EventType");
while (iter.MoveNext())
{
string catname = iter.Current.GetAttribute("name", "");
DiagCategory cat = new DiagCategory(iter.Current.Name, catname);
catList.Add(cat);
PopulateTemplateFeatureVersion(iter, cat);
XPathNodeIterator iterEvents = iter.Current.Select(EventClause); // iter.Current.Select("Event");
while (iterEvents.MoveNext())
{
string evtname = iterEvents.Current.GetAttribute("name", "");
DiagItem evt = null; //set to NULL because it will create child event for each type
if (evtType == EventType.TraceEvent)
{
string trcid = iterEvents.Current.GetAttribute("id", "");
TraceEvent trcevt = new TraceEvent(cat, iterEvents.Current.Name, evtname, trcid);
evt = trcevt;
}
else if (evtType == EventType.Perfmon)
{
evt = new DiagItem(cat, iterEvents.Current.Name, evtname);
}
else if (evtType == EventType.XEvent)
{
string evtpackage = iterEvents.Current.GetAttribute("package", "");
evt = new Xevent(cat, iterEvents.Current.Name, evtname, evtpackage);
//read field elements from eventfields section
XPathNodeIterator iterEventFields = iterEvents.Current.Select("eventfields/field");
while (iterEventFields.MoveNext())
{
string name = iterEventFields.Current.GetAttribute("name", "");
bool autoinclude = Convert.ToBoolean(iterEventFields.Current.GetAttribute("AutoInclude", ""));
bool isnum = Convert.ToBoolean(iterEventFields.Current.GetAttribute("IsNum", ""));
EventField evtField = new EventField(name, autoinclude, isnum);
(evt as Xevent).EventFieldList.Add(evtField);
}
//read action elements from eventactions section
XPathNodeIterator iterEventActions = iterEvents.Current.Select("eventactions/action");
while (iterEventActions.MoveNext())
{
string package = iterEventActions.Current.GetAttribute("package", "");
string name = iterEventActions.Current.GetAttribute("name", "");
EventAction evtAction = new EventAction(package, name);
(evt as Xevent).EventActionList.Add(evtAction);
}
}
else
{
throw new ArgumentException("GetEventCategoryList doesn't know how to handle this type of event yet");
}
PopulateTemplateFeatureVersion(iterEvents, evt);
cat.DiagEventList.Add(evt);
}
}
return(catList);
}
