Skip to content

Create Custom Collectors

Jump to bottom
JosephPilov-msft edited this page Oct 24, 2022 · 6 revisions

At times, you need to create you own custom collector for use. Here are a couple of walk-thrus that allow you to create your own custom collectors (utility and T-SQL).

Create a Utility Custom Collector

  1. Once you have configured PSSDIAG and extracted the PSSD.ZIP, locate the pssdiag.xml file in the extracted folder

  2. Open it in a text editor and go to the bottom of the file where you can create a new CustomGroup XML element

  3. Copy and Paste the CustomGroup with name "SQL Server Perf Stats" and paste it right below it

  4. Now modify the new CustomGroup by changing the name and removing the top 3 of the custom tasks. The bottom custom task with name "Top CPU Query Show Plan XML" will remain

  5. Let's create a Custom Group named NetworkInfo with a custom Task named PingRequests, which will be a utility executing the PingTest.cmd

    <CustomGroup name="NetworkInfo" enabled="true">
  <CustomTask enabled="true" groupname="NetworkInfo" taskname="PingRequests" type="Utility" point="shutdown" wait="Yes" cmd="PingTest.cmd  &gt; &quot;%output_name%.TXT&quot; 2&gt;&amp;1" pollinginterval="0" />`
</CustomGroup>`

    The command (cmd) may seem a bit cryptic:

    PingTest.cmd  &gt; &quot;%output_name%.TXT&quot; 2&gt;&amp;1

    The line above is translated to this command:

    PingTest.cmd >"%output_name%.TXT" 2>&1

    NOTE: The %output_name% is the name of the task that you are running and it will be appended to the file name.

    Now your groups will look like this:

     <CustomGroup name="SQL Server Perf Stats" enabled="true">
      <CustomTask enabled="true" groupname="SQL Server Perf Stats" taskname="Perf Stats " type="TSQL_Script" point="Startup" wait="No" cmd="SQL Server Perf Stats.sql" pollinginterval="0" />
  <CustomTask enabled="true" groupname="SQL Server Perf Stats" taskname="Perf Stats Snapshot" type="TSQL_Script" point="Startup" wait="OnlyOnShutdown" cmd="SQL Server Perf Stats Snapshot.sql" pollinginterval="0" />
  <CustomTask enabled="true" groupname="SQL Server Perf Stats" taskname="Perf Stats Snapshot" type="TSQL_Script" point="shutdown" wait="OnlyOnShutdown" cmd="SQL Server Perf Stats Snapshot.sql" pollinginterval="0" />
  <CustomTask enabled="true" groupname="SQL Server Perf Stats" taskname="Top CPU Query Show Plan XML" type="Utility" point="shutdown" wait="Yes" cmd="TopCPUQueryShowPlanXML.bat 5 %output_name% %server_instance%" pollinginterval="0" />
</CustomGroup>
<CustomGroup name="NetworkInfo" enabled="true">
  <CustomTask enabled="true" groupname="NetworkInfo" taskname="PingRequests" type="Utility" point="shutdown" wait="Yes" cmd="PingTest.cmd  &gt; &quot;%output_name%.TXT&quot; 2&gt;&amp;1" pollinginterval="0" />
</CustomGroup>

    Note: Keep in mind that the 'enabled' attribute for the <CustomGroup> is not honored. Therefore, regardless of "false" or "true" the group will run. If you want to disable a task, you can set enabled="false" at the <CustomTask> level.

    <CustomGroup name="SQL Server Perf Stats" enabled="true">
  <CustomTask enabled="false" groupname="..." />
</CustomGroup>

  6. Save the pssdiag.xml

  7. Create and save a PingTest.cmd file in the \pssd folder with the following command inside it:

    ping www.microsoft.com

  8. Run pssdiag.cmd. Once you press CTRL+C to shut it down you will see your NetworkInfo collector:

     2021/05/15 04:23:38.74 SQLDIAG Ctrl+C pressed.  Shutting down the collector</ br>
 2021/05/15 04:23:39.26 servername\* Shutting down the collector
 2021/05/15 04:23:54.44 servername\* SQL Server Perf Stats: Perf Stats Snapshot
 2021/05/15 04:23:55.58 servername\* SQL Server Perf Stats: Top CPU Query Show Plan XML
 2021/05/15 04:23:57.31 servername\* NetworkInfo: PingRequests

  9. Look at the \output folder now and you will see this file:

    servername__NetworkInfo_PingRequests_Shutdown.TXT

Create a T-SQL Custom Collector

  1. Open the pssdiag.xml again

  2. Create a new line in the NetworkInfo <CustomGroup> with a T-SQL collector.

    • TaskName=SQLServerName
    • Type=TSQL_Script
    • cmd=SQLServerName.sql
    • point="shutdown"

    This is what the group would look like:

    <CustomGroup name="NetworkInfo" enabled="true">
  <CustomTask enabled="true" groupname="NetworkInfo" taskname="PingRequests" type="Utility" point="shutdown" wait="Yes" cmd="PingTest.cmd  &gt; &quot;%output_name%.TXT&quot; 2&gt;&amp;1" pollinginterval="0" />
  <CustomTask enabled="true" groupname="NetworkInfo" taskname="SQLServerName" type="TSQL_Script" point="shutdown" wait="OnlyOnShutdown" cmd="SQLServerName.sql" pollinginterval="0" />
</CustomGroup>

  3. Create a new file in your \pssd folder named SQLServerName.sql and add the following text in the file

    select serverproperty('servername')

  4. Save the pssdiag.xml if you have not already

  5. Execute **PSSDIAG **and stop it. You will see the new T-SQL collector SQLServerName

    2021/05/15 04:23:38.74 SQLDIAG Ctrl+C pressed.  Shutting down the collector
2021/05/15 04:23:39.26 servername\* Shutting down the collector
...  
2021/05/15 04:23:54.44 servername\* SQL Server Perf Stats: Perf Stats Snapshot
2021/05/15 04:23:55.58 servername\* SQL Server Perf Stats: Top CPU Query Show Plan XML
2021/05/15 04:23:57.31 servername\* NetworkInfo: PingRequests
2021/05/15 04:23:57.31 servername\* NetworkInfo: SQLServerName

  6. Review the output folder. You will see this file. Examine its output.

    servername__SQLServerName_Shutdown.OUT

Built-in variables

You may have noticed variables used in the pssdiag.xml file. Here is a clarification to some of them:

  • %output_name%- the name of output file name which will be produced. This is in the format: SERVERNAME_GROUPNAME_TASKNAME_TIMEOFCOLLECTION. It is a good idea for you to append the name of the file extension you want to see. Here is an example of a file LAPTOP1_SQL2016_HighCPU_perfstats_Startup.OUT
  • %output_path%- the full destination path to where PSSDIAG writes the output log files. Commonly this is the ..\Output folder
  • %sspath%- the path to the discovered SQL Server instance against which PSSDIAG is being executed. This is the parent folder of \Log subfolder. For example: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL
  • %appname% - the name of the executable that started this collection. This will be "SQLDIAG"
Clone this wiki locally