Query Builder Question

[Edie0802]

I want to be able to query a CE9 system asking it to provide me a listing of users that have run an instance in the last 3 months against the Job Server (History tab).

I have looked at CI_SYSTEMOBJECTS and I have looked at CI_INFOOBJECTS, but I don't see a key between them in order to write an intelligent WHERE clause. Any help is really appreciated.

I called tech support yesterday and got told they'd research it and get back to me. Haven't heard back from them yet....

 

[pelajhia]

Can't remember if ce9 allows auditing of this data, you can go to servers > report job server and click 'audit' tab. If audit is available and there are scheduled options, you should be able to find this in the database. It might at least give you a clue as to whether it's possible.

I know it is possible in XI, as I just completed a report with this type of data.

 

[Edie0802]

Auditing is not available in CE9. I double checked. Anyone else have suggestions? Thx.

 

[Edie0802]

I would also be interested in how you did this type of report in XI. I am going live with XI within the week.

 

[pelajhia]

In order to set this up for xi, the basic steps:
1. in the ccm, set up the auditing database. You'll need a db user name with write privileges because you'll be creating tables when you do this. Stop the cms server (previously aps) go to properties and select configuration tab - audit db set up is fairly straight forward. Just enter connection info and credentials and 'voila'.

2. Select actions to audit within the CMC - under most servers there is an audit tab with variuos selections. The report job server will have selections pertinent to scheduled reports since this is what its primary function is.

3. The audit db in XI is fairly different in structure than that of 10, but there are only about a 1/2 dozen main tables so it's not too difficult to figure out.
The event_type and detail_type tables will be a good place to start to see how the items you select in step 2 end up being translated into the db.

Here is a query that I am using for the crystal report I am currently working on. It is substantially untested, but seems to work ok so far:

SELECT AUDIT_DETAIL.DETAIL_TYPE_ID,
AUDIT_EVENT.EVENT_TYPE_ID,
AUDIT_EVENT.DURATION,
AUDIT_EVENT.START_TIMESTAMP,
AUDIT_EVENT.USER_NAME,
AUDIT_DETAIL.DETAIL_TEXT
FROM CRYSTAL.AUDIT_EVENT AUDIT_EVENT
LEFT OUTER JOIN CRYSTAL.AUDIT_DETAIL AUDIT_DETAIL
ON (AUDIT_EVENT.EVENT_ID=AUDIT_DETAIL.EVENT_ID)
AND (AUDIT_EVENT.SERVER_CUID=AUDIT_DETAIL.SERVER_CUID)
WHERE AUDIT_DETAIL.DETAIL_TYPE_ID=3
AND AUDIT_EVENT.EVENT_TYPE_ID=327681 AND (AUDIT_EVENT.START_TIMESTAMP
>=TO_DATE ('22-02-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND AUDIT_EVENT.START_TIMESTAMP
<TO_DATE ('23-02-2006 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))

the AUDIT_DETAIL.DETAIL_TEXT is the name of the report in this case, though it can contain other data.
I have limited to successfully scheduled reports by
selecting where event_type_id = 327681 and detail_type_id = 3.

Anyway, like I said, at least so far this seems to be working for me, I am still developing. I am still looking for the parameter value fields at this point.

Good luck with your move to xi

 

[KingfisherINC]

Caveat emptor - Auditing schema changes again in XIR2.

Kingfisher

 

[DuncanSutcliffe]

If you really just want to use the Query Builder, you can do something as simple as:

select si_name, si_scheduleinfo.si_submitter, si_scheduleinfo.si_endtime from ci_infoobjects where si_endtime > '2006-01-01' and si_instance = 1

This will list every instance that happened since 1st Jan 2006, along with the person that submitted it and when it finished.

If you turn on auditing on your new XI system, check out the auditing sample reports which are installed on Enterprise under "Sample Reports" > "Audit Reports".

Duncan

 

[Edie0802]

Thank you, Duncan, your example gave me enough ammo to get what I needed!

 

[pelajhia]

DuncanSutcliffe -
are you familiar with gathering prompt values for instances?
Thanks!

 

[pelajhia]

KingfisherINC -
In release 2 is there any greater access to some of the more hard to get at data? eg: prompt values for historical instances.

Are the schema changes dramatic?? arg!

Thanks