Specific SQL query

[swalter]

Hi Everyone,

New user here.

I was wondering if anyone would be nice enough to share a SQL query where I can pull specific fields from the current schedule (to start, I would like to get job names, job ID's and instance).  We are using CWA 6.3.0.183 and utilizing a MSSQL DB.  I understand I can use the Web Console to pull this information and export it into a text file.  The reason I am looking to use a query is because we would like to automate the pulling of the data.  We're eventually going to import some of the data into ServiceNow so we can use the data in forms and to allow the customer to perform some scheduling actions.

Thank you in advance for your help.

All the best,

Scott

[jeff.hamway01]

Hello Scott,

I have not upgraded to 6.3 yet.  I imagine that the tables have been modified, updated, or may be totally different since 6.0x.  However,  I have attached some queries that I have created/collected over some time using Tidal 5.3 - 6.0x.  Feel free to use them if applicable.  Most are just selects so you can modify to your needs. 

If anyone else has a bank of scripts they use.  post them,  I'm sure it will help someone.

Note: some of them don't work anymore but I use them to help me formulate other scripts.  

[swalter]

Hi Jeff,

Thank you very much for taking the time to reply and for sharing your "cheat sheet".  I also managed to find this today, maybe it will help you and others in the future.

All the best,

Scott

[Derrick Au]

Thank you Jeff, and welcome Scott to the Cisco Support Community

I noticed your email domain - Congratulations Scott :-)

The data you seek can be found in the jobrun table, and joining it along with the jobmst table to get the names. You can try a simple SQL query and apply additional data columns or filters as needed:

SELECT jobmst.jobmst_prntname, jobmst.jobmst_name, jobmst.jobmst_id, jobrun.jobrun_instance FROM jobrun

JOIN jobmst on jobrun.jobmst_id = jobmst.jobmst_id

WHERE

jobmst.jobmst_name like '%ENTER_JOBNAME%'

and jobrun.jobrun_proddt >= 'ENTER DATE AS m/d/yyyy'

Regards,

Derrick