Home› Analytics

Building reports - All WI by Support Group

Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
in Analytics

I tried to produce this information by a simple saved search, however the columns are limited. I need the report to include resolution/results and the date it was completed. Can I do this with some SQL in the Analytics section. I had such report written in SQL Server Report Builder on our original Service Manager install but cannot seem to match the tables/views in our new database.

Best Answer

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    Answer ✓
    @Emma_Jenkins

    Try this, it is probably not the best thing to do but it works! Because it is a UNION you have to have the same column names thats why I named the SR ResolutionDescription.

    SELECT WorkItemId AS Id, Title, Status, PriorityId AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, ResolvedDate, 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_Incident AS wiIR
    WHERE Tier like '%Tier 1%'

    UNION
    SELECT WorkItemId AS Id, Title, Status, Priority AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, CompletedDate AS 'ResolvedDate', 
    SCSM.Notes_F647D4C9_9543_73C4_79D0_FCB06C288B5D AS 'ResolutionDescription', LastModified
    FROM [CiresonAnalytics].[dbo].[SM_WorkItem_ServiceRequest] AS wiSR
    LEFT JOIN [ServiceManager].[dbo].[MT_System$WorkItem$ServiceRequest] AS SCSM
    ON wiSR.Id = SCSM.BaseManagedEntityId
    WHERE Tier like '%Tier 1%'

    Thanks,
    Shane.

Answers

  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited April 2019
    Resolved:

    select wi.WorkItemId, FORMAT((DATEADD(hour,2,wi.Created)), 'dd-MM-yyy hh:mm') as Created,FORMAT((DATEADD(hour,2,wi.CompletedDate)), 'dd-MM-yyy hh:mm') as Completed, FORMAT((DATEADD(hour,2,wi.ResolvedDate)), 'dd-MM-yyy hh:mm') as Resolved, wi.AssignedUser, wi.AffectedUser, dsclassification.DisplayString AS Classification, wi.ResolutionDescription
    from WorkItem wi
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
    LEFT JOIN [Enumeration] enumclass ON enumclass.EnumerationID = wi.CategoryId
    LEFT JOIN [DisplayString] dsclassification ON dsclassification.ElementID=enumclass.EnumerationID
    where 
    (DATEDIFF(dd, ResolvedDate, GETDATE()) = 0 OR DATEDIFF(dd, CompletedDate, GETDATE()) = 0)
    AND wi.StatusId IN('2b8830b6-59f0-f574-9c2a-f4b4682f1681', 'B026FDFD-89BD-490B-E1FD-A599C78D440F') 
    AND dssupport.DisplayString in ('{{SuppGroup}}')

    This is for everything resolved today for a specific support group from the ServiceManagement DB
  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
    Thanks I will give this a try!
  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    edited April 2019
    Hi @Emma_Jenkins

    Have you tried using the Cireson Analytics DB? Using Incidents for an example you can do a lot of this without all of the joins:

    SELECT WorkItemId AS Id, Title, Status, PriorityId, 
    AssignedUser, AffectedUser, Category, Tier, ResolvedDate, 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_Incident

    Of course you can add bits to this to be more specific but this is a basic example, I would be happy to build something out more specific to what you are after if you let me know :smile:  You could also add other work items onto this using a little fiddling with UNIONs

    Thanks,
    Shane.
  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
    Shane_White said:
    Hi @Emma_Jenkins

    Have you tried using the Cireson Analytics DB? Using Incidents for an example you can do a lot of this without all of the joins:

    SELECT WorkItemId AS Id, Title, Status, PriorityId, 
    AssignedUser, AffectedUser, Category, Tier, ResolvedDate, 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_Incident

    Of course you can add bits to this to be more specific but this is a basic example, I would be happy to build something out more specific to what you are after if you let me know :smile:  You could also add other work items onto this using a little fiddling with UNIONs

    Thanks,
    Shane.
    That looks almost perfect! I just need it to include Service Requests too and target a particular support group. Is that possible? Thank you!
  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    @Emma_Jenkins

    How is this? Bear in mind you just need to change the name of the Tier for whatever your Support Groups are called ('%support group%')

    SELECT WorkItemId AS Id, Title, Status, PriorityId AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, ResolvedDate, 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_Incident AS wiIR
    WHERE Tier like '%Tier 1%'

    UNION
    SELECT WorkItemId AS Id, Title, Status, Priority AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, CompletedDate AS 'ResolvedDate', 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_ServiceRequest AS wiSR
    WHERE Tier like '%Tier 1%'

    Thanks,
    Shane
  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
    @Shane_White All that is missing now is the Implementation Notes from an SR and I cannot see this in the Analytics tables?
    I have noticed Resolution Description is a shared column in both tables but when I run the report this is empty on SRs so not sure if and where that information would come from? 

    Really appreciate your help!
  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    @Emma_Jenkins

    Leave this with me! The SR Implementation Notes as stored in the Service Manager DB but not pulled through into Cireson DBs so will try find away around this!

    Thanks,
    Shane.
  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    Answer ✓
    @Emma_Jenkins

    Try this, it is probably not the best thing to do but it works! Because it is a UNION you have to have the same column names thats why I named the SR ResolutionDescription.

    SELECT WorkItemId AS Id, Title, Status, PriorityId AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, ResolvedDate, 
    ResolutionDescription, LastModified
    FROM dbo.SM_WorkItem_Incident AS wiIR
    WHERE Tier like '%Tier 1%'

    UNION
    SELECT WorkItemId AS Id, Title, Status, Priority AS 'Priority', 
    AssignedUser, AffectedUser, Category, Tier, CompletedDate AS 'ResolvedDate', 
    SCSM.Notes_F647D4C9_9543_73C4_79D0_FCB06C288B5D AS 'ResolutionDescription', LastModified
    FROM [CiresonAnalytics].[dbo].[SM_WorkItem_ServiceRequest] AS wiSR
    LEFT JOIN [ServiceManager].[dbo].[MT_System$WorkItem$ServiceRequest] AS SCSM
    ON wiSR.Id = SCSM.BaseManagedEntityId
    WHERE Tier like '%Tier 1%'

    Thanks,
    Shane.
  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
    @Shane_White
    That second script has given me exactly what I need. Thank you for your help, information you have provided  has also given me a good basis for similar reports :smile:
Sign In or Register to comment.