ServiceNow plugin of large number of relationships

Started by Manj75, September 18, 2019, 10:44:07 AM

Previous topic - Next topic

Manj75

Hi Herve,

I have been using your plugin with great success and want to be able to dive in little deeper.  Our ServiceNow CMDB CI Relationships -> ALL comprises of 840610 discovered relationships, and the Service Delivery team have a user preference module that shows a filtered validated subset, i.e. called 'Snow Checksum is not empty', returning 10264 valid relationships.

My question is whether it is possible to generate the REST request URL to specify the user preference module so that 10264 are returned not the 840610.  The URL would need to comprise of parameter:

?sysparm_userpref_module=c90fd64737ed0f04b9fa0c5754990ebd

Where I presume c90fd64737ed0f04b9fa0c5754990ebd is the sys_id of the module.

Is this at all possible to specify this in the ini file?

This issue is that ALL relationships including non-validated ones are close to 1M and it takes far took long to parse, and I am also facing an exception, which occurs during the get process, but I'm not sure if this after all the relationships have been fetched and the plugin starts to parse the response:

org.codehaus.jackson,.JsonParsException: Unexpected character (',' (code 44)): was expecting a colon to separate the field name and value

I understand the JSON parse error the issue is I cannot easily diagnose as I don't know if it is an error of the REST get request or part of the response parse in which case it's not easy to identify which CI record the issue relates to.  Not easy to identify from the log file.

Any help you can provide is greatly appreciated.

Thanks,
Manjit

Manj75

#1
Investigating a little more it seems that I need to add a query parameter to the URL that is generated and not sure if there is a way to do this in the ini file.

sysparm_query=x_snsab_snow_sam_i_snow_checksumISNOTEMPTY

Below is a snippet from the log when limit is set to 850000 CI records to retrieve:

2019-09-18 12:58:32 DEBUG  719:MyImporter           -    Generated URL is https://nextdev.service-now.com/api/now/table/cmdb_rel_ci?sysparm_limit=850000&sysparm_fields=operational_status,sys_id,parent,type,sys_class_name,child&sysparm_query=&sysparm_exclude_reference_link=false
2019-09-18 12:58:35 TRACE   71:MyConnection         -       Getting relations from ServiceNow webservice ...
2019-09-18 12:59:33 TRACE   87:MyConnection         -       Read 65055088 bytes from ServiceNow webservice.
2019-09-18 12:59:34 FATAL   16:MyPopup              - Cannot get relations from ServiceNow web service: org.codehaus.jackson.JsonParseException: Unexpected character (']' (code 93)): expected a value  at [Source: java.io.StringReader@624fedfd; line: 1, column: 65054829]
2019-09-18 13:03:01 INFO   916:MyImporter           - 0 relations have been modified: 0 created, 0 updated, 0 removed.

Hervé

Hi Manjit,

While getting elements, it is possible to specify a "filter" property which is added to the "sysparm_query" part of the request.

Unfortunately, there is no such "filter" property for relationships so with the actual version of the plugin, I do not see how you could do it.

Please create an issue to my plugin's GitHub and I will study a way to add it to the next release of the plugin.

Best regards
Hervé

Manj75

No problem, I'll raise an issue to request this feature.

Separately, can you provide a response to the actual exception occurring over the completed set of relationships.  Our CMDB has in the order of 840000 relations and I set the limit to 850000 and it is throwing a Java exception detail in previous post.

I've looked at the plugin code and I suspect that it is in the JSON parse that it is being thrown due to malformed syntax.  Do you think this is a bug in the ServiceNow REST API or by the the CI data in the CMDB?  It would be nice if for any thrown exception the was JSON is logged for the CI record that caused it.

Thanks,
Manjit


Hervé

I tested several times my plugin against my ServiceNow instance and did not obtain any error message. I never had a parser error but your JSON is 62 MB long so the error may be linked to the limit of the parser.

What I would suggest is to retrieve the JSON manually (copying the URL in the log file in an Explorer) and save it to disk. Then, using a text editor (like notepad++ or likewise), go to column 65054829 and verify the object that causes the error.

Manj75

Hi Herve,

I've tested v1.7 and the filter is great but still getting the exception in Json parser.  I've now pin pointed it to a particular relationship type that is causing it, which is 'Contains::Contained by'.  What is strange is that with limit set to 150000 and running consecutive imports for each run the column it is reporting the error on is different for each run.

It would be really useful if the bytes read by the MyConnection, i.e. REST response is flushed to a message log file annotated with the corresponding generated URL.  This will really help to further diagnose the issue as it is a moving target.  Seems to me that our SN server is returning non-compliant JSON at times, but need to see the response.  Running the URL in a browser doesn't help as I want to be able to cross-reference the column error with the raw JSON response in context to an import process.

Any help is greatly appreciated.

Here is the snippet of a recent stracktrace:

