Below outlines the most commonly used Microsoft SQL Commands utilized with most Perspectium integrations:
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.
| Query | Use Cases |
|---|---|
LEFT JOIN [dbo].[sys_user] [sys_user] ON ([cmdb_ci].[assigned_to] = [sys_user].[sys_id]) | View the users assigned to configuration items |
LEFT JOIN [dbo].[task_sla] [task_sla] ON ([incident].[number] = [task_sla].[dv_task]) | Incidents with their SLA |
LEFT JOIN [dbo].[cmn_location] [cmn_location] ON ([incident].[location] = [cmn_location].[sys_id]) | View incidents with location |
LEFT JOIN [dbo].[sys_user] [sys_user] ON ([incident].[caller_id] = [sys_user].[sys_id]) | View incidents with users by caller |
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 |
INNER JOIN `cmdb_ci` ON (`incident`.`cmdb_ci` = `cmdb_ci`.`sys_id`) | Detecting incidents that have CIs attached |
LEFT JOIN [dbo].[task_sla] [task_sla] ON ([task].[number] = [task_sla].[dv_task]) | View SLAs by parent task table grouping by display number |