В этой публикации я буду аккумулировать по мере поступления запросы к базам данных системы System Center Service Manager. Их можно использовать либо для построения отчетов, либо, например, для настройки виджетов SQL для Cireson. Примеры SQL запросов для Service Manager привожу ниже.
В основном это очердная моя шпаргалка, но в случае вопросов пишите – постараюсь что-то подсказать.
Получение списка всех инцидентов и запросов на обслуживание
Пример SQL запроса:
SELECT
'Incident' as Type
,wi.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] as ID
,wi.[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] as Title
,dss.DisplayName as Status
,bmeassigned.DisplayName as AssignedUser
,CAST(wi.[Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794] AS varchar) AS Priority
,dsc.DisplayName as Categoty
,bmeaffected.DisplayName as AffectedUser
,dssg.DisplayName as SupportGroup
,CAST(bme.LastModified as datetime) as LastModified
,CAST(wi.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] as datetime) as Created
,wi.[IsParent_8C8C5710_9D59_DE9D_7846_A5BAB8744F0E] as isParent
FROM [ServiceManager].[dbo].[MTV_System$WorkItem$Incident] wi
LEFT JOIN [ServiceManager].[dbo].[Relationship] rlassigned on ((wi.BaseManagedEntityId = rlassigned.SourceEntityId) AND (rlassigned.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722') AND (rlassigned.IsDeleted = 0))
LEFT JOIN [ServiceManager].[dbo].[Relationship] rlaffected on ((wi.BaseManagedEntityId = rlaffected.SourceEntityId) AND (rlaffected.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE') AND (rlaffected.IsDeleted = 0))
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bme on wi.BaseManagedEntityId = bme.BaseManagedEntityId
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dss on ((dss.LTStringId = wi.Status_785407A9_729D_3A74_A383_575DB0CD50ED) AND (dss.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dsc on ((dsc.LTStringId = wi.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA) AND (dsc.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dssg on ((dssg.LTStringId = wi.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C) AND (dssg.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bmeassigned on (rlassigned.TargetEntityId = bmeassigned.BaseManagedEntityId)
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bmeaffected on (rlaffected.TargetEntityId = bmeaffected.BaseManagedEntityId)
UNION ALL
SELECT
'Service Request' as Type
,wi.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] as ID
,wi.[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] as Title
,dss.DisplayName as Status
,bmeassigned.DisplayName as AssignedUser
,dsp.DisplayName as Priority
,dsc.DisplayName as Categoty
,bmeaffected.DisplayName as AffectedUser
,dssg.DisplayName as SupportGroup
,CAST(bme.LastModified as datetime) as LastModified
,CAST(wi.[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] as datetime) as Created
,wi.[IsParent_8C8C5710_9D59_DE9D_7846_A5BAB8744F0E] as isParent
FROM [ServiceManager].[dbo].[MTV_System$WorkItem$ServiceRequest] wi
LEFT JOIN [ServiceManager].[dbo].[Relationship] rlassigned on ((wi.BaseManagedEntityId = rlassigned.SourceEntityId) AND (rlassigned.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722') AND (rlassigned.IsDeleted = 0))
LEFT JOIN [ServiceManager].[dbo].[Relationship] rlaffected on ((wi.BaseManagedEntityId = rlaffected.SourceEntityId) AND (rlaffected.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE') AND (rlaffected.IsDeleted = 0))
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bme on wi.BaseManagedEntityId = bme.BaseManagedEntityId
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dss on ((dss.LTStringId = wi.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F) AND (dss.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dsc on ((dsc.LTStringId = wi.Area_0E2739F7_6889_09EF_B260_35CD954D3563) AND (dsc.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dssg on ((dssg.LTStringId = wi.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488) AND (dssg.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[DisplayStringView] dsp on ((wi.Priority_DF8E4EEB_229C_2351_00CE_4343A94C0052 = dsp.LTStringId) AND (dsp.LanguageCode = 'RUS'))
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bmeassigned on (rlassigned.TargetEntityId = bmeassigned.BaseManagedEntityId)
LEFT JOIN [ServiceManager].[dbo].[BaseManagedEntity] bmeaffected on (rlaffected.TargetEntityId = bmeaffected.BaseManagedEntityId)Пример выполнения запроса:
