Work item relates to Request Offering

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?

Answers

  • Geoff_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭
    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_NepomniashchiiRoman_Nepomniashchii Customer IT Monkey ✭
    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?
  • Jeff_LangJeff_Lang Customer Advanced IT Monkey ✭✭✭
    @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
Sign In or Register to comment.