Updating CMDB from multiple sources

In case of CMDB update we are facing a challenge consisting in data coming from multiple sources. Below we will show how to approach this task from a practical point of view.

In our example we will work with a Server entity (Machine, OS server). We want to keep a list of all the servers in CMDB, because it is an essential configuration item. However, we will show a general approach that we can apply at any data update.

In an ideal world we would secure one file with records, import it and perform following:

• Create new records for servers that appeared in the file but were not in CMDB before.
• Update data about servers that were both in the file and in CMDB.
• Delete records of servers that are in CMDB but not in the import file.

All these three operations can be easily set up by checking corresponding options in the import definition. The import file could be obtained by using the infrastructure function OG_Servers.

In fact this scenario is usually not that simple. Servers are located in various networks to which necessary communication rules are not set up in order the infrastructure scan can be taken. We can have multiple Active Directory domains, servers in various DMZ, highly secured segments, in cloud or in networks of affiliated companies to which we do not have access at all.

In such a case we will have most probably more input files and for some servers we have to do manual data update.

Therefore we will create more imports and in each of them we will define a filter for record set that will be updated. E.g. if we know that in certain file there are all the servers from the test domain, we can set the import filter to the server property defining the domain and when assessing which servers should be deleted, only servers from this domain will be considered. Without this filter setting all other servers would be deleted (also those out of the esting domain), because they were not in the import file.

Automatic deletion of servers that were not in the import file can be an issue also because it may happen that that the file is not filled in correctly due to an error in the script or because the given server did not respond to the query asking for its configuration. In the first case we can use the import setting ensuring that the import will not be performed if the import contains a deviation from an expected record quantity. The second type of the problem can be solved by marking records that were not in the last import and stating date and time of the last import in which the server was found. In this way we can identify records which should be checked and deleted if necessary.

Another suitable element is finding out an indicator that will say whether the given server is updated manually. We will know that these servers will never appear in the imports and we will have to ensure their manual update.

Resolving issue with imports from multiple sources

We will extend the Server entity (or another entity that we want to import) with following properties:

Marking the records by the import

Records will be marked in columns present_in_last_import and last_import_datetime by an import script (tab Start after import end):

/*servers that were in the import file (matching class column name and file column Name) will be marked
in the field present_in_last_import as Yes (all other servers will be set to No). These servers from the import file will be also set in column last_import_datetime to the current date and time.*/

var cl = OG.ClassDef.GetById(OGActualImport.ClassDefId);
var colPres = cl.Columns['present_in_last_import'];
var colLast = cl.Columns['last_import_datetime'];

/* present_in_last_import = No */
var sql = 'update datarow' + cl.Id + ' set ' + colPres.DBColumnName + ' = 0' +
' where Id not in (select LastChildDataRowId from ' + OGActualImportDbTable + ' where IsError = 0)';
OG.Sql.RunSql(sql);

/* present_in_last_import = Yes, last_import_datetime = sysdate */
var sql = 'update datarow' + cl.Id +
' set ' + colPres.DBColumnName + ' = 1, ' + colLast.DBColumnName + ' = getdate() ' +
' where Id in (select LastChildDataRowId from ' + OGActualImportDbTable + ' where IsError = 0)';
OG.Sql.RunSql(sql);

Queries mappring CMDB data quality

Below two queries can be inserted into the page mapping data quality in CMDB.

Query for identification of newly created records

SELECT m.name, d.Name AS Datasource, m.created, '53-' + convert( varchar, m.id) AS FullId
FROM {{:class.machine:}} m
INNER JOIN DataSource d ON m.createddatasourceid = d.id
WHERE d.referenceid IS NOT NULL and m.status IS NULL

Query for identification of records tha should be considered for deletion

SELECT m.name, m.created, m.last_import_datetime, '53-' + convert( varchar, m.id) AS FullId
FROM {{:class.machine:}} m
WHERE (m.present_in_last_import IS NULL OR m.present_in_last_import = 0) AND (m.manual_update IS NULL OR m.manual_update = 0) AND m.deleted IS NULL

Notification about newly created servers

If there are new servers identified and corresponding records are created in CMDB, we will want to send a notification including list of servers that were created.

We will create a notification which we set for event After successful import start.

In the notification we will use following script in the tab Script.

var data = OG.GetItem('server-notif-data');
if ( OG.IsNull(data))
{
//read datasource id
var sql = OG.TextUtils.Format( "select max(id) from DataSource where ReferenceId = {0} and Name like 'Import {0}: %'", OGImport.Id);
var dsId = OG.Sql.RunScalarSql(sql);

//read from class new records - they have createddatasourceid of the current import and were created after ir.start
var drf = OG.DataRow.GetDataRowFilter(OGImport.ClassDefId);
drf['createddatasourceid'] = dsId;
drf.AddAndCon( OG.DataRow.CreateConLe(OGImport.ClassDef.Columns['created'], OGImportRun.DateTimeStart.AddMinutes(-1)));
//drf.WriteSqlToLog = true;

//go through data and create links to datarow
data = 'No new server import was identified in the import.';
var drl = OG.DataRow.GetDataByFilter(drf);
if ( drl != null && drl.Count > 0)
{
data = '';
for( var i = 0; i < drl.Count; ++i)
{
var dr = drl[i];
data += OG.TextUtils.Format( '<a href="{2}{1}">{0}</a><br />', dr.ShortDescriptionOrFullIdEnvelope, dr.DetailUrl, OG.GetWebUrl()); }
}
OG.SetItem('server-notif-data', data);
}
OGMessage.Body = OGMessage.Body.Replace( '#SERVERS#', data);

As a result there will be an email with list of servers that were newly identified in the import with a link from email to the corresponding record in CMDB.