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

[She Sull]

Drew West, thanks for sharing. This solution is awesome!

[sathish_anoor]

Drew, It is a real cool script, nice and neat.   I was wondering, if we can generalize it.   Like, call a JS with three parameters, cmdb_ci_win_server , cmdb_ci_service and "Runs::Runs On" and have 1 JavaScript to accomplish the task?   We have about 40-50 exception reports and looking for a quick solution.   I will also start looking into Scott Ferguson solution below,   but wanted to check this one out too.

[DrewW]

You can treat it like any function.   Just add the parameters and when you call it pass in the values it needs.   We do this for reports like Incidents created by users in group X.   Created a function like above, then just pass in the group name.   The function then finds all of the users in the group and passes back there sys_id's or user names in an array.

[Scott Ferguson]

Rick Mann,   there is a plugin called 'Compliance'.

Wiki --> "Compliance is a tool set that enables administrators to certify ServiceNow data for correctness and fix any discrepancies found in the data."

Basically, you create a template looking at not just attributes but   for things such as a relationship to related CIs.   Business services must use a application or a database must have a hosted on relationship.   Then use the template to audit data and identify the gaps to fix.   If you want to see it, let me know.   I can show you.

[dipam3]

Hi Scott, Please can you show me how to do this.I have same requirment where i need a report to list all servers which has no relationship with Application CI