2019-09-23 14:19:12 DEBUG  762:MyImporter           -    Generated URL is https://nextdev.service-now.com/api/now/table/cmdb_rel_ci?sysparm_limit=150000&sysparm_fields=operational_status,sys_id,connection_strength,parent,type,sys_class_name,child&sysparm_query=typeIN*,55c95bf6c0a8010e0118ec7056ebc54d&sysparm_exclude_reference_link=false
2019-09-23 14:19:13 TRACE   70:MyConnection         -       Getting relations from ServiceNow webservice ...
2019-09-23 14:20:13 TRACE   86:MyConnection         -       Read 64770101 bytes from ServiceNow webservice.
2019-09-23 14:20:14 FATAL   16:MyPopup              - Cannot get relations from ServiceNow web service:
org.codehaus.jackson.JsonParseException: Unexpected character (']' (code 93)): expected a value
at [Source: java.io.StringReader@416ea069; line: 1, column: 64769842]
   at org.codehaus.jackson.JsonParser._constructError(JsonParser.java:1433)
   at org.codehaus.jackson.impl.JsonParserMinimalBase._reportError(JsonParserMinimalBase.java:521)
   at org.codehaus.jackson.impl.JsonParserMinimalBase._reportUnexpectedChar(JsonParserMinimalBase.java:442)
   at org.codehaus.jackson.impl.ReaderBasedParser.nextToken(ReaderBasedParser.java:453)
   at org.archicontribs.servicenow.MyImporter.doImport(MyImporter.java:787)
   at com.archimatetool.editor.actions.ImportIntoModelAction.run(ImportIntoModelAction.java:37)
   at org.eclipse.jface.action.Action.runWithEvent(Action.java:474)
   at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:568)
   at org.eclipse.jface.action.ActionContributionItem.lambda$4(ActionContributionItem.java:400)
   at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
   at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4131)
   at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1055)
   at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3944)
   at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3547)
   at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1173)
   at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
   at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1062)
   at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
   at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:644)
   at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
   at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:566)
   at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)
   at com.archimatetool.editor.Application.start(Application.java:84)
   at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
   at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:137)
   at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:107)
   at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:400)
   at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:661)
   at org.eclipse.equinox.launcher.Main.basicRun(Main.java:597)
   at org.eclipse.equinox.launcher.Main.run(Main.java:1476)

Hervé

Hi,

As stated in the GitHub issue, I cannot do more with the current JSON library I'm using. The only information I get from the Exception is the column number and cannot know where the corresponding JSON object starts and stops.

In your case
Quote2019-09-23 14:19:12 DEBUG  762:MyImporter           -    Generated URL is https://nextdev.service-now.com/api/now/table/cmdb_rel_ci?sysparm_limit=150000&sysparm_fields=operational_status,sys_id,connection_strength,parent,type,sys_class_name,child&sysparm_query=typeIN*,55c95bf6c0a8010e0118ec7056ebc54d&sysparm_exclude_reference_link=false
...
org.codehaus.jackson.JsonParseException: Unexpected character (']' (code 93)): expected a value
at [Source: java.io.StringReader@416ea069; line: 1, column: 64769842]
The best way to debug it is to run the web service manually, save it to a text file, and use a text editor to go to column 64769842.

What I need to do to show a more valuable error message is to switch to another JSON parser but this would mean that I need to rewrite most of my code ... which is planned at some stage ... but I have few things to finish on my database pluging before going into this.

Best regards
Hervé

Manj75

Having done more investigation it turns out that a single relationship type, 'Contains::Contained by', is causing my issue due to the high number of relationships of this type, and this type alone is hitting a limit of the Jackson JSON parser.  I've also tried the URL in Chrome browser and it just stops after a while with incomplete JSON, not sure if there is a limit in Chrome, but suspect this is similar problem.  In any case this does not help as the exception is not always at the same column.

The only thing I can think of to resolve this is if there is a way to make the request in batches - do you know if this is possible in SN?

e.g. if the limit is set to 1,000,000 then also have a config of fetchInBatchesOf = 100,000, the code then makes 10 cyclic requests to retrieve all 1M.

Kind Regards,
Manjit

Hervé

It is possible to get partial response from ServiceNow using sysparm_offset and sysparm_limit.

Nevertheless, the default limit for the JSON body is set to 10 MB in ServiceNow, so I cannot believe you go beyond this limit.

Hervé

Hi Manjit,

I won't develop the batch mode because I lack time to do so, but I just released a small update that allows you to specify complex filters.

So you can do your import in several parts.

Let say you configure the sysparm_limit to 1000:
Quoteservicenow.sysparm_limit = 1000

Then you must run the first import with
Quotearchi.relations.*.filter = x_snsab_snow_sam_i_snow_checksumISNOTEMPTY&sysparm_offset=0

Then the second import with
Quotearchi.relations.*.filter = x_snsab_snow_sam_i_snow_checksumISNOTEMPTY&sysparm_offset=1000

