How to get the SQL command from a SQL task via Powershell? - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

Gary_W
Free support Topic Starter

2020-08-14T16:15:19Z

I'm fairly new to using the API on version 8.5.5. I'm trying to create a utility task that will let me search for a table or column name in the SQL command of a SQL task if that task is a type of "Text". The following snippet works, but the property "Command" is blank and from the documentation I see that is deprecated and to use "EncryptedCommand" instead. However, that gives me a big column of numbers. How to decrypt that so I can search the resulting string?

# Get all the jobs
    $jobs = Get-AllVCJobs
    Foreach ($job in $jobs)
    {
            $tasks = $job.Tasks # Get the Jobs' Tasks
            Foreach ($task in $tasks)
            {
              $sq = $task.SQL
              if ($task.TaskType -eq "SQL" -and $sq.CommandType -eq "Text")
              {
                 Write-Output ("Job: " + $job.Name + "  Task: " + $task.Name )
                 Write-Output($sq.EncryptedCommand)
              }
            }
    }

Edited by user 2020-08-14T16:15:57Z | Reason: Not specified

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

Gary_W
Free support Topic Starter

2020-08-17T19:54:05Z

I figured it out. Call the server's decrypt method. So line 12 in my snippet should look like below assuming you saved the server connection in $server:

Write-Output($server.decrypt($sq.EncryptedCommand))

However, sometimes the returned text contains a variable name. So now to figure out how to follow that to it's text....