CMDB Relationship recursive reporting.

[Fernando Gois]

Hello. Has anyone built a CMDB CI relationship recursive report?

By that I mean, starting at the Business Service CI level, list the children CIs related to that Business Service. But after that, also list the lower level CIs that are "children of the children" and so forth of a recursive way. We could probably establish how many levels down the chain to report.

This report would pretty my be a representation of what we see in a Business Service dependency view, but instead of been graphical, it is a list showing all the CIs that make up the business service and their relationships.

Please, let me know.

Thanks. Fernando.

[mechamberlain · Accepted solution]

Fernando,

I know of no way to do such a report in the Reporting Module.   I'm not sure if this will help you or not, but I do have a script I threw together an example script that will do two things based on giving the script the SysID of a single "target" CI.   It will "walk" the CI relationship tree downstream of the target CI and compile an array of the CI SysIDs it encounters.   While doing this, it also displays the level of in hierarchy, the Name and the Class of each of the downstream CI's.

This is just to show you one way to get at the data.   I'm not sure if this will help you, but I hope that it may be a starting point for you.

Attached is the script.   It was 'thrown together" so it may not be pretty but it works as a proof of concept:

[Fernando Gois]

Thank you Martin.

This is a very nice script you put together.

And like you said, I did not find a way to do this thru Reporting. Your script is definitely a good alternative to be explored.

Thanks a lot Martin!

[Paul Coste2]

While there is no way out of box to do this, it is possible create a new table that stores many-to-many relationships from the top node to all child nodes, i.e. flatten the relationships so that a Service CI will have one record for every Child CI, Grandchild CI, and so-on.   You need to then keep this table updated as CI Relationships are created, updated, and deleted.   If you do this, then the resulting table can be directly queried and the Service-to-CI dependencies can be put into Reports.   Furthermore this table could be used as a replacement for needing to continually walk the CI relationship tree in order to determine Impacted Services, thereby saving calculation time, and allowing Impacted Services to be calculated more automatically, even aggregating these Services across multiple Affected CIs.

[Jeff Boltz1]

What about the CMDB Query Builder in Istanbul?

[Paul Coste2]

Sort of.   But the limitations of the query builder are a problem.   The query must fit a specific hierarchical structure.   It cannot just return all children.   Also, the way it returns the results is not just a list of CIs, so it is a bit difficult to work with the results.   And the way the results are returned increases the number of results, which cannot be shown on the screen all at once, and if you are doing this on a large scale, you will likely reach the maximum number of exported records sooner than you would like.   There is also a lack of ability to reference the queries in other areas of the tool.   So, for instance, if you are in a standard list of CIs that are affected CIs on a Change Request, and you want to remove those affected CIs that are descendants of a particular service, there is no easy way to do this.   And as for reporting, there is no way to include these in the actual reporting app.   While the CMDB Query Builder is a good step forward, it is not well integrated into other areas of ServiceNow.   Or at least it wasn't in Istanbul -- I have not yet gotten a chance to test out the enhancements in Jakarta.