<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Textbox Name="Textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Create Windows Counter Capture Job</Value>
                <Style>
                  <FontSize>20pt</FontSize>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox1</rd:DefaultName>
        <Top>0.27167in</Top>
        <Left>0.38in</Left>
        <Height>0.51667in</Height>
        <Width>7.375in</Width>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <BackgroundColor>LightSteelBlue</BackgroundColor>
          <VerticalAlign>Middle</VerticalAlign>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Textbox Name="Textbox2">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>USE [msdb]


/****** Object:  Job [MonitorWindowsPerfCounters]    Script Date: 5/11/2023 7:18:55 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorWindowsPerfCounters')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorWindowsPerfCounters', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job captures the Windows counters using Powershell.  Found that having them all in one step helps manage the CPU utilization.  If there are different steps the CPU utilization will jump 3 to 5 percent.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureCounters]    Script Date: 5/11/2023 7:18:55 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureCounters', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'

$DateOfCapture = get-date -Format "MM/dd/yyyy HH:mm:ss"
$serverName = "LocalHost"
$databaseName = "AdminMonitor"
$tableName = "dbo.WindowsCountersCaptured"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=''$serverName'';database=''$databaseName'';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

$Counter = Get-Counter -counter "\System\Processor Queue Length"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()

$Counter = Get-Counter -counter  "\PhysicalDisk(_Total)\Avg. Disk sec/Write"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\PhysicalDisk(_Total)\Avg. Disk sec/Read"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()



$Counter = Get-Counter -counter  "\Paging File(_Total)\% Usage"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\Memory\Available MBytes"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\SQLServer:Buffer Manager\buffer cache hit ratio"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()



$Counter = Get-Counter -counter  "\Processor(_Total)\% Processor Time"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()

$Connection.Close();', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every30Sec', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230401, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</Value>
                <Style />
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox2</rd:DefaultName>
        <Top>2.355in</Top>
        <Left>0.38in</Left>
        <Height>49.9in</Height>
        <Width>7.375in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <BackgroundColor>LightGrey</BackgroundColor>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Textbox Name="Textbox3">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>This code create the job to capture the Windows performance counters.  Export this report to Word to copy and paste.</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox3</rd:DefaultName>
        <Top>0.9925in</Top>
        <Left>0.38in</Left>
        <Height>1.08195in</Height>
        <Width>3.30833in</Width>
        <ZIndex>2</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <BackgroundColor>LightGrey</BackgroundColor>
          <VerticalAlign>Middle</VerticalAlign>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Textbox Name="Textbox4">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Back to Previous Page</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox4</rd:DefaultName>
        <ActionInfo>
          <Actions>
            <Action>
              <Drillthrough>
                <ReportName>MonitorCreateJobsCode</ReportName>
              </Drillthrough>
            </Action>
          </Actions>
        </ActionInfo>
        <Top>0.9925in</Top>
        <Left>3.92167in</Left>
        <Height>1.08195in</Height>
        <Width>3.83333in</Width>
        <ZIndex>3</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <BackgroundColor>LightSteelBlue</BackgroundColor>
          <VerticalAlign>Middle</VerticalAlign>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>53.58834in</Height>
    <Style>
      <Border>
        <Style>None</Style>
      </Border>
      <BackgroundColor>DarkBlue</BackgroundColor>
    </Style>
  </Body>
  <Width>8.23333in</Width>
  <Page>
    <PageHeight>455in</PageHeight>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>97e2571f-f3b8-4c8a-a03a-32c9f45f3a3e</rd:ReportID>
</Report>