Common SQL Tables and Joins

In this guide we will cover:

• Common SQL Tables and What Each Contains
• Example Joins Between Them

This guide will cover some commonly referenced SQL tables and the information that can be pulled from each. A majority of the tables listed here are also within the following schemas.

• ITSM Schema
• PSA Schema
• CRM Schema

Each table listed below is sorted by the general area you are likely to be reporting on when using them. The most used columns within each are also outlined.

Tickets#

These are the commonly used tables to report on tickets, and are often joined together.

Faults and Actions:

Faults#

Ticket details.

• Faultid: ID of the ticket.
  
• Username: Name of the end-user of the ticket.
  
• Status: ID of the status of the ticket.
  
• Dateoccured: Date the ticket was logged.

RequestType#

Ticket type.

• RTid: ID of the ticket type.
• RTdesc: Name of the ticket type.
• RTdefsection: Default team.
• RTworkflowid: ID of the workflow that is started upon logging.

FlowHeader#

Workflow details.

• FHid: ID of the workflow.
• FHname: Name of the workflow.

FlowDetail#

Workflow step details.

• FDfhid: ID of the workflow the step is on.
• FDname: Name of the workflow step.
• FDstart: Whether the workflow step is the start step.
• FDend: Whether the workflow step is the end step.
• FDstagenumber: Workflow stage the step is within.

TStatus#

Ticket status.

• Tstatus: ID of the status.
• Tstatusdesc: Name of the status.
• Tshortname: Icon text/short name of the status.
• TstatusputonSLAhold: Whether the status puts the ticket on hold.

Actions#

Action details.

• Faultid: ID of the ticket.
• Actionnumber: Which number the action was in the sequence of the ticket.
• Actoutcome: Name of the action outcome.
• Who: Who completed the action.
• Whe_: When the action was done.

Feedback#

Ticket feedback.

• FBid: ID of the feedback.
• FBusername: Who gave the feedback.
• FBemail: Email of the user.
• FBscore: Score level of the feedback.
• FBcomment: Comment given with the feedback.
• FBfaultid: ID of the ticket the feedback is for.

Service Level Agreements (SLAs)#

These are the commonly used tables to report on service level agreements (SLAs), and both are often joined together.

SLAHead and Policy:

SLAHead#

SLA details.

• SLid: ID of the SLA.
• SLdesc: Name of the SLA.

Policy#

SLA priority.

• Pslaid: ID of the SLA.
• Ppolicy: Priority level.
• Pdesc: Name of the priority.
• Presponsetime: Response target.
• Presponseunits: Unit of time for response target.
• Ptime: Resolution target.
• Punits: Unit of time for resolution target.

Budgets#

These are the commonly used tables to report on project budgets, and both are often joined together to show an overall budget overview for a project.

FaultBudget and BudgetType:

FaultBudget#

Budget on projects.

• FBTfaultid: ID of the ticket.
• FBThours: Hours of the budget line.
• FBTrate: Rate of the budget line.

BudgetType#

Budget type details.

• BTid: ID of the budget type.
• BTname: Name of the budget type.
• BTdefaultrate: Default rate of the budget type

Clients/Customers#

These are the commonly used tables to report on customer details, as well as the sites and users associated with them. These are often joined together to report on multiple users' details within a customer.

Site and AddressStore:

Users#

User details.

• Uusername: Name of the user.
• Usite: Site the user is under.
• Umobile: Home mobile number.
• Umobile2: Work mobile number.
• Utelhome: Home telephone number.
• Uextn: Work telephone number.
• Uemail: User's email address.

UserChange#

User change history.

• UCuid: ID of the user.
• UCusername: Name of the user.
• UColdvalue: Previous value before the change.
• UCnewvalue: New value after the change.
• UCwho: Which agent/what integration made the change.
• UCwhen: When the change was made.

Site#

Site details.

• Ssitenum: Site ID.
• Sdesc: Site name.

AddressStore#

Site address.

• ASline1: Address line 1
• ASline2: Address line 2
• ASline3: Address line 3
• ASline4: Address line 4
• ASline5: Postcode/ZIP code.

Area#

Customer details.

• Aarea: Customer ID.
• Aareadesc: Name of the customer.

Tree#

Top level details.

• Treeid: Top level ID.
• Treedesc: Name of the top level.

Agents#

This is the main table to report on agents. It is often joined with ticket based reports to show the agent's details alongside the ticket details.

Uname and Faults:

Uname#

Agent details.

• Unum: Agent ID.
• Uname: Name of agent.
• Usection: Default team.
• Usmtp: Email address.

Contracts/Agreements#

This is the main table to report on contracts or agreements, and are often joined when creating invoice or billing related reports.

ContractHeader and Area:

ContractHeader#

Agreement/contract details.

• CHid: Contract ID.
• CHarea: Customer ID.
• CHstartdate: Start date.
• CHenddate: End date.

Assets#

These are the commonly used tables to report on assets. The first is often joined with site details to show an overview of each site's assets.

Device and DeviceChange

