Hello Community,
Lately I had a client asking to do an integration of their ServiceNow CMDB with LanSweeper. They would like to populate their CMDB from LanSweeper and at the same time update specific fields of an Asset in LanSweeper from the corresponding CI in ServiceNow.
I was not able to find any articles about a similar requirement so I decided to write about it and explain the steps on how I have done it.
The requirement: Was to import specific classes, i.e. Computer Class, Printer, Software from LanSweeper into ServiceNow's CMDB into their corresponding Classes. But at the same reconcile between 2 systems by allowing ServiceNow to update specific parameters of that CI in LanSweeper.
I will take the Computer class [cmdb_ci_computer] as an example.
LanSweeper is an MS SQL database so the connection to it is pretty straight forward, I have used a JDBC connection for my outbound from ServiceNow to LanSweeper and a Data source of type / Format : SQLServer for inbound into ServiceNow.
There is an interesting article by John Andersen on how to use JDBC probe in ServiceNow.
1. First you will need to activate the JDBC probe plug-in (com.snc.integration.jdbc) on your instance, this will allow you to run the JDBC api's.
2. Next you will need to go on LanSweeper and create a Report of what Data you want to import with their fields into ServiceNow. There are alot of Out-of-the-box Report examples in LanSweeper you can use, you can also generate your own or refer to the following Lansweeper forum, it is very resourceful. When you create the report and generate it in LanSweeper export into Excel to verify the information.
What's interesting is that you can name the fields to your choosing to simplify the Transform Map process.
Here is the query of the Report for Windows Computer I have used
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetName As name,
tblAssets.AssetID As LanSweeper_AssetId,
tblNetwork.Description As [Network Card],
tblNetwork.MACaddress As mac_address,
tblNetwork.IPAddress As netWork_IP,
tblAssets.IPAddress As PC_ip_address,
tblAssets.Domain As os_domain,
tblAssetCustom.Custom1 As asset_tag,
tblAssetCustom.Serialnumber As serial_number,
tblAssetCustom.PurchaseDate As purchase_date,
tblAssets.NrProcessors As cpu_count,
tblAssets.Processor As cpu_type,
tblAssets.Memory As ram,
tblAssets.Description As short_description,
tblAssets.FQDN As fqdn,
tblAssetCustom.Model As model_id,
tblAssets.Username As last_user,
tblAssetCustom.State As operational_status,
tblAssetCustom.Warrantydate As warranty_date,
tblAssetCustom.Manufacturer,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblOperatingsystem.Caption As Operating_system,
tblOperatingsystem.Version As os_version,
tblOperatingsystem.ServicePackMajorVersion As os_servicepack,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.IPAddress As IPAddress1
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblNetwork.IPEnabled = 1
Order By name3. In LanSweeper once you generate the report, export it to Excel
4. Go into ServiceNow and load the data Excel Sheet and create a Transform Map, this is to make sure you have an idea of what the transformation will look like upon import and that you are getting the expected result.
5. Create a Data source of type SQLServer. in the SQL Statement paste the query of your LanSweeper Report (see step 2)
6. For that Data Source in the Transforms related list select the Tramsform Map(s) you created. I have more than one as I am updating the Network Adapters and IP Address Table with the PC's info, note: the Order you set for the Transform Maps.
7. Next set a scheduled Job choice to run the import to your preference, the import will respect the Transformation you set in your Transform Map.
8. Next we want ServiceNow to Update the CI in LanSweeper, with specific parameters, in this example we want to update the Location and Asset Tag in LanSweeper.
9. Create a Script Include
Name: LanSweeperUtil
Client Callable: True
var LanSweeperUtil = Class.create();
LanSweeperUtil.prototype = Object.extendsObject(AbstractAjaxProcessor, {
updateLanSweeperAssetTag: function(assetTag, serialNumber, Location) {
var propertybag = "lansweeper.connect.";
var midServer = gs.getProperty(propertybag + "midsever");
var driver = gs.getProperty(propertybag + "driver");
var connectionstring = gs.getProperty(propertybag + "connectionstring");
var table = gs.getProperty(propertybag + "asset.table");
var tableField = gs.getProperty(propertybag + "asset.field");
var locationField = gs.getProperty(propertybag + "asset.location");
var dbUniqueField = gs.getProperty(propertybag + "primary.key");
var userName = gs.getProperty(propertybag + "username");
//var password = gs.getProperty(propertybag + "password");
var sourceField = gs.getProperty(propertybag + "source.field");
// password encryption for the ECC queue
var automation_api = new sn_automation.AutomationAPI();
var password = gs.getProperty(propertybag + "password");
var encrypted_password;
if (automation_api.isEncrypted(password))
encrypted_password = password;
else
encrypted_password = automation_api.encrypt(password);
//End of API encryption
var lansweeper = new JDBCProbe(midServer); //check for MID Server
lansweeper.setDriver(driver);
lansweeper.setConnectionString(connectionstring + ";user=" + userName + ";password=" + encrypted_password);
lansweeper.setTable(table);
lansweeper.setFunction("update");
lansweeper.setName("ServiceNowLanSweeperUpdate"); //provide source of JDBC Payload
lansweeper.addField(tableField, assetTag); // Asset Tag updated in LanSweeper
lansweeper.addField(locationField, Location); // Location updated in LanSweeper
lansweeper.setWhereClause(dbUniqueField + "='" + serialNumber + "'");
lansweeper.create();
},
updateLanSweeperProperties: function(midServer, driver, connectionstring, username, password, table, assetfield, primarykey, sourcefield, sourcelocation, assetlocation, asseturl) {
var sysProperties = new GlideRecord('sys_properties');
sysProperties.addEncodedQuery("nameSTARTSWITHlansweeper.connect");
sysProperties.orderBy('name');
sysProperties.query();
while (sysProperties.next()) {
if (sysProperties.name == 'lansweeper.connect.midsever') {
sysProperties.setValue('value', midServer);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.driver') {
sysProperties.setValue('value', driver);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.connectionstring') {
sysProperties.setValue('value', connectionstring);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.username') {
sysProperties.setValue('value', username);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.password') {
sysProperties.setValue('value', password);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.asset.table') {
sysProperties.setValue('value', table);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.asset.field') {
sysProperties.setValue('value', assetfield);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.primary.key') {
sysProperties.setValue('value', primarykey);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.source.field') {
sysProperties.setValue('value', sourcefield);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.source.location') {
sysProperties.setValue('value', sourcelocation);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.asset.location') {
sysProperties.setValue('value', assetlocation);
sysProperties.update();
}
if (sysProperties.name == 'lansweeper.connect.asset.url') {
sysProperties.setValue('value', asseturl);
sysProperties.update();
}
}
gs.addInfoMessage(gs.getMessage('lansweeper_properties_update_confirmation'));
},
updateLanSweeperURL: function(grAsset, ITAM) {
var propertybag = "lansweeper.connect.";
var assetUrl = gs.getProperty(propertybag + "asset.url");
grAsset.setValue('u_lansweeper_url', assetUrl + ITAM);
grAsset.update();
},
insertLanSweeperURL: function(grAsset, ITAM) {
if (ITAM == '') {
return "";
} else {
var propertybag = "lansweeper.connect.";
var assetUrl = gs.getProperty(propertybag + "asset.url");
return assetUrl + ITAM;
}
},
type: 'LanSweeperUtil'
});
10. Create the system properties for your parameters used in the Script Include, they are the paramaters of your SQLServer Data source. In my case I set them all to start with "lansweeper.connect." and for the password property I set it as type Password.
11. Create a Business Rule on the Computer table (or cmdb_ci table) for the classes you wish to run the update
Table: cmdb_ci
WHen: on Insert and on Update
(function executeRule(current, previous /*null when async*/) {
// THe following Business Rule will update the Asset Tag in LanSweeper
updateLanSweeperAssetTag();
function updateLanSweeperAssetTag() {
var lansweeper = new global.LanSweeperUtil().updateLanSweeperAssetTag(current.getDisplayValue(gs.getProperty('lansweeper.connect.source.field')), current.serial_number, current.getDisplayValue(gs.getProperty('lansweeper.connect.source.location')));
}
})(current, previous);
10. Now whenever you run your business rule it will create a payload on the ECC queue of type JDBC probe and will update the corresponding CI in LanSweeper. you can also view the logs in the ECC queue.
See example we update the Location and Asset Tag of the Computer in ServiceNow and the info got updated in LanSweeper.
NOTE: we are using the Serial Number to identify the CI between both systems
You can see the logs with the Payloads in the ECC Queue
Hope this helps, feel free to share your comments or mark as helpful, Thanks.
