Video Screencast Help

Building custom reports on SQL

Created: 26 Jul 2011 • Updated: 29 Jul 2011 | 6 comments
Goltrek's picture
This issue has been solved. See solution.


We want to build custom reports for Helpdesk Solution 6.5 on SQL. So, we need to build queries to extract the relevant info that we want to display on reports. Our Database Administrator ask me for documentation about the Database structure for Helpdesk Solution 6.5. She want to have the Entity-Relationships Model, description for all the relevant fileds in tables, and all information that she can use to driver the query building process.



Discussion Filed Under:

Comments 6 CommentsJump to latest comment

mclemson's picture

No schema has been published for Helpdesk 6, although one exists for CMS 6 and AMS 6.  You or your DBA will have to map out such information yourselves.  It's a relatively straightforward DB, but then again, I'm not sure what reports you're aiming to create.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner

Goltrek's picture

Thank you mclemson. (Just to know, where can I find the info for CMS 6 and AMS 6 databases?)

Thanks a lot..!

mclemson's picture

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner

Goltrek's picture

Thank you again mclemson...!

Rob Moore's picture

All you need to look at is the following views in the NS DB. These views are created by looking at the actual tables in the Incidents DB, and they are created in real time. Reporting from them puts less load on the Incidents DB - which is good since that DB needs to service customers!

HD_Worker_View - complete details of each worker
HD_workitem_current_view - current ticket version details  - one row per ticket number
HD_workitem_detail_view  - all ticket versions and details - multiple rows per ever ticket number
HD_Contact_View - All contact details related to all contact.

In truth, you are most likely to use the HD_Workitem_current_View, this is there the latest ticket details are maintained.

Never had a data dictionary, tho I begged Altiris/Symantec for one for years! The good news is the table schemas are real simple, and all the views are already created for you so you are not likely to be using a lot of custom joins as they are not really necessary.

send me a note if you like, I can show you some of the reports we have created. we have a few hundred reports for all the different groups using our helpdesk.


Goltrek's picture

Rob, I appreciate this info. I will check those views to find relevant information to build reports.