Jump to content

Unexpected SQLDataSet Variable Issues

Recommended Posts

After spending most of my day troubleshooting a script involving a sqldataset, I thought I would share my experience here in case a similar issue is plaguing any other Automate admins.

I've got a script that gets a sqldataset containing a list of ComputerIDs where OS = Server 2008 R2. I want to loop through the dataset and run a couple of Shell commands on each ComputerID. 

My intent is to run this script against the Automate App server, though it shouldn't matter if I run it against ANY agent, since I only want the Shell commands to target the ComputerIDs returned in the dataset. However, it occurred to me during development that while the SQL Get DataSet function will run on the server, the Shell functions target the agent of the parent script (which would also be the Automate server in this instance).

Through much Googling, searches here on MSPGeek, and CW Documentation referencing, I determined that I could use the RELOAD INTERNAL VARIABLES function (actually Variable Set: Reload Computer Variables) to point my Shell commands at the new datasetrow target. Or so I thought....

After numerous Debug Script runs, I figured out that NONE of the following functions worked to execute the commands on the target system:

  • Execute Script: Powershell Bypass
  • Powershell Command as Admin
  • Process Execute as Admin
  • Shell Enhanced (attempting to run as Local System, or in some cases as the admin user running the target system agent)
  • Shell as Admin (attempting to run as the location-defined admin on the Deployment tab)
  • Script Run (with my shell command saved in a Function script - I was grabbing at straws by this point)

All results of my command returned, "Failed to log in as user".

Reviewing the variables at each step, I could see that Reload Computer Variables was successfully changing %clientid%, %locationid%, %computerid%, etc., to the correct values associated with each target device from the dataset. 

I confirmed that the correct credentials were referenced on each target location's Deployment tab, and that they were current (I was able to Control to target devices using the same credentials).

Finally, I noticed in the debugging window, there were a few User Variables which I hadn't set ... ones that I typically don't pay much attention to: @clientid@, @locationid@, and @computerid@, specifically. They were still populated with the values from script initialization. So while Reload Computer Variables was updating the internal variables, it was not updating their user variable counterparts.

After my Reload Computer Variables step, I added Variable Set functions to mirror the new internal variable values into their user variable counterparts (i.e., SET: @locationid@ = %locationid%). The next run of the debugger revealed that the user variable values got updated, and as soon as the Process Execute as Admin function ran, the %executeresult% internal variable finally indicated success. Apparently, even though subsequent functions reference the internal variable values, scripts attempting to elevate using the location Deployment settings draw their values from the user variable equivalents.

I first tested with only the @locationid@ value updated, and that also failed. So it appears that all three of these key user variable equivalents need to reflect the target device's information in order for script elevation via location Deployment settings to function as expected.

This was counterintuitive to what I understood from the documentation, so I wanted to post it here in case anyone else runs into a similar headache.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...