Versions Compared

Old Version 9

changes.mady.by.user user-1bc08

Saved on

compared with

New Version 10

changes.mady.by.user user-1bc08

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Below outlines the most commonly used Microsoft SQL Commands utilized with most Perspectium integrations:The following are examples of database views queries you can run for reporting: 

JOIN

A Join statement is used to combine data or rows from two or more tables based on a common field between them. The table below outlines the join relationship between two tables and a brief explanation of the use cases.

QueryUse Cases


Code Block
languagesql
LEFT JOIN [dbo].[sys_user] [sys_user] ON ([cmdb_ci].[assigned_to] = [sys_user].[sys_id])


View the users assigned to configuration items


Code Block
languagesql
LEFT JOIN [dbo].[task_sla] [task_sla] ON ([incident].[number] = [task_sla].[dv_task])


Incidents with their SLA


Code Block
languagesql
LEFT JOIN [dbo].[cmn_location] [cmn_location] ON ([incident].[location] = [cmn_location].[sys_id])


View incidents with location


Code Block
languagesql
LEFT JOIN [dbo].[sys_user] [sys_user] ON ([incident].[caller_id] = [sys_user].[sys_id])


View incidents with users by caller


Code Block
languagesql
LEFT JOIN [dbo].[sys_user] [sys_user] ON ([incident].[dv_caller_id] = [sys_user].[name])


For customers that prefer to work with display value fields instead of id. This will join the table based on display name


Code Block
languagesql
INNER JOIN `cmdb_ci` ON (`incident`.`cmdb_ci` = `cmdb_ci`.`sys_id`)


Detecting incidents that have CIs attached


Code Block
languagesql
LEFT JOIN [dbo].[task_sla] [task_sla] ON ([task].[number] = [task_sla].[dv_task])


View SLAs by parent task table grouping by display number