Iniciar sesión
 
InicioBibliotecaAprendizajeDescargaSoporteComunidadForos
Recursos para Profesionales de TI >
> 
> 
How to get all incidents of affected user?

Respondida How to get all incidents of affected user?

  • sábado, 17 de septiembre de 2011 9:20
     
     
    Inicie sesión para votar
    0

    Hello Everyone!

     

    We have another requirement in SCSM, how can I get all incidents of affected user and display it in a list? I have suggested the easiest way to do it from Travis post, but they wanted to see it in one glance without clicking anything.

    Please help on how to code it. I have checked the database and found out that it is stored in this view  MTV_System$WorkItem$Incident.

     

    Best Regards,

    Zel

     

Todas las respuestas

  • domingo, 18 de septiembre de 2011 19:21
    Moderador
     
     Respondida
    Inicie sesión para votar
    0

    You could contruct a custom task or command line tool to do this, for example:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.EnterpriseManagement;
    using Microsoft.EnterpriseManagement.Configuration;
    using Microsoft.EnterpriseManagement.Common;
    using Microsoft.Win32;

    namespace ListUserIncidents
    {
        class ListUserIncidents
        {
            static void Main(string[] args)
            {
                //samAccountName
                string sUser = args[0];

                //Get the server name
                string sServer = Registry.GetValue("HKEY_CURRENT_USER\\Software\\Microsoft\\System Center\\2010\\Service Manager\\Console\\User Settings", "SDKServiceMachine", "localhost").ToString();

                //Connect
                EnterpriseManagementGroup emg = new EnterpriseManagementGroup(sServer);

                //Build query string
                string sCriteria = String.Format(@"<Criteria xmlns=""http://Microsoft.EnterpriseManagement.Core.Criteria/"">
                                                    <Expression>                                           
                                                     <SimpleExpression>
                                                      <ValueExpressionLeft>
                                                       <Property>$Context/Path[Relationship='WorkItem!System.WorkItemAffectedUser' TypeConstraint='System!System.Domain.User']/Property[Type='System!System.Domain.User']/UserName$</Property>
                                                       </ValueExpressionLeft>
                                                       <Operator>Equal</Operator>
                                                      <ValueExpressionRight>
                                                        <Value>" + sUser + @"</Value>
                                                      </ValueExpressionRight>
                                                    </SimpleExpression>
                                                  </Expression>                            
                                        </Criteria>");

                //Incident class
                ManagementPackClass mpcIncident = emg.EntityTypes.GetClass(new Guid("a604b942-4c7b-2fb2-28dc-61dc6f465c68"));

                //Typical type projection
                ManagementPackTypeProjection mptpIncident = emg.EntityTypes.GetTypeProjection(new Guid("1862825e-21bc-3ab2-223e-2a7f2439ba75"));

                //Get the incident library MP  (System.WorkItem.Incident.Library)
                ManagementPack mpIncidentLibrary = emg.ManagementPacks.GetManagementPack(new Guid("DD26C521-7C2D-58C0-0980-DAC2DACB0900"));

                //Get the incidents
                ObjectProjectionCriteria opcIncidents = new ObjectProjectionCriteria(sCriteria, mptpIncident, mpIncidentLibrary, emg);
                IObjectProjectionReader<EnterpriseManagementObject> oprIncidents = emg.EntityObjects.GetObjectProjectionReader<EnterpriseManagementObject>(opcIncidents, ObjectQueryOptions.Default);

                foreach (EnterpriseManagementObjectProjection emopIncident in oprIncidents)
                {
                    Console.WriteLine(emopIncident.Object.DisplayName);
                }

            }
        }
    }


    Of course, this is from the omdb and not the DW.
     
  • lunes, 19 de septiembre de 2011 6:15
     
     
    Inicie sesión para votar
    0

    Thanks for your reply Rob! I'll try the code.

    Do you have any idea on how to get the userName of Affected User? I tried to use the BaseManagementId and replace the criteria property into

    <Property>$Context/Path[Relationship='WorkItem!System.WorkItemAffectedUser' TypeConstraint='System!System.Domain.User']/Property[Type='System!System.Domain.User']/BaseManagedEntityId$</Property>

    but there is an error

    "The provided path ($Context/Path[Relationship='WorkItem!System.WorkItemAffectedUser' TypeConstraint='System!System.Domain.User']/Property[Type='System!System.Domain.User']/BaseManagedEntityId$) was not valid: Property reference 'Property[Type='System!System.Domain.User']/BaseManagedEntityId$' could not be resolved."

    Please help! Thanks again. :)

     

    Regards,

    Zel

     

     
  • lunes, 19 de septiembre de 2011 19:18
    Moderador
     
     Respondida
    Inicie sesión para votar
    0

    If you have an EnterpriseManagementObject for an incident (such as emopIncident.Object from above), you can get the affected user name for it like this:

    //Microsoft.Windows.Library
    ManagementPack mpWindows =
      emg.ManagementPacks.GetManagementPack(new Guid("545131F0-58DE-1914-3A82-4FCAC9100A33"));

    //Get the Microsoft.AD.User class
      ManagementPackClass mpcADUser = emg.EntityTypes.GetClass("Microsoft.AD.User", mpWindows);

    //Affected user relationship Guid
    Guid rel = new Guid("dff9be66-38b0-b6d6-6144-a412a3ebd4ce");

    EnterpriseManagementObject emoAffectedUser = null;

    foreach (EnterpriseManagementRelationshipObject<EnterpriseManagementObject> obj in
        emg.EntityObjects.GetRelationshipObjectsWhereSource<EnterpriseManagementObject>(emoIncident.Id, TraversalDepth.OneLevel, ObjectQueryOptions.Default))
    {
        if (obj.RelationshipId == rel)

       {
            emoAffectedUser = obj.TargetObject;
            break;
       }
    }

    string sUsername = emoAffectedUser[mpcADUser, "UserName"].Value.ToString()

    //Or get DisplayName for user like this

    string sUsername = emoAffectedUser.DisplayName;

     


    • Editado Rob.FordMVP, Moderator lunes, 19 de septiembre de 2011 19:20
    • Marcado como respuesta zelabz martes, 20 de septiembre de 2011 2:49
    •  
     
  • miércoles, 12 de octubre de 2011 15:48
     
     
    Inicie sesión para votar
    0

    try this T-SQL query,

    use DWDataMart

    Declare @LanguageCode varchar(3), @AssignedToUserName nvarchar(256)
    SET @LanguageCode='ENU'
    SET @AssignedToUserName = '<INSERT ASSIGNED TO USERNAME HERE>'

    SELECT DISTINCT
        I.IncidentDimKey,
        I.CreatedDate,
        I.ResolvedDate,
        dbo.WorkItemAssignedToUserFactvw.DeletedDate,
        I.Priority,
        I.Id,
        I.Title,
        Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) ,
        StatusEnum.IncidentStatusId AS StatusId,
        Impact = ISNULL(ImpactDS.DisplayName, ImpactEnum.IncidentImpactValue),
        ImpactEnum.IncidentImpactId AS ImpactId,
        AssignedTo.UserDimKey AssignedToUserId,
        AssignedTo.DisplayName AssignedToUserName
    FROM    
        dbo.IncidentDimvw I
        INNER JOIN dbo.WorkItemDimvw WI ON I.EntityDimKey = WI.EntityDimKey
        LEFT OUTER JOIN    dbo.WorkItemAssignedToUserFactvw ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey --AND dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL
        LEFT OUTER JOIN dbo.UserDimvw AS AssignedTo ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
        LEFT OUTER JOIN dbo.IncidentStatusvw AS StatusEnum ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
        LEFT OUTER JOIN dbo.DisplayStringDimvw StatusDS ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId AND StatusDS.LanguageCode = @LanguageCode
        LEFT OUTER JOIN dbo.IncidentImpactvw AS ImpactEnum ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId
        LEFT OUTER JOIN dbo.DisplayStringDimvw ImpactDS ON ImpactEnum.EnumTypeId=ImpactDS.BaseManagedEntityId AND ImpactDS.LanguageCode = @LanguageCode
    WHERE
        AssignedTo.DisplayName = @AssignedToUserName

     

     

    or try this,

     

    use DWDataMart

     SELECT

      i.Id
        , i.BaseManagedEntityId

     ,afu.DisplayName as affecteduser


     ,assigned_u.DisplayName as AssignedUser
     ,wu.CreatedDate as AssignedDate
     
     ,assigned_u2.DisplayName as FirstAssignedUser
     ,a2.CreatedDate as FirstAssignedDate
     ,ru.DisplayName as resolvedbyuser

     ,i.CreatedDate
     ,i.Priority
     ,ISNULL(resolutioncategory.DisplayName, i.ResolutionCategory) AS ResolutionCategory
        ,ISNULL(groupnames.DisplayName, i.TierQueue)  AS SupportGroup
        ,i.ResolvedDate

     ,cs1.EnteredDate as FirstCommentDate
        ,case
        when cs1.EnteredBy = 'DOMAIN\SERVICEACCOUNT'

      and cs1.Comment like 'Email Incident Comment From: "%'
      then     left (   right(cs1.Comment, len(cs1.Comment) - 30) ,
          charindex ('"',right(cs1.Comment, len(cs1.Comment) - 30)  ) -1 )
     else cs1.EnteredBy
        end
        AS FirstCommenter
        ,cs1.EnteredBy as RawFirstCommenter
        ,cs1.Comment as FirstComment
      

     
    FROM
          dbo.IncidentDimvw AS i
    inner join dbo.WorkItemDimvw wi on i.EntityDimKey = wi.EntityDimKey
    LEFT OUTER join WorkItemAffectedUserFactvw wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey
       and wiau.DeletedDate is null
    LEFT OUTER join UserDimvw afu on wiau.WorkItemAffectedUser_UserDimKey = afu.UserDimKey
    LEFT OUTER JOIN dbo.DisplayStringDimvw AS groupnames ON i.TierQueue = groupnames.ElementName
     and groupnames.LanguageCode='ENU'
    LEFT OUTER JOIN dbo.DisplayStringDimvw AS resolutioncategory ON i.ResolutionCategory = resolutioncategory.ElementName
       and resolutioncategory.LanguageCode = 'ENU'
    --resolved by user
    left outer join (select
      r3.IncidentDimKey,
      r3.TroubleTicketResolvedByUser_UserDimKey
      from dbo.IncidentResolvedByUserFactvw  AS r3
      where isnull(r3.DeletedDate,'31-DEC-2099')  =
        (select min(isnull(r4.DeletedDate,'31-DEC-2099'))
      from dbo.IncidentResolvedByUserFactvw  AS r4
      where r4.incidentdimkey = r3.incidentdimkey) ) r2
      on r2.IncidentDimKey =  i.IncidentDimKey
    left outer join UserDimvw ru on r2.TroubleTicketResolvedByUser_UserDimKey = ru.UserDimKey


    --assigned to user
     left outer  join  dbo.WorkItemAssignedToUserFactvw wu
            on WI.WorkItemDimKey = wu.WorkItemDimKey
             and wu.DeletedDate is null
     left outer  join dbo.UserDimvw assigned_u
            on wu.WorkItemAssignedToUser_UserDimKey = assigned_u.UserDimKey 
    -- first assigned user
     left outer join (select
      a3.WorkItemDimKey,
      a3.WorkItemAssignedToUser_UserDimKey,
      a3.CreatedDate,
      a3.DeletedDate
      from dbo.WorkItemAssignedToUserFactvw  AS a3
      where isnull(a3.DeletedDate,'31-DEC-2099')  =
        (select min(isnull(a4.DeletedDate,'31-DEC-2099'))
      from dbo.WorkItemAssignedToUserFactvw  AS a4
      where a4.WorkItemDimKey = a3.WorkItemDimKey) ) a2
      on a2.WorkItemDimKey = WI.WorkItemDimKey
     left outer  join dbo.UserDimvw assigned_u2
            on a2.WorkItemAssignedToUser_UserDimKey = assigned_u2.UserDimKey 
     
    --first response
    --if we were to remove auto replies and others first, then we'd need to use a function
    left outer join
     ( select
     r1.SourceObjectId,
        r1.TargetObjectId,
        c1.Comment_AECE7B96_331B_498C_2F77_AEA4376EFFF1 as Comment,
        c1.EnteredBy_A14C9A28_2F82_D1F2_BCCD_A4E3745179B5 as EnteredBy,
        c1.EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6 as EnteredDate
         from ServiceManager.dbo.MTV_System$WorkItem$TroubleTicket$AnalystCommentLog AS c1
         inner join  ServiceManager.dbo.RelationshipGenericView AS r1
          on c1.BaseManagedEntityId = r1.TargetObjectId
          where  c1.EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6 =
          (select MIN (c2.EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6)
            from ServiceManager.dbo.MTV_System$WorkItem$TroubleTicket$AnalystCommentLog AS c2
       inner join  ServiceManager.dbo.RelationshipGenericView AS r2
        on c2.BaseManagedEntityId = r2.TargetObjectId
            where r2.SourceObjectId = r1.SourceObjectId)
          ) cs1
           on i.BaseManagedEntityId = cs1.SourceObjectId

     

     

    Regards,


    • Editado Aspen123 miércoles, 12 de octubre de 2011 15:51
    •  
     
  • viernes, 27 de julio de 2012 19:23
     
     
    Inicie sesión para votar
    0
    how to get second assigned to user.