Work item relates to Request Offering

Answers

• Geoff_Ross Cireson Consultant O.G.

  August 2018

  Hi Roman,
  
  I did a webinar on this way back in 2016. I found it on vimeo but the quality is so bad you couldn't read the SQL. Here it is updated a bit anyway.
  
  

  SELECT Count(Id) Count, RO
  FROM (
  	SELECT SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C Id, SRRO.TargetObjectDisplayName RO FROM MTV_System$WorkItem$ServiceRequest SR
  	JOIN RelationshipGenericView SRRO
  	ON SR.BaseManagedEntityId = SRRO.SourceObjectId
  	AND SRRO.RelationshipId = '2730587f-3d88-a4e4-42d8-08cf94535a6e'
  	UNION
  	SELECT IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C Id, IRRO.TargetObjectDisplayName RO FROM MT_System$WorkItem$Incident IR
  	JOIN RelationshipGenericView IRRO
  	ON IR.BaseManagedEntityId = IRRO.SourceObjectId
  	AND IRRO.RelationshipId = '2730587f-3d88-a4e4-42d8-08cf94535a6e'
  ) WIRO
  GROUP BY WIRO.RO

  
  Geoff

  1
• Roman_Nepomniashchii Customer IT Monkey ✭

  August 2018 edited August 2018

  @Geoff_Ross
  Cireson Portal can work only with DB ServiceManagement? We insert SQL request in  
  
  

  INSERT INTO ServiceManagement.dbo.DataSourceINSERT INTO ServiceManagement.dbo.DataSource

  VALUES ('f9e8d11b-e105-5fd4-9857-ea20fd406241',

  null,

  NULL,

  'SELECT Count(Id) Count, RO

  FROM (

  SELECT SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C Id, SRRO.TargetObjectDisplayName RO 

  FROM ServiceManager.dbo.MTV_System$WorkItem$ServiceRequest SR

  JOIN ServiceManager.dbo.RelationshipGenericView SRRO ON SR.BaseManagedEntityId = SRRO.SourceObjectId

  AND SRRO.RelationshipId = "2730587f-3d88-a4e4-42d8-08cf94535a6e"

  UNION

  SELECT IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C Id, IRRO.TargetObjectDisplayName RO 

  FROM ServiceManager.dbo.MT_System$WorkItem$Incident IR

  JOIN ServiceManager.dbo.RelationshipGenericView IRRO ON IR.BaseManagedEntityId = IRRO.SourceObjectId

  AND IRRO.RelationshipId = "2730587f-3d88-a4e4-42d8-08cf94535a6e"

  ) WIRO

  GROUP BY WIRO.RO

  ORDER BY Count DESC;',

  NULL);
  
  We don't have result. Data comes empty. We don't have access to ServiceManager from Cireson?

  0
• Jeff_Lang Customer Ninja IT Monkey ✭✭✭✭

  August 2018

  @Roman_Nepomniashchii you can create a view in the ServiceManagement DB that then joins to/reads  tables in the ServiceManager DB. you just need to make sure the account you are using for access also has access to both DB's
  

  0