Running multiply Powershell scripts at the same time

[Danny_Nilsson]

I have been working the past 2 weeks with getting powershell scripts and cron jobs stable. but find that when i run 6 scripts into the input.conf, i causes issue on some of the scripts randomly. one of the errors is

"Collection was modified; enumeration operation may not execute"

The thing is the powershell script is running the same PS1 file where the parameter is almost identical. and when i restart the service the same error can come from one of the other script and the one just failed

At the moment i have been able to fix the issue with implementing a sleep in front of every script, and this seems to work, but are not the ideal solution

Here is the debug log with the sleep. in basic it a script using dbatools and run some queries.

I do an import of dbatools at the beginning, and for the output it also looks good in splunk when the script runs succesful.

I tried to do an ISE where i added all the same script lines as in the input.conf, but are unable to reproduce it no matte how much i want it to fail.

i also had some different approaches, to collect all the script info to a variable and output at the bottom

run as Dot, Source

Run as a isolated function with powershell command

The only thing that make it stable is the sleep. 

06-30-2020 10:45:00.0225972+2 INFO Start executing script=sleep 3; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 35 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag35
06-30-2020 10:45:00.0235980+2 INFO Start executing script=sleep 4; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 46 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag46
06-30-2020 10:45:01.3045026+2 INFO End of executing script=sleep 1; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 27 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag27, execution_time=1.2849084 seconds
06-30-2020 10:45:02.2756997+2 INFO End of executing script=sleep 2; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 34 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag34, execution_time=2.2541032 seconds
06-30-2020 10:45:03.3188309+2 INFO End of executing script=sleep 3; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 35 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag35, execution_time=3.295233 seconds
06-30-2020 10:45:04.1506740+2 INFO End of executing script=sleep 4; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 46 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag46, execution_time=4.127076 seconds
06-30-2020 10:45:07.3460828+2 INFO End of executing script=sleep 6; ."C:\Program Files\SplunkUniversalForwarder\etc\apps\xxx\Bin\Get-NCSQLDiagsplunk.ps1" -QueryIDs 102 -SqlInstance localhost -SplunkEnable for stanza=NCSQLDiag102, execution_time=7.3264873 seconds

[Danny_Nilsson]

It seems the scripts shares the same run space some how. don't know what the best approach is to execute powershell scripts from splunks. seems like it causing a lot of trouble

here are some more error i get when receiving the exception from the scripts

Script failed System.Management.Automation.RuntimeException: The following error occurred while loading the extended type data file: Error in TypeData "Sqlcollaborative.Dbatools.dbaSystem.DbatoolsExceptionRecord": The member SerializationDepth is already present.
Error in TypeData "Sqlcollaborative.Dbatools.dbaSystem.DbatoolsException": The member SerializationDepth is already present.
Error in TypeData "Microsoft.SqlServer.Management.Smo.Server": The member Invoke is already present.
Error in TypeData "Microsoft.SqlServer.Management.Smo.Server": The member Query is already present.
Error in TypeData "Microsoft.SqlServer.Management.Smo.Database": The member Invoke is already present.
Error in TypeData "Microsoft.SqlServer.Management.Smo.Database": The member Query is already present.

at System.Management.Automation.Runspaces.InitialSessionState.ThrowTypeOrFormatErrors(String resourceString, String errorMsg, String errorId)
at System.Management.Automation.Runspaces.InitialSessionState.UpdateTypes(ExecutionContext context, Boolean updateOnly)
at System.Management.Automation.Runspaces.InitialSessionState.Bind_UpdateTypes(ExecutionContext context, Boolean updateOnly)
at System.Management.Automation.Runspaces.InitialSessionState.Bind(ExecutionContext context, Boolean updateOnly, PSModuleInfo module, Boolean noClobber, Boolean local)
at System.Management.Automation.Runspaces.InitialSessionState.Bind(ExecutionContext context, Boolean updateOnly)
at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadModuleManifest(String moduleManifestPath, ExternalScriptInfo manifestScriptInfo, Hashtable data, Hashtable localizedData, ManifestProcessingFlags manifestProcessingFlags, Version minimumVersion, Version maximumVersion, Version requiredVersion, Nullable`1 requiredModuleGuid, ImportModuleOptions& options, Boolean& containedErrors)

Script failed System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "WarningPreference" or common parameter is set to Stop: [15:00:05][Invoke-QueryRawDatabases] Failure | Method invocation failed because [Microsoft.SqlServer.Management.Smo.Server] does not contain a method named 'Query'.
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

[Danny_Nilsson]

just to say when adding a sleep; command in from of every script, over time they start to run successful