Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Report on Parent Child dependency

prashantsingh

Hi team,

Has anyone created the report on Parent Child dependency, Following SQL query uses the ID and not name of the node.

SELECT TOP 1000 * FROM [dbo].[Dependencies]

Please help if anyone has done it.

Thanks

Prashant

Find more posts tagged with

Accepted answers

All comments

nick_scott

It would be nice to get a canned report. I have been experiencing issues with dependencies and would like an easy way to compare the different configurations.

mesverrum

So this can be a bit messy because of all the scenarios where basically any type of object can be a parent to any other type of child object type. Going to have to play with some case logic to make this into less of an unreadable mess....

mesverrum

Ok so this is as far as I'm going to do the leg work on this because I expect it will take care of 90% of use cases. Feel free to follow the model and add on if you need one of those other cases.

Also, the replace() function is brand new in Orion Platform 2017.3 (NPM 12.2) and later, if you aren't up to date then just use the commented version of the parent and child lines instead.

SELECT d.Name as [Dependency]
,Replace(Replace(Replace(d.ParentEntityType,'Orion.',''),'SEUM.','WPM '),'s','') as [Parent Type] --,d.ParentEntityType as [Parent Type]
,case when np.caption is not null then np.Caption
when ap.Name is not null then ap.Name
when gp.Name is not null then gp.Name
when tp.Name is not null then tp.Name
when ip.FullName is not null then ip.FullName
end as [Parent]
,Replace(Replace(Replace(d.ChildEntityType,'Orion.',''),'SEUM.','WPM '),'s','') as [Child Type] --,d.ChildEntityType as [Child Type]
,case when nc.caption is not null then nc.Caption
when ac.Name is not null then ac.Name
when gc.Name is not null then gc.Name
when tc.Name is not null then tc.Name
when ic.FullName is not null then ic.FullName
end as [Child]

FROM Orion.Dependencies d
left join orion.nodes np on np.Uri=d.ParentUri --Nodes
left join orion.nodes nc on nc.Uri=d.ChildUri
left join orion.apm.Application ap on ap.Uri=d.ParentUri --Applications
left join orion.apm.Application ac on ac.Uri=d.ChildUri
left join orion.Groups gp on gp.Uri=d.ParentUri --Groups
left join orion.Groups gc on gc.Uri=d.ChildUri
left join orion.seum.Transactions tp on tp.Uri=d.ParentUri --Transactions
left join orion.seum.Transactions tc on tc.Uri=d.ChildUri
left join orion.npm.Interfaces ip on ip.Uri=d.ParentUri --Interfaces
left join orion.npm.Interfaces ic on ic.Uri=d.ChildUri

order by d.Name

--to do
--Player Locations
--AppInsight for Exchange: Applications
--AppInsight for IIS: Applications
--AppInsight for IIS: Application Pools
--AppInsight for Exchange: Databases
--AppInsight for Exchange: Databases Copies
--Database Instances
--LUNs
--AppInsight for Exchange: Mailboxes
--AppInsight for IIS: Sites
--AppInsight for SQL: Databases
--AppInsight for SQL: Applications
--Transaction Steps

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

tedapap

This is giving me an error that "* Query not valid"

tedapap

I get the following error in SWQL Studio. "Source Entity [orion.seum.Transactions] not found in catalog"

tedapap

This worked for me on Orion version

SELECT d.Name as [Dependency]  ,d.ParentEntityType as [Parent Type]  ,case when np.caption is not null then np.Caption  when ap.Name is not null then ap.Name  when gp.Name is not null then gp.Name  when ip.FullName is not null then ip.FullName  end as [Parent]  ,d.ChildEntityType as [Child Type]  ,case when nc.caption is not null then nc.Caption  when ac.Name is not null then ac.Name  when gc.Name is not null then gc.Name    when ic.FullName is not null then ic.FullName  end as [Child]    FROM Orion.Dependencies d  left join orion.nodes np on np.Uri=d.ParentUri left join orion.nodes nc on nc.Uri=d.ChildUri  left join orion.apm.Application ap on ap.Uri=d.ParentUri left join orion.apm.Application ac on ac.Uri=d.ChildUri  left join orion.Groups gp on gp.Uri=d.ParentUri left join orion.Groups gc on gc.Uri=d.ChildUri  left join orion.npm.Interfaces ip on ip.Uri=d.ParentUri  left join orion.npm.Interfaces ic on ic.Uri=d.ChildUri    order by d.Name

mr.e

Strangely, I get Query Not Valid for both scripts. By the way, I tried to fix the problem by replacing Orion by SolarWindsOrion which is the name of our DB. Still, neither of these queries worked for me. Any ideas?

mesverrum

If you are referencing the name of the database then I'm assuming you are trying this as SQL, it's not SQL it's SWQL. You would put this into a custom query resource or use a custom table resource with the custom data option set to SWQL.

My original version assumes you have NPM/SAM/WPM but the version posted by tedapap will work with just NPM/SAM and also works for older versions if you don't have 2017.3 installed yet.

Whenever you run into issues executing custom SWQL your best bet to troubleshoot it is by loading SWQL studio and trying it there because it gives far more descriptive errors than just saying it's "invalid"
GitHub - solarwinds/OrionSDK: SDK for the SolarWinds Orion platform, including tools, documentation, and samples in Powe…

lynchnigel

Does the node to node link report that you can create not give you what you want without having to create SQL for it?