ServiceNow / PySNC

Python API for ServiceNow
MIT License
91 stars 28 forks source link

Provide join_queries that look through cmdb_rel_ci #54

Closed arthurzenika closed 1 year ago

arthurzenika commented 1 year ago

I'm trying to extract data from two tables that are linked using cmdb_rel_ci. I was expecting to be able to add a join_query as described by https://servicenow.github.io/PySNC/user/advanced.html#join-queries but it seems that this is a functionality to query linked or dotwalked attributes (not sure how they are called in servicenow - reference field?) with join_table_field.

This is pseudo code I would like to be able to write :

gr = client.GlideRecord('cmdb_ci_server')
join_query = gr.add_join_query('cmdb_ci_ip_address', join_relationship_field='Owned by::Owns')
gr.query()

Inspired by schema https://docs.servicenow.com/en-US/bundle/tokyo-servicenow-platform/page/product/configuration-management/concept/class-server.html

That would return records of servers with their related ip_address.

Is this already possible with the existing code ?

vetsin commented 1 year ago

looking at the schema you probably want something more like

gr = client.GlideRecord('cmdb_ci_server')
gr.add_join_query('cmdb_ci_ip_address', join_relationship_field='cmdb_ci')
arthurzenika commented 1 year ago

Hey @vetsin thanks for your prompt answer !

I see in the documentation :

image

so I don't think the join_table_field would be cmdb_ci... I'm new to servicenow so maybe there's something I'm missing...

vetsin commented 1 year ago

honestly a join query is unlikely what you want here. I would recommend querying the individual tables - though I cannot help there as I'm wholly unfamiliar with CMDB. Seeing however as there is no issue with the API I'm going to close this - if there are particular data questions I suggest asking within the ServiceNow community

arthurzenika commented 1 year ago

For anyone having a similar problem I ended up solving this like this :

gr = client.GlideRecord('cmdb_rel_ci')
gr.add_query('type.name', 'Owned by::Owns')
gr.add_query('parent.sys_class_name', 'cmdb_ci_server')
gr.add_query('child.sys_class_name', 'cmdb_ci_ip_address')
gr.fields = ['sys_id', 
             'parent.name', 
             'child.name',]
gr.query()
gr.serialize_all()

I still think that a pythonic way of doing this would be nice. If I end up having a clean implementation in the project that I am developing I'll contribute it here, if you welcome such a contribution.