Follow

SQL Audit

SQL Audit :

Determine your SQL Server Version, Service Pack, and Edition using Power shell

Steps :

1) Create new custom field in Audit tab called “ SQL version “
2) Make Sure Power Execution Policy Unrestricted in end point if you can add below commands in attached PS file)

Set-ExecutionPolicy Unrestricted
Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned

3) import attached Script and PS1 file ..execute 

Tested with 2008 OS not sure about 2003

 

SQL version information Script:

<?xml version="1.0" encoding="utf-8"?>
<ScriptExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.kaseya.com/vsa/2008/12/Scripting">
  <Procedure name="SQL version information" treePres="3" id="264040338" folderId="41219414837261517866125287">
    <Body description="Using Power Shell Command and get the Result &#xA;&#xA;$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances&#xA;foreach ($i in $inst)&#xA;{&#xA;   $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i&#xA;   (Get-ItemProperty &quot;HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup&quot;).Edition&#xA;   (Get-ItemProperty &quot;HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup&quot;).Version&#xA;}&#xA;&#xA;">
      <If description="KITS-Software update">
        <Condition name="True" />
        <Then>
          <Statement description="" name="GetVariable" continueOnFail="false">
            <Parameter xsi:type="EnumParameter" name="VariableType" value="AgentTempDirectory" />
            <Parameter xsi:type="StringParameter" name="SourceContent" value="" />
            <Parameter xsi:type="StringParameter" name="VariableName" value="agentDrv" />
          </Statement>
          <Statement description="" name="WriteFile" continueOnFail="false">
            <Parameter xsi:type="StringParameter" name="Path" value="C:\SQLVer1.ps1" />
            <Parameter xsi:type="StringParameter" name="ManagedFile" value="VSASharedFiles\SQLVer1.ps1" />
            <Parameter xsi:type="BooleanParameter" name="DeleteAfter" value="False" />
          </Statement>
          <Statement description="Execute Powershell Command (64-bit, Run As System)" name="Execute Powershell Command (64-bit, Run As System)" continueOnFail="false">
            <Parameter xsi:type="StringParameter" name="Parameter1" value="C:\SQLVer1.ps1" />
            <Parameter xsi:type="StringParameter" name="Parameter2" value="" />
            <Parameter xsi:type="StringParameter" name="Parameter3" value="True" />
          </Statement>
          <Statement description="Write an Entry into the Procedure Log" name="WriteScriptLogEntry" continueOnFail="false">
            <Parameter xsi:type="StringParameter" name="Comment" value="#global:psresult#" />
          </Statement>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="3000" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2012 Service Pack 1" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="2100" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2012 RTM" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="10.50.4000.0" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 Service Pack 2" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="2500" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 Service Pack 1" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="1600" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 RTM" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="5500" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 3" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="10.00.4000.00" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 2" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="2531" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 1" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="1600.22" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 RTM" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="5000" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 4" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="4035" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 3" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="3042" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 2" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="2047" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 1" />
              </Statement>
            </Then>
          </If>
          <If description="">
            <Condition name="CheckVariable">
              <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" />
              <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" />
              <Parameter xsi:type="StringParameter" name="Value" value="1399" />
            </Condition>
            <Then>
              <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false">
                <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" />
                <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 RTM" />
              </Statement>
            </Then>
          </If>
          <Statement description="Delete the specified file - full path to the filename required." name="DeleteFile" continueOnFail="false">
            <Parameter xsi:type="StringParameter" name="Path" value="c:\kworking\psoutputtmp.txt" />
          </Statement>
        </Then>
      </If>
    </Body>
  </Procedure>
</ScriptExport>
Save below command as SQLVer1.ps1

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $inst)
{
$p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition
(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version
}

Thanks
Gopinath
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.