Anonymous_User Absent Member.
Absent Member.
797 views

Job details through SQL


Hello,

I am looking for a way to get specific job details for a set of servers
by using SQL. The job info I am most interested in having returned would
be information regarding the Schedule, Values, and Actions for the job.
Any info or a point in the right direction would be appriciated. Thank
you.

-Jake


--
jportenier
------------------------------------------------------------------------
jportenier's Profile: https://forums.netiq.com/member.php?userid=7504
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
6 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


Obviously it is possible to get this information in SQL, but whether you
actually want to or not depends on what you are trying to do and your
comfort level in SQL. There is an AM Report job (JobInfo) which provides
this information in a report which would be the "preferred" way to get
hold of it. Also, Alain has a VB Script which can gather the information
(he can post a link to that). Both of these use NetIQOLE to gather the
information.

If you do want to use SQL to get the information then the tables
involved are...

For Schedules and Parameters (values): Job, Props and TextPt
For Actions: Job and ActionTmpl

So for example, you would need to join the Job table to the Props table,
then the Props table to the TextPt table to get the schedule or
parameter information. The 2 queries below do that to show the joins
involved as an example for getting this information for a Job with JobID
13:-

SELECT ValuesTab = T.TextField
FROM Job J WITH (NOLOCK)
INNER JOIN Props P WITH (NOLOCK) ON P.PropID = J.PropID
INNER JOIN TextPt T WITH (NOLOCK) ON T.TextPointerID = P.ParameterXMLPt
WHERE J.JobID = 13

SELECT Schedule = T.TextField
FROM Job J WITH (NOLOCK)
INNER JOIN Props P WITH (NOLOCK) ON P.PropID = J.PropID
INNER JOIN TextPt T WITH (NOLOCK) ON T.TextPointerID = P.ScheduleXMLPt
WHERE J.JobID = 13


But the data returned is bascially XML. To see what you are looking for
you then have to process the XML to extract the embedded data. That
requires a bit more code... I have examples of doing that, but the SQL
then starts to become quite complicated. If you are unfamilar with SQL
and XML processing within SQL it might be better to see if the report
job or Alain's VB Script can provide what you are looking for


--
Andy Doran
Software Engineer Consultant (NetIQ)
------------------------------------------------------------------------
andy_doran's Profile: https://forums.netiq.com/member.php?userid=3937
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


You can download the VBScript that Andy referred to from here:
http://tinyurl.com/pwmxepk

My script essentially converts all the XML into regular database fields
so that it can more easily be queried. The link includes the code, help
files and sample queries but let me know if you have any questions or
issues.


--
Alain Salesse | Senior Technology Consultant | Alain.Salesse@NetIQ.com
------------------------------------------------------------------------
SalesseA's Profile: https://forums.netiq.com/member.php?userid=3958
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


SalesseA;248414 Wrote:
> You can download the VBScript that Andy referred to from here:
> http://tinyurl.com/pwmxepk
>
> My script essentially converts all the XML into regular database fields
> so that it can more easily be queried. The link includes the code, help
> files and sample queries but let me know if you have any questions or
> issues.



After running this script using cscript.exe and including the
appropriate userid and pwd for my SA account on SQL, I get an error
stating :


********************************************************************
Script complete. 0 Jobs were parsed. The following errors were
encountered:
Failed to execute query CREATE TABLE permission denied in database
'QDBDalton'.
CREATE TABLE _JobInfo ( UID Varchar(100),
Instance
Varchar(50), Repository Varchar(50),
Server_Name Varc
har(30), Script_Name Varchar(60),
ParentJobID Int,
JobID Int, Status VarChar(25),
Deployme
nt VarChar(7), KSG_Name Varchar(50), Objects
Varchar(1024),
Version Varchar(30), Submit_Time Varchar(25),
Modification_Time INT, Report_Date Varchar(25) )
Initialisation failed
Script execution time: 0 minutes.
********************************************************************

Using my SA account, I should not encounter a permission error.. any
thoughts???


--
jportenier
------------------------------------------------------------------------
jportenier's Profile: https://forums.netiq.com/member.php?userid=7504
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


You do not have permission in the database QDBDalton to create tables.
If you look at the html help, you will see that you can specify a
"destination" database which is where the parsed job information will be
written to. If you do not specify that, then the destination is the same
as the target. As you do not have permissions to create the tables, you
either need to use an account that does have those permissions, or to
specify a database as the target where you do have those permissions.

The help file explains, but I am sure Alain can give more pointers if
required.


--
Andy Doran
Software Engineer Consultant (NetIQ)
------------------------------------------------------------------------
andy_doran's Profile: https://forums.netiq.com/member.php?userid=3937
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


andy_doran;252850 Wrote:
> You do not have permission in the database QDBDalton to create tables.
> If you look at the html help, you will see that you can specify a
> "destination" database which is where the parsed job information will be
> written to. If you do not specify that, then the destination is the same
> as the target. As you do not have permissions to create the tables, you
> either need to use an account that does have those permissions, or to
> specify a database as the target where you do have those permissions.
>
> The help file explains, but I am sure Alain can give more pointers if
> required.


Hi Andy,


I am using the system admin account to run this. I can verify that the
account has the ability to create tables on that database.


--
jportenier
------------------------------------------------------------------------
jportenier's Profile: https://forums.netiq.com/member.php?userid=7504
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Job details through SQL


The error you quoted was "permission denied". I can;t tell from that
what account was used to log in to the QDB, but either you logged in
using the Windows account you were using when you invoked the script, or
you overrode that by specifying a SQL account. Either way - that account
was denied permission to create the tables. That is not an error
generated by the script, it is an error generated by SQL that the script
is reporting.

I don;t know your specific configuration, but by default - SQL does NOT
grant a Windows Admin account (ie one in the Administrators group)
rights in SQL


--
Andy Doran
Software Engineer Consultant (NetIQ)
------------------------------------------------------------------------
andy_doran's Profile: https://forums.netiq.com/member.php?userid=3937
View this thread: https://forums.netiq.com/showthread.php?t=51679

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.