Database view to show servers with no relationship to a business service

[Rick Mann]

Hello SN Community

I'm trying to create a report which I think will require a database view.   I want to identify Windows servers that do not have a "depends on:used by" relationship with a
Business Service.   I've been working on creating a database view, but I'm not certain on how to build the where clause and if I need a left join.

My thought is to join the cmdb_rel_ci table with the cmdb_ci_win_server table.   I want to show all records from the cmdb_win_server table that do not have a record in the cmdb_rel_ci based on the query:

parent.sys_class_name=cmdb_ci_service&&type=1&&child.sys_class_name=cmdb_ci_win_server

Any help with the db view is appreciated.

Rick

[DrewW]

The wiki has good info on this

http://wiki.servicenow.com/index.php?title=Database_Views

But I seam to remember looking at this before and found that that the easiest way to do this was to write a script that returns an array of sys_id's of servers that are not listed in the cmdb_rel_ci table with the criteria you want.

Put the below function in s business rule or script include then go to the reporting app and run a report on the windows server table with the filter of sys_id is javascript:serversWithNoBSLinked() and you got what you want.   Its a bit slow and if you want a certain type just add that to the first query.

If you still want to go with the view keep in mind that the type field is a reference field and you are going to need the sys_id of the type for the filter.

function serversWithNoBSLinked(){

       var au = new ArrayUtil();

       var linked = [];

       var notlinked = [];

       var gr = new GlideRecord("cmdb_rel_ci");

       gr.addQuery("child.sys_class_name", "cmdb_ci_win_server");

       gr.addQuery("parent.sys_class_name", "cmdb_ci_service");

       gr.query();

       while (gr.next()) {

               linked.push(gr.getValue("child"));

       }

       linked = au.unique(linked);

       var gr2 = new GlideRecord("cmdb_ci_win_server");

       gr2.addQuery("sys_id", "NOT IN", linked);

       gr2.query();

       while (gr2.next()) {

               notlinked.push(gr2.getValue("sys_id"));

       }

       return notlinked;

}

[Rick Mann]

Drew

Very handy and thanks for the insight.   This works very well and provides the data we need.

Rick

[dipam3]

Hi Rick,

Did this trick work for you? If yes, what script did you write. Please help me here as I have sort of same requirement.

Regards,

Dipam

[dipam3]

Hi Rick,

Did this trick work for you? If yes, what script did you write. Please help me here as I have sort of same requirement.

Regards,

Dipam