PowerShell not executing

Brian_Winter

Given:

import-module SMLets

import-module SQLServer

$SQLInstance = "MYServer"

$SQLDatabase = "MyDatabase"

$SQLQuery = "Update Variables set Value = 'Value' where UniqueID like '4ACCE14F-D956-4069-8A24-AF8D333258BD';"

Invoke-Sqlcmd -Database $SQLDatabase -HostName $SQLInstance -OutputSqlErrors $True -Query $SQLQuery -ServerInstance $SQLInstance

 $SQLQuery = "Update Variables set Value = 0 where UniqueID like 'AE7DF220-FAFC-41E6-B3DB-2B7766ACEE2F';"

Invoke-Sqlcmd -Database $SQLDatabase -HostName $SQLInstance -OutputSqlErrors $True -Query SQLQuery -ServerInstance $SQLInstance

I can run this no problem in the PowerShell ISE but it will not run in an "Execute PS Script" widget in an Orchestrator RunBook! 🤬

Suggestions? TIA!

Brett_Moffett

Hi @Brian_Winter

What errors or feedback are you getting from the PS Script activity in Orchestrator?

If you run the runbook via the Runbook Tester, you should be able to get it to output errors to the system for you to watch and record.

You could also use a TRY trap to write errors out to a file so you can see where things are breaking down.

Side note... Why not use the PowerShell activity rather than calling a Runbook to then run PowerShell? Much faster and easier to use without the need for the Orchestrator server....

Geoff_Ross

Hi @Brett_Moffett

"Execute PS Script"??? Is that the third party activity, rather than the OOB Microsoft .net Script activity?

Geoff

Brett_Moffett

There was one.... until Microsoft closed codeplex!

The Orchestrator Integration Pack for PowerShell Script Execution 1.2 is now available - Microsoft Tech Community

Brian_Winter

Hi @Brett_Moffett and @Geoff_Ross . That IP is the one I'm using, but I also tried using the "Run .Net Script" as well. The .Net didn't like the SCSM commandlets even with the SMLets loaded. I'll put in some reporting today and give an update.

This RB was designed before we had sufficient expertise with PS. It's not attached to SCSM, but is a standalone Run-Once. It pulls all users and then all assets for each user and builds an email. The script was only supposed to build the string listing the assets for the email. It worked in 2012R2, but doesn't behave the same on 2016.

If I had the chance, I'd rewrite the whole thing in a few hundred lines of code and this would be a non-issue.

Geoff_Ross

Hi Brian,

I'm not super familiar with that IP but the OOB one still runs PS version 2.0 in a 32 bit process. (Despite Microsoft repeating saying they would address this).

This can cause one of two issues (or both).

1. Module paths can be wrong because they assume 64 bit process.
2. Modules can require higher than PS version 2.0

There are various hacks etc around this - my preference is to just use the Script to execute a new instance of PowerShell.exe and pass in a ps1 file.

Geoff

Matt_Howard1

Brian--

Give this a try: https://get-cmd.com/?p=3731

Set a registry key on the Runbook Server to use the latest CLR (4.0). This fixed some issues we had running newer modules.

Path: HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\.NETFramework

Value: OnlyUseLatestCLR

Data: 1

Type: DWORD

PowerShell:

New-ItemProperty -Path HKLM:\SOFTWARE\WOW6432Node\Microsoft\.NETFramework -Name OnlyUseLatestCLR -PropertyType DWORD -Value 1

Brian_Winter

I suspect there's some issue with the Execute-SQL command. I decided to abandon this approach and used a return to pass along the string I was building.

A problem for another day. Thanks anyway!