Retrieve VM details from Azure using REST API integration hub ETL and load to CMDB

[VaranAwesomenow]

This article talks about how to fetch azure vm details and load them into ServiceNow CMDB table cmdb_ci_vm_instance

A. Register an application - Azure

1. Open Azure CLI
2. az account set --subscription <subscription-id>

3. az ad sp create-for-rbac
{
"appId": "XXXXX",
"displayName": "XXXXX",
"name": "XXXXX",
"password": "XXXXX",
"tenant": "XXXXX"
}

4. az account show --query id -o tsv

5. Create Outbound REST Message  - ServiceNow

Name : AzureEndPoint

Endpoint : https://management.azure.com

Authentication : No Authentication

6. Create Post HTTP Method  - ServiceNow

Name = GetToken

EndPoint = https://login.microsoftonline.com/${tenantid}/oauth2/token

Authentication type = No Authentication

Content = client_id=<appId from step # 3>&client_secret=<password from step # 3>&grant_type=client_credentials&resource=https://management.azure.com

Variable substitutions 

Name = tenantid 

Test Value =  tenant id (captured in above steps # 3)

Save the record.

7. Get OAUTH Token - ServiceNow

Click on test to get the authentication token from Azure.

Test run should look like below

HTTP Status = 200

Response = {"token_type":"Bearer","expires_in":"3599","ext_expires_in":"3599","expires_on":"1617300913","not_before":"1617297013","resource":"https://management.azure.com","access_token":"XXXXXXXXXX"}

B. Plugin installation - ServiceNow

1. Install plugin = com.glide.integration_studio
Request from store or from developer.servicenow.com (if its PDI) https://store.servicenow.com/sn_appstore_store.do#!/store/application/f1a5a5b60f94001067ae409dc4767e08
2. install plugin = sn_cmdb_int_util
2.5.0
Request from store  or from developer.servicenow.com (if its PDI)
https://store.servicenow.com/$appstore.do?ref=appmgmt&instanceid=d6a466f5db4284900ae3ac44d496197f#!/store/application/d43fe173dba23300c121f3c61d961958/2.0.1?referer=%2Fstore%2Fsearch%3Flistingtype%3Dallintegrations%25253Bancillary_app%25253Bcertified_apps%25253Bcontent%25253Bindustry_solution%25253Boem%25253Butility%26q%3Detl&sl=

3. Install plugin = sn_int_studio
2.0.1
Dont load demo data in production

C. Integration Hub ETL configuration - ServiceNow

1. Create Data source of type REST (Integration hub)

2. Integration Hub action

    Preprocessing script to fetch api token

(function execute(inputs, outputs) {
// ... code ...

var tenantId = 'XXXXXXXXXXXXXXXX';
var AzrDetails = getazureResource(tenantId);
var issueJson = JSON.parse(AzrDetails);
var issueDescString = JSON.stringify(issueJson);
gs.print("issueDescString=" + issueDescString);
var accessToken = issueJson.access_token + '';
gs.log(accessToken, 'azurerest');
outputs.apikey = 'Bearer ' + accessToken;
outputs.apiversion = '2020-12-01';
function getazureResource(tenantId) {
try {
var r = new sn_ws.RESTMessageV2('AzureSponsorEndPoint', 'GetToken');
r.setStringParameterNoEscape('tenantid', tenantId);
r.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
r.setRequestBody("client_id=XXXXXXXXXX&client_secret=XXXXXXXX &grant_type=client_credentials&resource=https://management.azure.com");

//override authentication profile
//authentication type ='basic'/ 'oauth2'
//r.setAuthenticationProfile(authentication type, profile name);

//set a MID server name if one wants to run the message on MID
//r.setMIDServer('MY_MID_SERVER');

//if the message is configured to communicate through ECC queue, either
//by setting a MID server or calling executeAsync, one needs to set skip_sensor
//to true. Otherwise, one may get an intermittent error that the response body is null
//r.setEccParameter('skip_sensor', true);

var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
}
catch(ex) {
var message = ex.message;
}
return responseBody;
}
})(inputs, outputs);

 REST API step

Define connection lnline.

Base url = https://management.azure.com/
Resource path = subscriptions/<subscription_id>/resourceGroups/varan2_rg/providers/Microsoft.Compute/virtualMachines/test1234

Query parameters
api-version

Headers
Authorization

2. Follow guided setup of ETL and map the attributes from Azure REST API JSON response to ServiceNow CMDB class = cmdb_ci_vm_instance, cmdb_ci_logical_datacenter

CMDB relationships = Virtual machine instance to logical data center

 Video links

Get OAUTH token - https://youtu.be/6pbLcK1a_xc

Plugin installation - https://youtu.be/AyT-ryWEqpE

Data source setup - https://youtu.be/KHMZymEfXEQ

Integration hub ETL setup - https://youtu.be/o_If_l1JDsM

References : https://blog.jongallant.com/2021/02/azure-rest-apis-postman-2021/

[Ram Devanathan1]

or you could run Discovery pattern to do the same.

still, nice work - good idea to document this.

it would be prudent not to use this to replace fully provided capabilities like ITOM Discovery as this becomes support issue for future, and impacts ability of organizations to adopt service mapping, csdm, etc.

[VaranAwesomenow]

Ty Ram

[Belal Salas1]

Hi Anil,

What is the details that can be retrieved by this method? Can it replace the IP discovery using MID server completely?

Thanks

[SnowUser09]

tried this but it is not uploading data in import set, as per other developers this scripts looks incomplete, can anyone please check and suggest.

[VaranAwesomenow]

@SnowUser09 hey, which part of script didnt work, may be I can take a look, you can also go through video links that demo the feature to understand the solution better.

[SnowUser09]

@VaranAwesomenow  Hello , thanks for replying. We have gone through all the videos and able to set up connection with Azure, fetched data from azure (tested in notepad as shown in the videos) but that data was not loading up in Import set/staging table.  We followed all the steps mentioned in the videos but could not move ahead of import set table. Due to this we were unable to complete last step of Integration Hub.

[VaranAwesomenow]

@SnowUser09 -> do you see any errors in the import log against that import set table.

[SnowUser09]

@VaranAwesomenow 

Error from Import set is :

Loading import set table with error: u_azurevm

com.glide.db.impex.json.JSONStreamLoader: com.glide.db.impex.datasource.DataSourceException: java.lang.Exception: Can not map the element 'doc' refers from / to a table row. Path should always refer JSON Objects.: com.glide.db.impex.json.JSONLoader.initParser(JSONLoader.java:109)
com.glide.db.impex.json.JSONStreamLoader.preLoad(JSONStreamLoader.java:18)
com.glide.db.impex.AbstractLoader.createColumnAttributes(AbstractLoader.java:569)
com.glide.db.impex.AbstractLoader.createTableAndMap(AbstractLoader.java:324)
com.glide.db.impex.AbstractLoader.createTableForImport(AbstractLoader.java:296)
com.glide.db.impex.AbstractLoader.load(AbstractLoader.java:184)
com.glide.db.impex.AbstractLoader.startWork(AbstractLoader.java:166)
com.glide.worker.AbstractProgressWorker.startAndWait(AbstractProgressWorker.java:126)
com.glide.worker.HierarchicalProgressWorker.startAndWait(HierarchicalProgressWorker.java:26)
com.glide.worker.AbstractProgressWorker.start(AbstractProgressWorker.java:101)
com.glide.system_import_set.ImportSetLoader.startWorker(ImportSetLoader.java:141)
com.glide.system_import_set.ImportSetLoader.startLoader(ImportSetLoader.java:130)
com.glide.system_import_set.ImportSetLoader.loadImportSetTable(ImportSetLoader.java:76)
com.glide.system_import_set.ImportSetLoader.loadImportSetTable(ImportSetLoader.java:57)
com.glide.integration_studio.IntegrationStudioScriptableApi.jsStaticFunction_getSampleData(IntegrationStudioScriptableApi.java:192)
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base/java.lang.reflect.Method.invoke(Method.java:566)
org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)
org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:677)
org.mozilla.javascript.FunctionObject.call(FunctionObject.java:614)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2649)
org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1518)
org.mozilla.javascript.Interpreter.interpret(Interpreter.java:830)
org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:160)
com.glide.caller.gen.sys_ws_operation_ff3225ee7394001024e5aa114df6a76a_operation_script.call(Unknown Source)
com.glide.script.ScriptCaller.call(ScriptCaller.java:18)
org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:159)
org.mozilla.javascript.ScriptRuntime.doCall2(ScriptRuntime.java:2734)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2657)
org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1518)
org.mozilla.javascript.Interpreter.interpret(Interpreter.java:830)
org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:160)
com.glide.caller.gen.sys_ws_operation_ff3225ee7394001024e5aa114df6a76a_operation_script.call(Unknown Source)
com.glide.script.ScriptCaller.call(ScriptCaller.java:18)
org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:159)
org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:597)
org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3573)
org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:172)
com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:392)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:204)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:133)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:348)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:240)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:219)
com.glide.rest.service.custom.CustomService.runScript(CustomService.java:90)
com.glide.rest.service.custom.CustomService.execute(CustomService.java:77)
com.glide.rest.handler.impl.ServiceHandlerImpl.invokeService(ServiceHandlerImpl.java:37)
com.glide.rest.processors.RESTAPIProcessor.process(RESTAPIProcessor.java:336)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:625)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:277)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:184)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:172)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:51)
com.glide.sys.Transaction.run(Transaction.java:2501)
com.glide.ui.HTTPTransaction.run(HTTPTransaction.java:27)
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
java.base/java.lang.Thread.run(Thread.java:829)

[VaranAwesomenow]

based on error its unable to parse the output, can you check if you are able to get data from azure you can test it using the flow action and check for creation of attachment.

If attachment is getting created then it appears to be parsing issue in data source for that you might want to check the field

path for each row on data source and see if it meets the attachment JSON format.

[SnowUser09]

@VaranAwesomenow Yes we are getting attachment and data is appearing in that attachment.  How can we check the field path for each row. Currently in path as shown in your video , we have updated "/" in data source

[abrouf]

Hello Varan,

The steps 1-7 for getting OAUTH token can be replaced by "Set up Microsoft Azure SQL Database spoke", correct?

https://docs.servicenow.com/bundle/tokyo-application-development/page/administer/integrationhub-stor...

Please let us know your feedback.

Thank you.

abrouf

[SayaliK]

@VaranAwesomenow     

Got a authentication error 

with code: 403 - Forbidden username/password combo

Could you please elaborate a step you followed while creating azure account and hierarchy for resource groups and virtual machines?