Work item relates to Request Offering

Roman_Nepomniashchii

Hi, Team. Who know say me please, how I can calculate the quantity work item, which are created through one of the Request Offering. Thank all very well! Powershell or SQL or other variants?

Geoff_Ross

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

Roman_Nepomniashchii

@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?

Jeff_Lang

@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