Extracting information from CE using XML/CSP 2

[gortuk]

Hi all,

I found a web page at

http://www.dmgrpt.com/JulyNL.htm

(near bottom of the page) with a very interesting approach towards reporting on the 'hidden' information in the APS Database. It's a lot easier and more automated than pulling information via Excel macros on an exported HTML page, but there are a few improvements needed. I'm not taking credit for any of this, just bringing it to the attention of the group to see if others can help perfect it...

Basically, it involves creating a CSP page which generates an XML datasource containing the results of a CI_INFOOBJECTS query. I had to modify the CSP code given on the above link a little bit in order to get it to output valid XML, here's what I used:

<%
Dim SessionManager
Set SessionManager=Server.CreateObject("CrystalEnterprise.SessionMgr")
Dim Sess
Set Sess = SessionManager.Logon("administrator", "PASSWORD_GOES_HERE","SERVERNAME_GOES_HERE", "secEnterprise")
Dim IStore
Set IStore = Sess.Service ("", "InfoStore")

'Next we create a query
Dim Rs: Set Rs = IStore.Query("SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_PROGID, SI_CREATION_TIME," &_
"SI_STARTTIME, SI_ENDTIME, SI_LAST_RUN_TIME, SI_NEXTRUNTIME, SI_PARENTID, SI_PROCESSINFO.SI_DBNEEDLOGON," &_
"SI_SCHEDULE_STATUS, SI_RECURRING, SI_OWNER, SI_PATH, SI_SCHEDULEINFO.SI_DESTINATION.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_MESSAGE," &_
"SI_ERROR_MESSAGE FROM CI_INFOOBJECTS WHERE " &_
"SI_INSTANCE = 1")

'Next we will write a simple function that will build each data row within xml tags
'with the tag names matching select properties
Function PrintOutRow(row)
For each col in row
PrintOutRow = PrintOutRow &_
" <"&Server.HTMLEncode(col.Name) & ">" &_
Server.HTMLEncode(col.Value)& "</" & Server.HTMLEncode(col.Name) & ">" & vbCRLF
Next
End Function

'Now before we loop thru all rows of our result set we need to enclose a
'beginning tag
Response.Write "<?xml version='1.0' ?>" & vbCRLF
Response.Write "<DATA>"& vbCRLF

'and then loop thru the result set of Rs
For Each rsRow in Rs
Response.Write "<ITEM>" & vbCRLF
Response.Write PrintOutRow(rsRow.Properties)

'now we can set a reference to the processing info object and
'get its properties
Dim pinfo: Set pinfo = rsRow.ProcessingInfo
Response.Write PrintOutRow(pinfo.Properties)

'and then the scheduling info
Dim sinfo: Set sinfo = rsRow.SchedulingInfo
Response.Write PrintOutRow(sinfo.Properties)
Response.Write "</ITEM> " & vbCRLF
Next

'and lastly end it with a closing tab
Response.Write "</DATA>"& vbCRLF
%>

Once the CSP page is on the server, you can create an ODBC connection to the CSP page using the CR XML driver (Location type is "XML Document") and base reports on that XML data source.

The one thing that this doesn't seem to be able to show is some of the 'nested' items in Query Builder. For example, it would be really useful to be able to get some of the items in SI_PROCESSINFO, to be able to produce a report showing full details for all scheduled reports. But including items from SI_PROCESSINFO in the .csp causes errors, probably because there's more than one column.

I'm a complete novice at CSP and XML, does anyone have the 'skillz' to take this to the next level and get this to display ALL data in the CI_INFOOBJECTS table?

 

[gortuk]

Sorry, forgot to mention that this is on Crystal Enterprise 9.0, although the original article suggests that it would also work on CE 8.5.

 

[crystalvictim]

Sounds quite interesting. I tried to create a sample page based on your posting and ran into the following problems:

- Using secWinAD instead of secEnterprise doesn't work (running the report results in: 'ODBC error: [MERANT][ODBC XML driver][XML][Closed Integrator Base]Insufficient connection information available to connect to any underlying providers').

- Specifying username and password also doesn't work and results either in the above mentioned error message or a GPF.

So up to now I've only been able to access CE's information with secEnterprise without username/password (i.e. logging on as 'guest'). Unfortunately, 'guest' doesn't have that much authorizations and can see only a very limited range of reports.

Has anybody made similar expierences and found a solution for the problem?

Regards.

 

[gortuk]

Here's my second attempt. This time I started with the query.csp page which allows the output to be all fields, not just non-nested ones. The resulting XML is pretty ugly, but seems to work. The authentication may work better on this as well. Again, I'm not an expert so I'm sure someone with better CSP/XML-fu could fix this up nicely.

