Workload Automation1

Expand all | Collapse all

plumbing windows jobs to powershell files

  • 1.  plumbing windows jobs to powershell files

    Posted 22 days ago
    ​Hi.  I just got started with ca workload automation.  I've created an event and set up a related series of 4 jobs connected by dependencies in my app.  I have links on the front and back also for what I understand could support inter app dependencies later.  I've set up various user accounts for dev test, etc.

    I'd like to relate each job to a powershell script that would be executed when that job runs but don't understand how agents, servers and the command line come into play at this juncture.  Or if an agent is even necessary.  I get the feeling agents relate to a server and are woken up by ca wa.  I cant find any documentation on this subject, especially the setting up of agents.

     I see the "command to run" text box related to each job when I rt click and edit..  its been recommended to me that the agent group be a ca variable.  Can somebody fill me in a little on what my options are and where I would go from here?

    jobs with dependencies



  • 2.  RE: plumbing windows jobs to powershell files

    Posted 22 days ago
    Hi,
    You would need an agent installed on the windows server(s).  If you want to use an agent group you would set that up in ADMIN - Topology with all the agents the job can run on.  On the application properties you would specify the agent or agent group that the majority of the jobs would use.  On the jobs for agent if you leave application default checked will take whatever server is on the application properties, if you want you can uncheck and put another agent there. Also, on the job you would fill in the script/command to run and any arguments the script needs. ​Sometimes you need a userid and sometimes you do not.  If you do need a userid, that userid and password would have to be added to the agent in ADMIN - Topology and the userid would go on the job.


  • 3.  RE: plumbing windows jobs to powershell files

    Posted 22 days ago
    thx, you have answered the question so this is just gravy.  from your experience do larger companies share the ​same servers for housing ps scripts belonging to multiple business groups or do the groups themselves leverage existing file servers they already own?  The reason I ask is that management and purposing would seem more economical using the first choice.  But perhaps ps cmdlet "installs" can rock (in a bad way) one group's world while trying to satisfy the needs of another.


  • 4.  RE: plumbing windows jobs to powershell files

    Posted 22 days ago
    we have a script that is reusable by many apps that is housed on a centeral server and through the script and arguments goes to the appropriate server.  For app specific scripts they are server specific (or possibly 2 servers for HA) for security reasons.  ​


  • 5.  RE: plumbing windows jobs to powershell files

    Posted 22 days ago
    thx, ours are all app specific but the user id under which the ca jobs run would (I hope) take care of any security concerns.​


  • 6.  RE: plumbing windows jobs to powershell files

    Posted 8 days ago
    Our bigger business groups have dedicated servers for their line of business, in part because they have dedicated developers who actually code the scripts we schedule.
    We do have a good share of shared servers as well though.

    I sense your questions have been answered, but here is how we have things set up.

    Agent:  In order to make it easy to move DE applications between various environments (Dev, Stage, Prod) we use a variable for the Agent Name.  If the application is run in Dev, the agent variable will resolve to a a development server ( ex. FTPD04), and if the same application is copied and run in Prod the agent will resolve to the equivalent production server (ex FTPP04)

    Command to run: if you want to execute Powershell then this field should be:
    C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

    Arguments to pass: again, if you are executing Powershell the first argument you will likely pass to it is the path to the PS1 file you want to run.:
    -command "C:\Cyb_exec\your_powershell.ps1"

    If your powershell is short enough to fit onto one line, you can just put the whole command into the Argument to pass.  For example, the following command will strip a 16 character timestamp from filenames of the files in a specific folder.

    Get-ChildItem -Path D:\Inetpub\FTProot\PROD\IN\*.csv | foreach { rename-item $_.FullName -newname ($_.BaseName.Substring(0,$_.BaseName.length - 16) + $_.Extension)}

    ------------------------------
    Andy Reimer
    ------------------------------



  • 7.  RE: plumbing windows jobs to powershell files

    Posted 7 days ago
    Edited by stan teitelbaum 7 days ago
    thx Andy, that brings up an interesting question for me.  We put the full path to the ps1 file in the ca command rather than a reference to an exe.  My thinking was that the os would relate that file type to powershell and execute accordingly the same way it does when i double click on ps1 files on other servers.

    We know the job isnt working when i trigger it from the client.  i asked for permission to rdp to the server so i can try to double click the file myself and determine if perhaps ps cmdlets havent been installed.  The idea would be to determine if the problem is downstream of ca or upstream.  i have to wait till fri 11/8 to get that rdp permission.

    In the mean time i'm a little unclear on how the name of a file as a parameter would be passed to the exe (arguments to pass).  Would it be on the same line  in the ca command or via another text box.  I'm going back to the client now to familiarize myself with the options.  If its in a separate box, i have to wonder how one param is differentiated from potentially others.  Maybe its that -command designation you showed

    I did go back and enter the ps exe in the command ...and then -command followed by the full ps1 path in quotes in the arg to pass... and then simulate.  Same results (none).  This is probably on us to figure out, not the forum.


  • 8.  RE: plumbing windows jobs to powershell files

    Posted 7 days ago
    By default Windows does not execute a PS1 file when it is double clicked.  It opens it in Notepad.  There are presumably ways around this, but it would likely require changes in the user id profile that matched the User ID specified on the job description.  My guess is if you schedule a job to run and nothing is happening, you will find a notepad.exe process running under the user id the agent is using.

    I was going to upload an image of one of our powershell jobs but for some reason (site / browser / security / who knows) I can't load images into the forum post. but here are some exact values we use.

    Command to run: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
    Argument to Pass: -command "C:\Cyb_exec\PatchNight\Reboot_Server.ps1" SQLP25CAWM

    Powershell can handle both named and positional arguments.  In this case the first argument is named by -command and specifies which PS1 file to run.  The second argument is technically the first argument that will be passed into the PS1 (in this case the name of a server I want to reboot).

    ------------------------------
    Andy Reimer
    ------------------------------



  • 9.  RE: plumbing windows jobs to powershell files

    Posted 7 days ago
    Edited by stan teitelbaum 7 days ago
    thx Andy , that resonates with me.  I think when i originally (long time ago) tested the ps to sql server plumbing downstream of ca on test servers, a ps window opened up where i had to choose an exec icon under ps ise to actually run the script.  And of course that choice was probably a server by server decision based on certain settings, probably default settings.  I'll revisit that exercise and post back here.

    If that's the case, the possibilities get narrower.  And most probably the user id (really a non expiring nt account) we assigned to the ca d series job doesnt have exec privileges on ps on the agent machine.  we gave it exec on the directory where the ps file resides but that is starting to sound like the wrong way to go.


  • 10.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago
    did due diligence and indeed this worked the way you said Andy.  Below is a summary of what I did.  I will be going back to the ca client and entering the exe as the command , ps1 file as the argument to pass the way you showed and I will also get exec permission on the path to poweshell itself for the non expiring user we have on the job.  For good measure, I'll make sure the userid also has read on the path to the ps1 file even though it already has exec.  I'll probably request removal of the exec permission the non expiring userid has on the path to the ps1 file.

    going back to powershell testing in prep for setting up ca differently, i went to testserver, double clicked
      C:\...\catopowershelltestsqlbased.ps1 and indeed notepad opened up.  I right clicked the same and chose run with powershell and the record did show up
      (the test script inserts a record on a different server that is sql) on [db].[stan].[catopowershelltest].  I ran the full path in ps ise in the command line area
      (bottom) and the record also got inserted.  I rt clicked the file, hit edit and the script showed up in the ise's script area.  i ran it from there with the green
      arrow and the record showed up. ​


  • 11.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago
    Excellent well-presented responses from Andy ! ! !

    In my environment powershell.exe is in the path, so the example below works.

    NT_JOB PWRSHELL  
      AGENT <Agent Name>                                            
      CMDNAME powershell.exe                                             
    /*CMDNAME C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe */

    The advantage I see is that in the future if PowerShell is moved the ESP Job will still work.

    The other issue I had in my environment is that the script did not exit with the proper exit code.

    I added the function below, then called it with the appropriate exit code value.

    #---------------------------------------------------------------------#
    #   Functions ExitWithCode                                            #
    #---------------------------------------------------------------------#
    function ExitWithCode
    {     param
        (         $exitcode     )
        $host.SetShouldExit($exitcode)
        exit }
    .
    .
    .
    # Last line of Main Program

    ExitWithCode -exitcode $ExitStatus​


     



    ------------------------------
    Senior Systems Analyst
    UPS
    ------------------------------



  • 12.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago
    Rick,

    This has worked for us for years as well. However, we now have users that need to run 64 bit Power shell which runs from this path:
    C:\WINDOWS\SYSWOW64\WINDOWSPOWERSHELL\V1.0\POWERSHELL.EXE
    Is there a way in Windows PATH or environment variables to make "powershell.exe" resolve to this?

    Thanks!
    <JC>​


  • 13.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago

    If the PATH were updated to reference the 64-bit PowerShell.

    Check out the link below, that may provide what you need without any changes to Windows PATH.

    http://cosmonautdreams.com/2013/09/03/Getting-Powershell-to-run-in-64-bit.html



    ------------------------------
    Senior Systems Analyst
    UPS
    ------------------------------



  • 14.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago

    For the elimination of the path in the CMDNAME, make sure the following is set in agentparm.txt

    #
    # Windows Settings
    #
    oscomponent.lookupcommand=true

    Otherwise job will be on CSF as follows:

    Job Name   ApplName     CCode Job Status
    PWRSHELL   PWRSHELL     20007 Command file not found



    ------------------------------
    Senior Systems Analyst
    UPS
    ------------------------------



  • 15.  RE: plumbing windows jobs to powershell files

    Posted 6 days ago
    I've considered using the environment path but we run powershell on so many servers and our change control is pretty stringent.  Too much effort.

    The need to use $host.SetShouldExit() is not unique to your environment.  We also use it to ensure the correct exit code is passed back to DE.  Fortunately Powershell has pretty good error handling and the built in $error variable which allows for easy control of what codes to pass back
    ​​

    ------------------------------
    Andy Reimer
    ------------------------------



  • 16.  RE: plumbing windows jobs to powershell files

    Posted 5 days ago
    Edited by stan teitelbaum 5 days ago
    we seem to be getting there.  In our work session we found that invoke-sqlcmd needed to be installed before we could even exec the script on the server where the agent runs.  And while our ca contact/peer felt that the permissions we gave the non expiring userid there was more than necessary, we did get the script to run under that userid's creds.

    we think also that because we have spaces in the path to the ps1 file that this pattern (notice escape before double quote)  needs to be in the ​arguments to pass:

    -command \ "E:\x\y y\z\a\b.ps1\"

    I don't know yet if the last slash was a typo on my part or placed there by the person who knew that to get past one error on the ca client we needed the escape sequence pattern.  I'm looking into that but I did remove it temporarily, ran and still no luck..

    I'm asking my peer to give the userid exec on folder x.  That way I can try to run with this argument and see what happens -command "E:\x\b.ps1" .

    Any search engine based url I try to use on this subject that starts with docops.ca.com... seems to be returning an error so my attempts thus far to find an answer online are failing.  I may try this from home this eve.

    In anticipation of a couple more hurdles I put logging into the script as shown below.  The idea is that we at least need to know if the ps1 script is starting up and then if it is what errors are being generated.  Right now, triggering the event is running on the client but doing nothing over on the agent server.  Every now and then I look over at the sql server to see if its even aware that something is trying to run something else over there.  The catch block hasn't happened so I don't know if that clause will work when we need it to.

    Out-File -FilePath .\Script.txt -InputObject "before" -Append
    try
    {
    $SQLServer = "someserver"
    $Database = "MSDB"
    $ExecAgent = "exec dbo.sp_start_job N'test ca series d to ssis'"
    Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query $ExecAgent
    }
    catch
    {
    Out-File -FilePath .\Script.txt -InputObject $_.Message
    }
    Out-File -FilePath .\Script.txt -InputObject "after" -Append





  • 17.  RE: plumbing windows jobs to powershell files

    Posted 5 days ago
    we got this to work by putting an underscore in the folder name that has a blank.  And going with Andy's original format.

    I didn't realize that if you don't upload a change in the ca client, triggering a test will fool a newbie like me.  Still anxious to hear how blanks are dealt with in passed file name arguments but we are on a roll.​  thank you!