Community forum

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


  1. Forum
  2. Support
  3. API
  4. How to get the SQL command from a SQL task via Powershell?
Gary_W
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)
              }
            }
    }
 Reply
 View All Posts by User
Sponsor
Forum information
Gary_W
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....
 Reply
 View All Posts by User
Similar Topics
Users browsing this topic
  1. Forum
  2. Support
  3. API
  4. How to get the SQL command from a SQL task via Powershell?
 

© VisualCron

Scroll to Top