(Note: I also had to modify helper_js.csp to remove the header information from that page, otherwise the XML wasn't valid).

<%@ LANGUAGE="JavaScript" codepage=65001%>
<%
Response.Write("<?xml version='1.0' ?>")
Response.Write("<DATA>")
%>

<!-- #include file="helper_js.csp" -->
<%
try
{
var iStore;
var usr = "administrator";
var pwd = "PASSWORD";
var aut = "secEnterprise";

if (Request.Form.Item("usr").Count == 1)
usr = String(Request.Form.Item("usr"));

if (Request.Form.Item("pwd").Count == 1)
pwd = String(Request.Form.Item("pwd"));

if (Request.Form.Item("aut").Count == 1)
aut = String(Request.Form.Item("aut"));

//CHECK IF THERE IS ALREADY AN EXISTING INFOSTORE OBJECT
if (GetSession("IStore") &&
(typeof(GetSession("IStore")) == "object") &&
(usr == GetCookie("query_usr")) &&
(aut == GetCookie("query_aut")) &&
pwd == "")
{
iStore = GetSession("IStore");
}

else {
var aps;
var usr;
var pwd;
var aut;

//GET APS NAME FROM SERVER
aps = "APSNAME";

//SET THE SESSION MANAGER OBJECT
var sm;
sm = Server.CreateObject("CrystalEnterprise.SessionMgr");

//LOGON WITH USER CREDENTIALS
var en;
en = sm.Logon( usr, pwd, aps, aut );

//SET THE INFOSTORE OBJECT
iStore = en.Service("", "InfoStore");

//STORE THE INFOSTORE OBJECT IN OUR SESSION VARIABLE
SetSession("IStore", iStore );

SetCookie("query_usr", usr);
SetCookiePath("query_usr", "/");
SetCookie("query_aut", aut);
SetCookiePath("query_aut", "/");

}
//GET THE SQL STATEMENT AND EXECUTE IT. RS HOLDS THE COLLECTION
var rs;
rs = iStore.Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'")
%>
<%

function PrintOutProps(props)
{
var i;
for (i=1;i<=props.Count;i++) {
prop = props.Item(i);
if (prop.Container) {
PrintOutProps(prop.Properties);
}
else {
Response.Write(("<_"+(prop.Name)+">"));
Response.Write(Server.HTMLEncode(prop.Value));
Response.Write(("</_"+(prop.Name)+">"));
}

}
}

function PrintOutBag(headertext, props)
{
if (props.Count == 0)
return;

PrintOutProps(props);
}


var i;

//LOOP THROUGH THE COLLECTION AND DISPLAY THE OBJECT DETAILS
for (i=1;i<=rs.Count;i++) {
Response.Write(("<ITEM>"));
var prop;

//LOOP THROUGH THE OBJECTS AVAILABLE PROPERTIES AND DISPLAY THEIR NAME / VALUES
PrintOutBag("", rs.Item(i).Properties);

var pinfo = rs.Item(i).ProcessingInfo;
if (pinfo)
{
Response.Write(("<PROCESSINGINFO>"));
PrintOutBag("", pinfo.Properties);
Response.Write(("</PROCESSINGINFO>"));
}

var sinfo = rs.Item(i).SchedulingInfo;

if (sinfo)
{
Response.Write(("<SCHEDULINGINFO>"));
PrintOutBag("", sinfo.Properties);
Response.Write(("</SCHEDULINGINFO>"));
}

Response.Write(("</ITEM>"));
}
}
catch(e)
{
Response.Write(e.description);
}
Response.Write(("</DATA>"));
%>

 

[mdwyer]

This is great! Thanks gortuk (and Tom Fallwell from your original source).

I got your first example working and can see a lot of potential. I may be able to try your second as well. I'm nearly clueless about working with XML so I probably can't get ahead of you there.

I only work with Enterprise authentication so I can't help crystalvictim either, except to wonder if you can't create a user with enterprise authentication (won't Administrator work?) who can be given more privileges than Guest. (Also, is secWinAD legal in CE10? I see only secEnterprise, secWindowsNT, and secLDAP in CE9.)

 

[mdwyer]

I've tried the "second attempt" with questionable results. In the CR designer data sources, the tables have names like _1 and _SI_NAME. With the first attempt, there was one table - DATA.

Why the underscore in front of the property names:
Response.Write(("<_"+(prop.Name)+">"));
It seems to be necessary to the XML but I can't tell why. It also seems related to the multiplicity of tables in the ODBC data source.

(FYI, I changed the line that sets the APS name:
aps = Request.ServerVariables("WCS_NAME_SERVER"); // was = "APSNAME"
Just a tiny tweak.)

I also forgot to ask crystalvictim about the XML driver used in the ODBC config. Your error mentions Merant. Are you using a Merant XML driver instead of CR ODBC XML Driver 4.10? Would that make a difference?

 

[gortuk]

The reason for the difference in the second attempt is that the first attempt outputs ONLY the top-level (unnested) pieces of data. Eg since SI_DEST_SCHEDULEOPTIONS is nested within SI_DESTINATION, the first attempt does not output SI_DEST_SCHEDULEOPTIONS or anything within it like SI_SERVER, SI_USERNAME, SI_MAIL_SUBJECT, etc.

In the second attempt, it outputs everything but as a flat set of data... hence the reason for the underscores, because <1> is not a valid XML tag. So I appended "_" to make all the table names come out valid.

In the second attempt there are multiple tables in addition to Data because every time a field name is repeated (eg if a report uses multiple data sources), Crystal shows that tag as a separate 'table' with an ID that links back to the Data table.

Ideally, it would be great to actually preserve the nested tables in the output, so that you'd have something like <SI_DESTINATION<SI_DEST_SCHEDULEOPTIONS>
<SI_MAIL_SUBJECT>
<tag1></tag1>
<tag2></tag2>
</SI_MAIL_SUBJECT>
</SI_DEST_SCHEDULEOPTIONS></SI_DESTINATION>
But I can't figure out how to 'hold on' to the container names in order to display closing tags at the end... as I've said before, I'm very new to XML/CSP (I basically started on Friday when I came across Tom's page) and hoping someone else here can build on this...

 

[jrennatc]

I got around the _ by holding onto the property name when I found a container and using that property name when the column name was a numeric. I stayed in VBscript like your original post...
'Recursive function to show all the properties and sub-properties
Function PrintOutProps(props)
For each varProp in props
If (varProp.Container) Then
holdprop = varProp.Name
PrintOutProps(varProp.Properties)
Else
'If the column name is an iteration, get the container name
If isnumeric(varProp.Name) Then
colname = holdprop
Else
colname = varProp.Name
End If

Response.Write "<"&Server.HTMLEncode(colname) & ">" &_
Server.HTMLEncode(varProp.Value)& "</" & Server.HTMLEncode(colname) & ">" & vbCRLF

End If
Next
End Function

Anyone have any luck reporting off this data? I chose to only pull scheduled instances and the properties will differ for different settings (i.e. dest SMTP vs FTP). I'm concerned that I will implement the report with the properties that are on the database now and next week, someone will schedule a report in a way that I hadn't seen before and I won't have those elements captured in the report. Or worse yet, a distinct report could be ended and properties that I have in my report go away (which I think will cause major problems with Crystal). Suggestions?

 

[pelajhia]

This is very cool gortuk; I am still having issues getting the report to 'see' this as fields, though. I am sure I am doing something wrong with the driver, so I will refer to crystal's doc on xml datasources.
I set it up and hit the 'test connection' button and it responds that the test is successful, but when pulled into the report, I see only an 'add command' option. Do I need to add some table or field hints? I don't have a clue what they'd be or what to reference in the sql if I did use a command.

Tried this with drivers: cr xml 3.6, 4.1 and 4.2. Using ce10/cr10.
Using essentially exactly the code you have suggested in your second example.
Thanks for all the info so far!

 

[gortuk]

I don't think I used any hints or any special settings - just the standard Crystal XML driver. Haven't tried this in CR 10 though, so maybe something changed. One thing to check first - have you verified that your output file is valid XML?

 

[jrennatc]

I'm pretty sure when I tried this, the output had a - in front of all the HTML tags like -<SI_NAME>. I believe I had to remove these before I could use it in a report.

 

[pelajhia]

hm; I will look into both of those ideas; I have to request permission to download the msdn xml validator noted in the crystal kb article on using xml; (cr_xml_data_sources.pdf). But as soon as I get it I will give it a try. Just looking at the output, it looks a bit goofy, not all the tags close where I would "expect" them to. Having zero xml experience, my expectations might be a bit off, however

Here is my really DUMB question after re-perusing this kb article:
It looks like I'm supposed to have a .xml file laying around in a folder somewhere according to the driver config.
All I did was use the suggested code in a .csp file. I can pull it up via IE and it seems 'nice', however I don't know if I am supposed to be using this URL as the location in the driver or not. Am I missing a step? Thanks!

 

[gortuk]

pelajhia- I found a web-based XML validator for this purpose, I think on Microsoft's site - try googling it.

And you are not missing a step - you do just use the URL as your data source (as long as it outputs valid XML).

 

[basam350z]

Where is the object located for using "CrystalEnterprise.SessionMgr"? I trying to code something in VB and I can't find what component I need to add to access this "CrystalEnterprise.SessionMgr".

Basam
basam@kohls.com

 

[pelajhia]

I kind of dropped this for a while, now I am in a different environment and was able to get this working nicely pulling records from ci_sysobjects.
The only problem I see is that this only returns 1000 records at a time. I have noticed this with the query builder that comes with ce 10, so I am going to look into it from that angle. I was wondering if anyone else noticed this and if so, what the solution might be.

THanks!

 

[gortuk]

Pelajhia - is it something to do with the query size threshhold (CMC > Crystal Applications > CMC)? I didn't notice any restrictions in CE 9 and this threshold is new in CE 10 so maybe it's related...

 

[pelajhia]

Thanks for the suggestion but this is set to 500 on my server, so I don't believe this is the variable I am looking for.
Since I don't see much in the csp code, I am wondering if this is set in the registry.

 

[jrennatc]

If you want to return more than 1000, you have to specify in the query...
SELECT TOP 6000 SI_NAME, SI_ID ...

It's a Crystal limitation I believe.

 

[pelajhia]

Thanks, that's great! I found the registry value, too, but I'd rather change the query itself...

hkey local machine> software>crystal decisions>10.0>cms>instances>cmsname.cms > InfoStoreDefaultTopNValue