View difference between Paste ID: d3bRyuLu and TdJTt7g9

 /*
-Name: Create Database Virtual Server
+Name: Create MSSQL Connection Relationship
-Table: Windows Cluster Resource
+Table: TCP Connection
 Order: 100
 When: after
 Insert:	true
 Update:	true
-Condition: current.resource_type == 'IP Address'
+Condition: current.to_port == 1433
 */
 
-if(current.name.indexOf('SQL IP Address') == 0) {
+createMSSQLRel();
-   createDBVirtualServer(current);
+
 function createMSSQLRel() {
    //Check for matching MSSQL Instances by IP
-function createDBVirtualServer(clusterRes) {
+   var MSSQLInst = new GlideRecord("cmdb_ci_db_mssql_instance");
-   var clusterVIP = new GlideRecord('cmdb_ci_cluster_vip');
+   MSSQLInst.addQuery("ip_address", current.to_ip);
-   clusterVIP.addQuery('name', clusterRes.name);
+   MSSQLInst.query();
-   clusterVIP.addQuery('cluster', clusterRes.cluster);
+   if(MSSQLInst.next()) {
-   clusterVIP.query();
+      //If not set to absent or if absent for less than 30 days, create new. Otherwise delete relationship
-   while(clusterVIP.next()) {
+      if(current.absent == false || (current.absent == true && current.sys_updated_on < gs.daysAgo(-30))){
-      //gs.log(clusterVIP.name + '/' + clusterVIP.ip_address + '/' + clusterVIP.cluster.name, 'eric');
+         createRelationship(current.computer.sys_id, MSSQLInst.sys_id);
-      var VIPServer = clusterVIP.name.split('(')[1].replace(')', '');//isolate the virtual server name
+      }else{
-      //Create MSSQL Database Virtual Server
+         deleteRelationship(current.computer.sys_id, MSSQLInst.sys_id);
-      var DBVirtualServer = new GlideRecord('cmdb_ci_database');
+
-      DBVirtualServer.addQuery('name', VIPServer);
+
-      DBVirtualServer.query();
+
-      if(!DBVirtualServer.next()) {
+
-         DBVirtualServer.initialize();
+function createRelationship(source,dest) {
-         DBVirtualServer.name = VIPServer;
+   var computer = new GlideRecord("cmdb_ci_computer");
-         DBVirtualServer.ip_address = clusterVIP.ip_address;
+   computer.addQuery("sys_id", source);
-         DBVirtualServer.type = 'Microsoft SQL Server';
+   computer.query();
-         DBVirtualServer.u_substatus = 'active';
+   if(computer.next()) {
-         DBVirtualServer.short_description = gs.nowDateTime() + ' - Auto created from cluster SQL IP Address resource';
+      var rel1 = new GlideRecord('cmdb_rel_ci');//check for existing relationships
-         DBVirtualServer.insert();
+      rel1.addQuery('child.sys_id', source);
       rel1.addQuery('parent.sys_id', dest);
-      //create MSSQL instance
+      rel1.query();
-      var DBInstance = new GlideRecord('cmdb_ci_db_mssql_instance');
+      if(!rel1.next()) {
-      DBInstance.addQuery('name', 'MSSQLSERVER@' + VIPServer + '(Microsoft SQL Server)');
+         var newRel = new GlideRecord('cmdb_rel_ci');//create relationship with type: Serves MSSQL Data::Queries MSSQL Data
-      DBInstance.query();
+         newRel.initalize();
-      if(!DBInstance.next()) {
+         newRel.parent = dest;
-         DBInstance.initialize();
+         newRel.child = source;
-         DBInstance.name = 'MSSQLSERVER@' + VIPServer + '(Microsoft SQL Server)';
+         newRel.type = 'baa92dcb74906000ce885c6f8d102bf4';
-         DBInstance.ip_address = clusterVIP.ip_address;
+         newRel.insert();
-         DBInstance.u_substatus = 'active';
+
-         DBInstance.short_description = gs.nowDateTime() + ' - Auto created from cluster SQL IP Address resource';
+
-         DBInstance.install_date = gs.nowDateTime();
+
-         DBInstance.insert();
+
 function deleteRelationship(source,dest) {
-      //Create DB Virtual Server -> Cluster relationship
+   var computer = new GlideRecord("cmdb_ci_computer");
-      var clusterRel = new GlideRecord('cmdb_rel_ci');//create relationship with type: Runs on::Runs
+   computer.addQuery("sys_id", source);
-      clusterRel.addQuery('parent', clusterVIP.cluster.sys_id);
+   computer.query();
-      clusterRel.addQuery('child', DBVirtualServer.sys_id);
+   if(computer.next()) {
-      clusterRel.addQuery('type', '60bc4e22c0a8010e01f074cbe6bd73c3');
+      var rel1 = new GlideRecord('cmdb_rel_ci');//check for existing relationships
-      clusterRel.query();
+      rel1.addQuery('child.sys_id', source);
-      if(!clusterRel.next()){
+      rel1.addQuery('parent.sys_id', dest);
-         clusterRel.initalize();
+      rel1.addQuery('type', 'baa92dcb74906000ce885c6f8d102bf4')
-         clusterRel.parent = clusterVIP.cluster.sys_id;
+      rel1.addQuery('sys_updated_on', '>', gs.daysAgo(-30))//if over 30 days old
-         clusterRel.child = DBVirtualServer.sys_id;
+      rel1.query();
-         clusterRel.type = '60bc4e22c0a8010e01f074cbe6bd73c3';
+      if(rel1.next()) {
-         clusterRel.insert();
+         rel1.deleteRecord();
       }
-
+
-      //Create DB Virtual Server -> MSSQL instance relationship
+