SQL Query for Parent Child Report with Names vice ObjectID

1st off, we are using Orion Platform, SRM, VNQM, NCM, NPM, NTA, VMAN, UDT, SAM: 2020.2.4

My customer has put in hundreds of Parent/Child relationships into SW and is asking me to create a report so he can see what he has put in and what still needs to be put in off his list.

I have been able to pull half the information, but I cannot get the other half. 

What tables do I pull from to convert the ParentNetObjectID & ChildNetObjectID columns into the Node Caption/Name?

Columns needed:  Parent (Name/Caption) & Child (Name/Caption)

Here is what I have so far:

select Name, ParentNetObjectID, ChildNetObjectID from Dependencies

Order by Name ASC

How do I connect the dots?

Parents
  • Hello,

    You may try similar query in swis, the number of joined entities depends on installed orion products.

    The list of all entities which are possible to made dependency with is listed in swis table "Orion.DependencyEntities"

    SELECT 
        dep.[DependencyId], 
        dep.[Name], 
        dep.[LastUpdateUTC], 
        dep.[ParentUri], 
        dep.[ChildUri], 
        dep.[ParentEntityType] as ParentEntity, 
        dep.[ParentDisplayName],
        dep.[ChildEntityType] as ChildEntity, 
        dep.[ChildDisplayName], 
        dep.[AutoManaged],
        dep.[IncludeInStatusCalculation],
        dep.[Description],
        dep.[Owner],
        dep.[FoundAsAutoManaged]
        --, dep.[EngineID], dep.[Category], dep.[ParentNetObjectID], dep.[ChildNetObjectID] 
    FROM 
    ( SELECT d.[DependencyId], d.[Name], d.[LastUpdateUTC], d.[AutoManaged], d.[EngineID], d.[Category], 
    d.[ParentUri], d.[ParentEntityType], d.[ParentNetObjectID], parent.[DisplayName] as ParentDisplayName, 
    d.[ChildUri], d.[ChildEntityType], d.[ChildNetObjectID], child.[DisplayName] as ChildDisplayName, d.[IncludeInStatusCalculation],
    d.[Description], d.[Owner], d.[FoundAsAutoManaged]
    FROM Orion.Dependencies d
    JOIN 
    (
    
        SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Groups') d
        JOIN (SELECT Uri, [DisplayName] FROM Orion.Groups ) x ON d.[ParentUri]= x.[Uri]
    
        UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Component') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Component ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Database') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Database ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.DatabaseCopy') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.DatabaseCopy ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Mailbox') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Mailbox ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.GenericApplication') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.GenericApplication ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.ApplicationPool') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.ApplicationPool ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.Site') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Site ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.SqlDatabase') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlDatabase ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.SqlServerApplication') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlServerApplication ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.ContainerMembers') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.ContainerMembers ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.DPA.DatabaseInstance') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.DPA.DatabaseInstance ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Nodes') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.Nodes ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.NPM.Interfaces') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.NPM.Interfaces ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.SRM.LUNs') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.SRM.LUNs ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Volumes') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.Volumes ) x ON d.[ParentUri]= x.[Uri]
        )
    
    ) parent ON d.[ParentUri] = parent.[ParentUri]
    JOIN 
    (
    
        SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Groups') d
        JOIN (SELECT Uri, [DisplayName] FROM Orion.Groups ) x ON d.[ChildUri]= x.[Uri]
      
         UNION ALL (
         SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Component') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Component ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Database') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Database ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.DatabaseCopy') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.DatabaseCopy ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Mailbox') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Mailbox ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.GenericApplication') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.GenericApplication ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.ApplicationPool') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.ApplicationPool ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.Site') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Site ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.SqlDatabase') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlDatabase ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.SqlServerApplication') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlServerApplication ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.ContainerMembers') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.ContainerMembers ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.DPA.DatabaseInstance') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.DPA.DatabaseInstance ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Nodes') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.Nodes ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.NPM.Interfaces') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.NPM.Interfaces ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.SRM.LUNs') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.SRM.LUNs ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Volumes') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.Volumes ) x ON d.[ChildUri]= x.[Uri]
        )
    
    ) child ON d.[ChildUri] = child.[ChildUri]
     ) as dep  ORDER BY dep.[Name] DESC
    

    Regards Radek