Device#

Asset details.

• Did: Asset ID.
• Dinvno: Asset number.
• Dsite: Site the asset is against.
• Dtype: Asset type.

DeviceChange#

Asset change history.

• DCdevnum: Asset ID.
• DColdvalue: Value before the change.
• DCnewvalue: Value after the change.
• DCwho: What agent/integration made the change.
• DCwhen: When the change was made.

XType#

Asset type.

• Ttypenum: Asset type ID.
• Tdesc: Asset type name.

Items/Products#

These are the commonly used tables to report on items or products, and are often joined with quotation or revenue reports.

Item and ItemStockHistory

Item#

Product details.

• Iid: Item ID.
• Igeneric: Item group ID.
• Idesc: Item name.
• Ibaseprice: Sell price
• Icostprice: Cost.

ItemStock#

Stock quantity details.

• ISiid: Item ID.
• ISlocation: Site ID.
• ISdate: Stock date.

ItemStockHistory#

Stock change log.

• ISHiid: Item ID.
• ISHlocation: Site ID.
• ISdate: Stock date.

Generic#

Item/asset groups.

• Ggeneric: Group ID.
• Gdesc: Group name.
• Gshowasitem: Whether the group is an item group.
• Gshowasequip: Whether the group is an asset group.

Quotations#

These are the commonly used tables to report on quotes, and are often joined together to give an overview of current quotations.

QuotationHeader and QuotationDetail:

QuotationHeader#

Quotation details.

• QHid: Quote ID.
• QHfaultid: Ticket ID the quote was raised from.
• QHstatus: Status of quote.
• QHuserid: User of quote.

QuotationDetail#

Quotation line details.

• QDid: Quote line ID.
• QBdesc: Item description.
• QDquantity: Quantity.
• QBprice: Sell price.
• QDcostprice: Cost.

Sales Orders (SOs)#

These are the commonly used tables to report on sales orders, and are often joined together.

OrderHead and OrderLine:

OrderHead#

Sales order details.

• OHid: SO ID.
• OHtitle: SO title.
• OHusername: User's name.
• OHsitenum: SIte ID.
• OHinvoicedate: Invoice date.

OrderLine#

Sales order line details.

• OLid: Line ID.
• OLsellingprice: Price.
• OLcostprice: Cost.

Purchase Orders (POs)#

These are the commonly used tables to report on suppliers and purchase orders, and are often joined together to give a full overview of a purchase order.

SupplierOrderHeader and SupplierOrderHeader:

Company#

Suppliers.

• Cnum: Supplier ID.
• Cdesc: Supplier name.

SupplierOrderHeader#

PO details.

• SHid: PO ID.
• SHfaultid: Ticket ID the PO was raised from.
• SHporef: PO reference.
• SHusername: User's name.

SupplierOrderDetail#

PO line details.

• SDif: Line ID.
• SDshid: PO ID.
• SDdesc: Item description.
• SDquantity: Quantity.
• SDprice: Price.

Invoices#

These are the commonly used tables to report on invoices, and are often joined together.

InvoiceHeader and InvoiceDetail:

InvoiceHeader#

Invoice details.

• IHid: Invoice ID.
• IHaarea: Customer ID.
• IH3rdpartyinvoicenumber: Integration invoice number.
• IHdatepaid: Date of payment.

InvoiceDetail#

Invoice line details.

• IDid: Line ID.
• IDihid: Invoice number.
• ID_itemid: Item ID.
• IDitem_shortdescription: Item short description.
• IDitem_longdescription: Item long description.
• IDunit_price: Price
• IDnet_amount: Total excluding tax.
• IDtax_amount: Total including tax.

InvoicePayment#

Payment details.

• IPid: Payment ID.
• IPihid: Invoice ID.
• IPamount: Amount paid.

Licences/Subscriptions#

These are the commonly used tables to report on licences and subscriptions. They are often joined together to give a full overview of licence and subscription updates and counts.

Licence and LicenceChange:

Licence#

Software licenses and subscriptions.

• Lid: Licence ID.
• Ldesc: Licence name.
• Lcount: Licence count.
• Larea: Customer ID.

LicenceChange#

Change log for licenses and subscriptions.

• LCid: Change ID.
• LClid: Licence ID.
• LCwhen: Change date.
• LCwho: Who/what integration made the change.
• LColdvalue: Previous value.
• LCnewvalue: New value.

Knowledge Base#

These are the commonly used tables to report on the knowledge base. The first and third are often joined to show a list of articles and the FAQ list they are each contained in.

KBEntry and KBVotes:

KBEntry#

Article details.

• Abstract: Title of article.
• Description: Description of article.
• Resolution: Resolution of article.
• Datecreated: When the article was created.
• Whocreated: Who made the article.

KBVotes#

Article ratings.

• KBVkbid: Article ID.
• KBVvote: Rating.
• KBVdate: Date voted.
• KBVnegativecomment: Comment left if rated negative.

FAQListHead#

FAQ list details.

• FAQlistdesc: FAQ list name.