Then the third import with
Quotearchi.relations.*.filter = x_snsab_snow_sam_i_snow_checksumISNOTEMPTY&sysparm_offset=2000

and so on.

Hope this helps

Best regards
Hervé

Manj75

Hi Herve,

Your suggestion does not work because the filter value is also being set to the &sysparm_fields in the URL and this causes a HTTP 500 error.  Taking the generated URL from the log and running in the browser I get the following:

<response>
  <error>
    <detail>
      For input string: "0,type,sys_class_name,child" Check logs for error trace or enable glide.rest.debug property to
      verify REST request processing
    </detail>
    <message>
      java.lang.NumberFormatException: For input string: "0,type,sys_class_name,child"
    </message>
  </error>
  <status>failure</status>
</response>

URL generated is:

https://nextdev.service-now.com/api/now/table/cmdb_rel_ci?sysparm_limit=5&sysparm_fields=operational_status,sys_id,connection_strength,parent,&sysparm_offset=0,type,sys_class_name,child&sysparm_query=typeIN*,55c95bf6c0a8010e0118ec7056ebc54d%5E&sysparm_offset=0&sysparm_exclude_reference_link=false

Seems this is a bug in that the filter is also being added as a sysparm_fields to return and this causes the exception.  I removed the filter value from the fields and then it works as expected, i.e. using following URL:

https://nextdev.service-now.com/api/now/table/cmdb_rel_ci?sysparm_limit=5&sysparm_fields=operational_status,sys_id,connection_strength,parent,type,sys_class_name,child&sysparm_query=typeIN*,55c95bf6c0a8010e0118ec7056ebc54d%5E&sysparm_offset=0&sysparm_exclude_reference_link=false

Can this be resolved - let me know if you want me to raise an issue.

Btw - I did not need the x_snsab_snow_sam_i_snow_checksumISNOTEMPTY filter as it was deemed unreliable as a number of populated checksums were nullified.

Thanks,
Manjit

Hervé

That's why I released the version 1.7.1 which removes the filter from the sysparm_fields :-)

Manj75


Manj75

Hi Herve,

I've now tried with v1.7.1 and able to import 265K "Contains:Contained by" relationships in 3 batches of 100K.

One suggestion I'd like to make is to be able to specify the multiple batches in the same ini config file so that the import will execute all batches based on the instances/offset for the type defined.  I had tried this but it does not work as it only utilises the first defined properties of the ini file to execute the import.  With this approach I am having to run first batch then modify the offset run a second batch and so on (as per your instruction).

Also, I'm not sure if the filter property can be overridden the the top-level '*'.

Ideally, I want to have all import setup in a single in file and be scheduled to be run using Archi CLI.  I now you are busy but if you can consider this enhancement on you backlog on change that would be great.

To give you and example - in a single ini file define:

archi.relations.*.id                                                        = sys_id
archi.relations.*.name                                                   = type#name
archi.relations.*.source                                                 = child
archi.relations.*.target                                                  = parent
archi.relations.*.folder                                                   = ${class}
archi.relations.*.property.strength                                  = connection_strength
archi.relations.*.property.source_action                          = type#child_descriptor
archi.relations.*.property.target_action                           = type#parent_descriptor
#archi.relations.*.filter                                                    = &sysparm_offset=100000

# "Contains::Contained by" Batch 1
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.archi_class                 = "CompositionRelationship"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.folder                         = "Contains::Contained by"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.name                         = "Contains"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.source                        = parent
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.target                         = child
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.property.type_name     = type#name
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.property.source_action = type#parent_descriptor
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.property.target_action  = type#child_descriptor
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.filter                            = &sysparm_offset=0

# "Contains::Contained by" Batch 2
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.archi_class                 = "CompositionRelationship"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.folder                         = "Contains::Contained by"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.name                         = "Contains"
...etc...
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.filter                            = &sysparm_offset=100000

# "Contains::Contained by" Batch 2
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.archi_class                 = "CompositionRelationship"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.folder                         = "Contains::Contained by"
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.name                         = "Contains"
...etc...
archi.relations.55c95bf6c0a8010e0118ec7056ebc54d.filter                            = &sysparm_offset=200000

Thanks,
Manjit

Hervé

Hi Manjit,

In fact, the ServiceNow plugin is the first plugin I wrote, so I had to learn the Java language, the Eclipse framework and the Archi internal classes, all in one time when I started to develop it.

That's why it is quite very simple (in the sense that it does not allow a lot of options) and has not got a graphical interface.

I've got in my roadmap a complete rewrite of the plugin with a proper graphical interface that will allow to parse the ServiceNow structure and pickup the requested information without the need of editing any INI file.

But I also have got roadmaps for all my other plugin and haven't got much time at the moment, so unfortunately this rewrite won't arrive very soon.

Best regards
Hervé