SQL Audit

Question

How to determine your SQL Server Version, Service Pack, and Edition using Power shell?

Answer:

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 

$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
}

">

<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

Have more questions?

Was this article helpful?

0 out of 0 found this helpful

Provide feedback for the Documentation team!

Contact Documentation Team

Browse this section