Reply
  • Hello,

    You may try similar query in swis, the number of joined entities depends on installed orion products.

    The list of all entities which are possible to made dependency with is listed in swis table "Orion.DependencyEntities"

    SELECT 
        dep.[DependencyId], 
        dep.[Name], 
        dep.[LastUpdateUTC], 
        dep.[ParentUri], 
        dep.[ChildUri], 
        dep.[ParentEntityType] as ParentEntity, 
        dep.[ParentDisplayName],
        dep.[ChildEntityType] as ChildEntity, 
        dep.[ChildDisplayName], 
        dep.[AutoManaged],
        dep.[IncludeInStatusCalculation],
        dep.[Description],
        dep.[Owner],
        dep.[FoundAsAutoManaged]
        --, dep.[EngineID], dep.[Category], dep.[ParentNetObjectID], dep.[ChildNetObjectID] 
    FROM 
    ( SELECT d.[DependencyId], d.[Name], d.[LastUpdateUTC], d.[AutoManaged], d.[EngineID], d.[Category], 
    d.[ParentUri], d.[ParentEntityType], d.[ParentNetObjectID], parent.[DisplayName] as ParentDisplayName, 
    d.[ChildUri], d.[ChildEntityType], d.[ChildNetObjectID], child.[DisplayName] as ChildDisplayName, d.[IncludeInStatusCalculation],
    d.[Description], d.[Owner], d.[FoundAsAutoManaged]
    FROM Orion.Dependencies d
    JOIN 
    (
    
        SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Groups') d
        JOIN (SELECT Uri, [DisplayName] FROM Orion.Groups ) x ON d.[ParentUri]= x.[Uri]
    
        UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Component') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Component ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Database') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Database ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.DatabaseCopy') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.DatabaseCopy ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.Exchange.Mailbox') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Mailbox ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.GenericApplication') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.GenericApplication ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.Application') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Application ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.ApplicationPool') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.ApplicationPool ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.IIS.Site') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Site ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.SqlDatabase') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlDatabase ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.APM.SqlServerApplication') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlServerApplication ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.ContainerMembers') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.ContainerMembers ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.DPA.DatabaseInstance') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.DPA.DatabaseInstance ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Nodes') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.Nodes ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.NPM.Interfaces') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.NPM.Interfaces ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.SRM.LUNs') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.SRM.LUNs ) x ON d.[ParentUri]= x.[Uri]
        ) UNION ALL (
            SELECT x.[displayName],d.[ParentUri] FROM (SELECT [ParentUri] FROM Orion.Dependencies WHERE [ParentEntityType] = 'Orion.Volumes') d
            JOIN (SELECT Uri, [DisplayName] FROM Orion.Volumes ) x ON d.[ParentUri]= x.[Uri]
        )
    
    ) parent ON d.[ParentUri] = parent.[ParentUri]
    JOIN 
    (
    
        SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Groups') d
        JOIN (SELECT Uri, [DisplayName] FROM Orion.Groups ) x ON d.[ChildUri]= x.[Uri]
      
         UNION ALL (
         SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Component') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Component ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Database') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Database ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.DatabaseCopy') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.DatabaseCopy ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.Exchange.Mailbox') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.Exchange.Mailbox ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.GenericApplication') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.GenericApplication ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.Application') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Application ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.ApplicationPool') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.ApplicationPool ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.IIS.Site') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.IIS.Site ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.SqlDatabase') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlDatabase ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.APM.SqlServerApplication') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.APM.SqlServerApplication ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.ContainerMembers') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.ContainerMembers ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.DPA.DatabaseInstance') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.DPA.DatabaseInstance ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Nodes') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.Nodes ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.NPM.Interfaces') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.NPM.Interfaces ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.SRM.LUNs') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.SRM.LUNs ) x ON d.[ChildUri]= x.[Uri]
        ) UNION ALL (
          SELECT x.[displayName],d.[ChildUri] FROM (SELECT [ChildUri] FROM Orion.Dependencies WHERE [ChildEntityType] = 'Orion.Volumes') d
         JOIN (SELECT Uri, [DisplayName] FROM Orion.Volumes ) x ON d.[ChildUri]= x.[Uri]
        )
    
    ) child ON d.[ChildUri] = child.[ChildUri]
     ) as dep  ORDER BY dep.[Name] DESC
    

    Regards Radek

Children
No Data