Reporting of Enterprise Data 3

[MKVAB]

Hello all.

To keep track of what is running on a schedule and where the reports are going, I would like to be able to query the CE SQL Server database where that info is stored.

My problem is not in accessing the SQL Server db that I believe CE is using, but in finding that information. I see no table that contains scheduling information, none the less distribution information.

Does anybody know of a way to report on scheduled reports and their distribution lists?
How do any of you keep track of what's running and when on your CE box?

Any ideas or suggestions are GREATLY appreciated!

-MK

 

[lyanch]

You have to buy (or build) an add on product... yeah I know.. you would think that a major reporting tool would be able to report on itself.. but not true.

Lisa

 

[MKVAB]

No!!!! From an administrative point of view, how could they overlook providing that functionality?

Any applications you can refer me to?

Do you know, though, where the data is stored on the server? Is it in an actual database? If I could just find where it's stored that would be major progress.

Thanks so much!
-MK

 

[lyanch]

I believe the tools parse it out of log files.. to the best of my knowledge it isn't held withing the DB. This is a tool that Crystal refers to.. but I know there are several more available:

http://support.businessobjects.com/library/kbase/articles/c2012056.asp

Lisa

 

[MKVAB]

Thanks lynach. I must say I am quite disappointed with Crystal...or should I say "Business Objects"...on this one. I'll look into the tools.

-MK

 

[MJRBIM]

Apparently, CE 10 has a lot of this "audit" functionality built in.

 

[DanENW]

No the tools don't parse it from any log files. The log files don't contain this information.

The APOS tool, KPI, actually reads the information from the CE backend database and stores it in an Access (or Oracle, or SQL Server) database to be reported on. Some of the information is logged into the KPI db directly from plugins provided by APOS.

You can query this backend db yourself using the CSP Query Builder tool that exists on the Lauchpad in the window titled "Client Samples".

 

[gashandler]

You'd think CE would already have this functionality by now. It's a lot of fun administering CE "in the dark" isn't it? It's one of the reasons why I don't let users schedule and publish their own reports. Anway, like someone already said, CE10 is supposed to have a way to audit itself. There is a new service called Crystal Management Server that is supposed to give you auditing capabilities. About freaking time.

 

[MKVAB]

I too am eager, and somewhat anxious, to see the extent to which they provide administrators with auditing capabilities in 10.

DanENW, do you know where the physical database is located that is accessed by the CSP Query Builder (if you're using SQL Server)?

 

[mdwyer]

Even if you can read the database, the data is stored as a binary object. You will need the csp toolkit (SDK) to make any sense of what is stored.

 

[MKVAB]

Oh. Thanks Mdwyer.

-MKvab

 

[lyanch]

Depending on the level of logging you have turned on you can get basic info from the logs. I had some scripts one of the original users (BMT) wrote to grep usage from the log files. It was incredibly cumbersome and hard to maintain.. along with having to move all my logs to Linux to run.

Lisa

 

[DanENW]

MKVAB,

The system database can be found by tracing the information backwards from the CCM.

Open the CCM; right-click the Crystal APS and select Properties; select the Configuration tab; in the bottom half of the panel look for the APS Data Source block. This block contains information about the system database. In our case it contains the name of the ODBC system DSN that the APS uses to access the system database.

By default the system database is contained in a MSDE (stripped down SQL Server) database. )Review of the CE documentation will tell you which databases need ODBC and which are accessed natively.)

Tracing the database backwards through the ODBC DSN reveals, in our case, that the system database is located on a SQL Server system, and what the database name is.

Now the bad news. As pointed out by MDWYER, you can't make heads or tails of the database. I don't believe that it is coded as binary information as much as it is highly normalized, everything is an "info object" of some sort, and the different fields in the database have a context sensitive meaning.

Crystal Decisions, formerly Seagate Software, has always stated that we shouldn't mess with the database. In SI 7.0 they put much of the information into the database in both user readable form and also coded into a binary data block. They sometimes used the binary block and sometimes used the user readable values; we were never able to determine which. But, if we tampered with the data we would risk corrupting the database because we were unable to correctly set the binary block. Now with CE 9.0 they have developed a method of storing the system data which is truly inscrutable if you don't go thru the APS with a query for the data.

 

[rhinok]

BlurredVision has a method for gathering data. Check out thread thread782-732083.

~Kurt

 

[MKVAB]

Thanks DanENW!! I see what you and mdwyer were talking about now. Yeah, the data means very little without the metadata. Blast!

Thanks for pointing out the other posting, rhinok. I tried what was suggested by BlurredVision but the data extracted from the query did not include the distributions for the scheduled reports (which is what I really need the most).

Lyanch, thank you for your suggestion (and warning).