Ask the Community
Groups
SQL Audit - Connect IT Community | Kaseya
<main> <article class="userContent"> <p><strong>Question</strong></p> <p>How to determine your SQL Server Version, Service Pack, and Edition using Power shell?</p> <p><strong>Answer:</strong></p> <p>1) Create new custom field in Audit tab called “ SQL version “<br>2) Make Sure Power Execution Policy Unrestricted in end point if you can add below commands in attached PS file)</p> <p>Set-ExecutionPolicy Unrestricted<br>Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned</p> <p>3) import attached Script and PS1 file ..execute <br><br>Tested with 2008 OS not sure about 2003</p> <p> </p> <p>SQL version information Script:</p> <p>�</p> <div><?xml version="1.0" encoding="utf-8"?></div> <div><ScriptExport xmlns:xsi="<a href="/home/leaving?allowTrusted=1&target=http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>" xmlns:xsd="<a href="/home/leaving?allowTrusted=1&target=http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema">http://www.w3.org/2001/XMLSchema</a>" xmlns="<a href="/home/leaving?allowTrusted=1&target=http%3A%2F%2Fwww.kaseya.com%2Fvsa%2F2008%2F12%2FScripting">http://www.kaseya.com/vsa/2008/12/Scripting</a>"></div> <div> <Procedure name="SQL version information" treePres="3" id="264040338" folderId="41219414837261517866125287"></div> <div> <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;"></div> <div> <If description="KITS-Software update"></div> <div> <Condition name="True" /></div> <div> <Then></div> <div> <Statement description="" name="GetVariable" continueOnFail="false"></div> <div> <Parameter xsi:type="EnumParameter" name="VariableType" value="AgentTempDirectory" /></div> <div> <Parameter xsi:type="StringParameter" name="SourceContent" value="" /></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="agentDrv" /></div> <div> </Statement></div> <div> <Statement description="" name="WriteFile" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="Path" value="C:\SQLVer1.ps1" /></div> <div> <Parameter xsi:type="StringParameter" name="ManagedFile" value="VSASharedFiles\SQLVer1.ps1" /></div> <div> <Parameter xsi:type="BooleanParameter" name="DeleteAfter" value="False" /></div> <div> </Statement></div> <div> <Statement description="Execute Powershell Command (64-bit, Run As System)" name="Execute Powershell Command (64-bit, Run As System)" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="Parameter1" value="C:\SQLVer1.ps1" /></div> <div> <Parameter xsi:type="StringParameter" name="Parameter2" value="" /></div> <div> <Parameter xsi:type="StringParameter" name="Parameter3" value="True" /></div> <div> </Statement></div> <div> <Statement description="Write an Entry into the Procedure Log" name="WriteScriptLogEntry" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="Comment" value="#global:psresult#" /></div> <div> </Statement></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="3000" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2012 Service Pack 1" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="2100" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2012 RTM" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="10.50.4000.0" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 Service Pack 2" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="2500" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 Service Pack 1" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="1600" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 R2 RTM" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="5500" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 3" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="10.00.4000.00" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 2" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="2531" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 Service Pack 1" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="1600.22" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2008 RTM" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="5000" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 4" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="4035" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 3" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="3042" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 2" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="2047" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 Service Pack 1" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <If description=""></div> <div> <Condition name="CheckVariable"></div> <div> <Parameter xsi:type="StringParameter" name="VariableName" value="#global:psresult#" /></div> <div> <Parameter xsi:type="EnumParameter" name="Condition" value="Contains" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="1399" /></div> <div> </Condition></div> <div> <Then></div> <div> <Statement description="Update the selected System Info field with the specified value for the agent this procedure runs on." name="UpdateSystemInfo" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="ColumnName" value="SQL Version" /></div> <div> <Parameter xsi:type="StringParameter" name="Value" value="SQL Server 2005 RTM" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> <Statement description="Delete the specified file - full path to the filename required." name="DeleteFile" continueOnFail="false"></div> <div> <Parameter xsi:type="StringParameter" name="Path" value="c:\kworking\psoutputtmp.txt" /></div> <div> </Statement></div> <div> </Then></div> <div> </If></div> <div> </Body></div> <div> </Procedure></div> <div></ScriptExport></div> <div> </div> <div> </div> <div>Save below command as SQLVer1.ps1</div> <div> </div> <div> </div> <div> <p>$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances<br>foreach ($i in $inst)<br>{<br>$p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i<br>(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition<br>(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version<br>}</p> </div> <div> </div> <div> </div> <div>Thanks<br>Gopinath</div> </article> </main>