Database Values

[Stephen.whittle]

When writing reports using the where SQL statements it is useful to know the database values to contribute to your filters. When using Supportworks I used to browse the Data Dictionary for assistance with this but haven't found a way of doing this in Service Manager. I am by no means a SQL expert and I am sure there is an easy way to achieve this I just haven't found it. Can anyone help? 

[samwoo]

@Stephen.whittle,

If you go into the Admin Tool - > Service Manager - > click Entity Browser (or viewer, can't remember off the top of my head the exact name) you should be able to see a list of the tables and columns associated with them with lots of other related info. 

It's really good, very detailed and has helped me a lot. 

Thanks, 

Samuel

[James Ainsworth]

Hi Stephen,

When you say 'Values' are you referring to database column names or the content within the columns?

There are two tools available depending on what you are looking for.

Under each application there is the Application Entity View.

 

This also includes a Database Schema View

I believe that this will be more or less what you may have see in the Data Dictionaries in Supportworks.

There is also our Database Direct tool which is available under the System->Data tile.

Regards,

James

[Stephen.whittle]

Thanks both, yes i have used the entity viewer but again it shows me the table and column names which is great and a good start. But yes, I was referring to the values within the columns which I gathered would need to be achieved via the direct database. 

[Steve Giller]

If you run the report before applying filters you should get all the results, so I'm not sure why you'd need to use direct database access.

[Stephen.whittle]

I would prefer not to use direct database, when build reports in Supportworks you could add a column value as a filter in a report while in the report builder screen, this was very useful and an efficient use of time. It just seems backwards having to run the report, view the values and sift through which values you would like to use in a filter only to go back and modify the report so get it to where you want to be. 

More an annoyance than anything. That's why I wanted to check I wasn't missing a trick. 

[Bob Dickinson]

Hi @Stephen.whittle

We also have created a page on the wiki that gives you a reference to some of the most common reporting attributes in the Main Request Table (h_itsm_requests):

https://wiki.hornbill.com/index.php/Table_Info:_Main_Request_Table

We do not have the equivilent for all tables, but this should give you an idea of the types of values that are held in the columns, and especially the options available in those columns that are set by the system e.g. h_requesttype and h_status.

I hope this comes in handy,

Kind Regards

Bob

[Stephen.whittle]

Thanks I have had a look and this is good for some of the more common columns. Thanks.