ServiceNow Connector - Source Code
<?xml version="1.0" encoding="utf-8"?>
<ApiConfig Name="ServiceNow"
Desc="Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access."
Slug="servicenow-connector" Id="17205e73-c22f-4d57-a9d4-db295d46bdb3"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAACGCAMAAAAICYNLAAAAIGNIUk0AAHomAACAhAAA+gAAAIDoAAB1MAAA6mAAADqYAAAXcJy6UTwAAALoUExURQAAAGLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTmLYTv///9y1J+8AAAD2dFJOUwAAAxEnQmF/m7TI2OXu8vX29O/n3Mu3oIJkRikTBAsoTnunyeHx+vviqlINBiVWjsHm6MSRVwgvbrLk/ONvMAJq6uu2ayYOSZ2fGL/AZhkdddD90R5y1dZ0Gg/Oz0e6RSGT8JIgBdJYlZjKzArsni6+QNRTX2ABZVQtG7wJcR/NmZZbI49IQxC92Wf+xppwNiRP4KV387lou9McefixB7X5XAwXYmxN9z/Dbbgx3zg5WrOJjBbFSpAqg4hRK32XfKg8QaYSx+0yXj4i2q+cioeN3hSkUOmLczcV3Sw1o2miPa40TKE7dtvCRIB4sF2p13qrgTqsWSOp7WMAAAABYktHRPer3Hr3AAAACXBIWXMAAAsTAAALEwEAmpwYAAAAB3RJTUUH5wYBEBwcHjmxPQAACY1JREFUeNrFnGtAVMcVgO9ZEGkioKgrK4LEsAvy0IBC1LVuoJUoBjRgUEEkJTyiiDQlKkaQGKJriqAxJZGEImIwscRHlEi19uEzqdpUl9iUGGtia2rTJjHVzu/OZVlgl32cmTu7nJ9w5pxv586dxzlnriRxCZhF5eU9zGe47w8eeHCEn3/AyFGBo8eMVY8L0owPnhDSq8Jnn58pdGLYQ5MeDtfqyCCJ8I8cO3l4VHC058h6HMXETpn6SJwdICu4+GnTE2JjPEDW4yLx0RkzZ+kJSvSzZ/5wTqJ7yWTjhtjH1Ek4JIsk/+jHcw1uA5MNpzw+L5yNySyR8x9PcQuYbDRVs2AkD5QsI59ISxcOJhtcuOjJDF4oWTIyFy0WCiYbS38qSwmTWZYsXSYMTDaUrclRDiXLck22GDB5Ls9dESGGik5mK/JUyrnkn/b0T7SioGTJf6ZAaYfR5oUJRSKhZCkqLlTEJb9/zwaIpqKzxcrF/FxyV+etEg8lS0ku74OkzVYvTXYPFSGlS1dzcdFGy9YIewEHS8SaZRxctEnZT90HJcuCMmYu2uC5n7mXipCsYYxcVD0q0t1UhMwuZ+Kiys+7bbAPlOS16/BcAOvWB3qCik75aWguSpUmdLlxJtr1yOdI1dbme4qKkMDnUVxUqdwj48oipRUILqoybLYnqej76HqeoAqPZnmWis5fG1xxAbzwhKepCNmY7hyLrs7jPE9FiG+lMy7al4uQx2WxElHl5DHSf+WWDgUVne7zHHMBbKoeGipCxi52hAUQ8+JQURGyudA+F+3Fl7jP8solIMH+YwSoyRo6KkIyN9nDoqfUZ4eSipCXDXa4ALZ4bNtgXwK3DsYCMG7jt6jTjngls6Qk85UR9kKpWFGnDOIC+DnfRFqbtL2uPmzHzvQJRuOE9Ngdr1b5bmcMF1okwscWi66FmTxMu17TbEgBG0kpWz8pvJbDXEmqDRfAL9itNLyeVqOyjXH3Bux3vrF7FPuvrLLGAih4kNWGdlLeagchZHPcPndyA6vNxjetzAG8xWig6Zd5MeB8fYXmLXtaGM3WW3f8C4xn1aK9q13FNuT/t/osYbObs2+AUYDHmEZok+/TmICLrLNznI7Fsv7tfrN0zprG0nZEWjP+FJXYFs9ie1trn2WA/e8wtFz+Lj421RMjYxkgAbn9WAaWnfK2YNbAQdkBBvPzVWBp6M0QBnl9E3uYpWY33v5s717zAG0MfVXAE5QqYAiV/cqCValGt1k1ly+E5z0W7aK9EsyN3kMf7g8e4g14Ht6F9ZH8Xo8LgCpsi4Zi/vDwEfQSaZ7pofl9bIOjKt5oOl28j2G9vN8McoNYbCjkQAd/7gEg9QOkm1mx1A1AAnL/l5+rICVCmx5Hbsr1CT1Y05G/YjP3IzRzhdQhHXXKWNHIzt31a6VprQ3I0TItmmIFI5fSE8qzbSdxnkZPpFi/wWVQwicqTU4CnJqFcqV7lWL9FvcTpivOmQKs+x3O1+9BUv0BpZl/WnmGGeAMbnd/ViWdw2UMN4aKwIp+BOXsvJcUjNvUPCUmHY87yYR7S3n+GMULH4rBOoRKRGi3ShrUiyhPJQIEjKgho/ORTqC6tVNMrQfASpS74dJ8jFrtR6Kw0lBYdZIa9bB3iML6I2rbNVNC7WbHvCmokAguol787dIljNoHraKwjKgQ+xIpDqN2OUQUVnM7xl+k5IdR+5OoajAwnMX4S5JQQ3CqMCxAZSTyJVRlz0lxWJ0Yfw0ex/oY42+UhNpq/Fkc1lGMP38JVfBwTBzWFYw/Pwm1k98sDgu1KMZJVzFqZw3CJgjUZniM9CRGbXeiKKxKVAonR0JFnopMorA6UBHLjdIkjFp8sCisU6h8UJeEejNajova2ITpMP4+lmag6qffEoWFOpXq26RPUPnpy4VisGJQsbSA/dKH1zCKVwvEnHxqULcARs+R0lGvRsQUMVhHULG0olSpeQ9GkcwXMaGCqgvlbGazBFNRmrtiRRz25+KO8H8BCXxQmqRKBNanKFf0+CfBX3EhzVUm5YGkblzSIH8HYJcDq0wfL5YGFzxu7KZYKtTyQ8gD3UqDlB3IKvcugxxpnoFTrq1XeiXgU2SKt60nAI4L7hDymaJQM8B11NaOkMDPZT9gfBinTm4kKkkXVKJOiFTOZ5tTUdhsTEsa92OkDf+mQ7o52psh24LNVEce5s+QncYFvwkZOb4Xqxu1ce7pX/bcay9VDfquSXXvBIl/inSDY+LLvu5DJwf7jsoAz+GLYW6e48lVn8MOd0K0X/RhtS5AtyI3u9kz+9038Pb7EwBMqX3yJduNJqq86TLeeu0bA6pGLn7GwHXgFlvVyOcslTKXBtRTAnLT1StX94bga2wKPzrIYvuoVUXSLaYLBQGb0RVJF1cyldjGz7HCUqHC8/3SuLYVU78V/XYWm906q9pTOgmjYqj90rLn745K8CxMEBqm1rFZLT1kU4QX8g82A/RJ7plisn8n3/xXU7GauUTa1yZLT7c37NdVWqqDbmdbqjoHEgFk3z5RzdhTVOJvDSrwNDzDbIWK9vyVilNelo8s9EjIuVMVV85zFSIfG3RFCiAYuUuzlYhrOV92fqUprigvryjWfNV5Oeca57XGfw4+9MnXMHgKa/skI0Ovz1B2Tb3e7jg13VFiVLls87JfWostgnGP+P3Lwe0CFbbaxi1y0mB/EgRYiC+VEy7TUh1MzXItk4duSw6W5GFOVox1X/O+jbV6XVOTTs/bPOPfTsu2jf9hNahLKlLXBbV9E3V8/PjjUd+0Bfm2NybpWK18G+1k3afEExsZjAV8N/nu/uumZuvLBc2m62EPff8dy4JYXeN0NyLfIkPFUqnk3/nv1nSV9ZLYvyiq0rcGHcDeVY0743qTlIb5mXSVvufkmz7m/0TfO1GNqeNvcH39lS61QS6jUIH3o0wuP5lj3t2U33f5cuvuGjA73VAXubXSeadxXxgyj7Qv1ji/XFj7MibiQi15vebEiv/Nwyq2k4/q3g1nVU/zUlDGqKGO7x3Z0K/ILWT7wISsHbNlo8OB0YU9psvf97lv38bVNo7vvcgtjDMcHMu6JrD0fIe95xjx7Qa+T3HIrcom29scjsNTmcfX9CZbE3H1oUrCbqFVo20tvjPVyDogYtZmWXdV+20ln5+R295u11mZLNrL+uEY2UrB3eV9YUK/9pdSlFfpphxRX7BYDChZtJDjd8pNlv1v5ao4bfKl9qAzLg/ROIut737dfunahYN3Oj/Z58zi/wHJ99FRQvowYgAAACV0RVh0ZGF0ZTpjcmVhdGUAMjAyMy0wNi0wMVQxNjoyODoxMyswMDowMI1DjK0AAAAldEVYdGRhdGU6bW9kaWZ5ADIwMjMtMDYtMDFUMTY6Mjg6MTMrMDA6MDD8HjQRAAAAKHRFWHRkYXRlOnRpbWVzdGFtcAAyMDIzLTA2LTAxVDE2OjI4OjI4KzAwOjAwJ4NG1wAAAABJRU5ErkJggg=="
Version="2"
EngineVersion="12">
<VersionHistory>
<Change Ver="2" Date="2024-11-29" Type="New">Added images in HTTP and OAuth authentication instructions</Change>
<Change Date="2023-09-01" Type="New">Add new options Query and Fields for Read Data operation (get_table_rows).</Change>
<Change Date="2023-09-01" Type="Fix">Make ApiVersion optional.</Change>
<Change Date="2023-09-01" Type="Fix">Using Basic Auth throws error - Input string was not in a correct format.</Change>
<Change Date="2023-09-01" Type="Fix">Test connection always works</Change>
<Change Date="2023-03-15" Type="New">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="ServiceNow API" Url="https://[$InstanceName$].service-now.com/api/now" />
</ServiceUrls>
<Auths>
<Auth Type="HTTP" Name="BasicAuth" Label="Basic Auth"
Desc="Authenticate using ServiceNow instance username and password."
ConnStr="CredentialType=Basic;UserName=[$UserName$];Password=[$Password$];RetryMode=[$RetryMode$];RetryCountMax=[$RetryCountMax$];RetryMultiplyWaitTime=[$RetryMultiplyWaitTime$];RetryStatusCodeList=[$RetryStatusCodeList$];"
TestEndPoint="get_table_columns" TestParameters="TableName=incident">
<Params>
<Param Name="InstanceName" Required="True" />
<Param Name="UserName" Label="User name" Required="True"
Desc="You can create new user account and set password here https://{your-instance-id}.service-now.com/now/nav/ui/classic/params/target/sys_user.do or visit All \ Organization \ Users in the portal" />
<Param Name="Password" Required="True" Secret="True"
Desc="You can create new user account and set password here https://{your-instance-id}.service-now.com/now/nav/ui/classic/params/target/sys_user.do or visit All \ Organization \ Users in the portal" />
<Param Name="ApiVersion" Value="v2" Options="Default=;v2=v2;v1=v1" ValueTemplate="{$value$}/" />
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch"
Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
</Params>
<Notes>
<![CDATA[
<p>Locate your ServiceNow instance credentials and use them for HTTP authentication:</p>
<ol>
<li>Go to <a href="https://developer.servicenow.com/dev.do" target="_blank">https://developer.servicenow.com/dev.do</a> and log in.</li>
<li>Click on your profile picture.</li>
<li>
<p>When a menu appears, click on <strong>Manage instance password</strong> or a similar option:</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/http/manage-instance-password-in-service-now.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Manage instance password in ServiceNow"
title="Managing instance password in ServiceNow"
width="1002"
height="543" />
</li>
<li>
<p>In a new window you will see your HTTP Basic Authentication credentials:</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/http/use-instance-credentials-to-authenticate-to-service-now.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Use instance credentials to authenticate to ServiceNow"
title="Using instance credentials to authenticate to ServiceNow"
width="990"
height="443" />
</li>
<li>
<p>Done! Use this <strong>username</strong> and <strong>password</strong> when configuring <em>OAuth Authentication</em> parameters.</p>
</li>
</ol>
]]>
</Notes>
</Auth>
<Auth Name="OAuth" Type="OAuth"
Desc="Authenticate using ServiceNow instance username, password, OAuth Client ID, and Client Secret."
HelpLink="https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0778194"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;GrantType=Password;TokenUrl=https://[$InstanceName$].service-now.com/oauth_token.do;Username=[$UserName$];Password=[$Password$]"
TestEndPoint="get_table_columns" TestParameters="TableName=incident">
<Notes>
<![CDATA[
<p>Create OAuth application and use it for authentication:</p>
<ol>
<li>Login to your instance at <span style="text-decoration: underline">https://<strong>your-instance-id</strong>.service-now.com</span>.</li>
<li>
<p>Start by clicking on menu item <strong>All</strong>, then search for <strong>oauth</strong>, and click <strong>Application Registry</strong> option:</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/oauth/locate-oauth-configuration-in-service-now.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Locate OAuth configuration in ServiceNow"
title="Locating OAuth configuration in ServiceNow"
width="1000"
height="376" />
</li>
<li>
<p>Then click button <strong>New</strong>:</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/oauth/create-new-oauth-application-in-service-now.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Create new OAuth application in ServiceNow"
title="Creating a new OAuth application in ServiceNow"
width="1002"
height="303" />
</li>
<li>
<p>Continue by clicking <strong>Create an OAuth API endpoint for external clients</strong> option:</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/oauth/use-service-now-oauth-application-for-external-clients.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Use ServiceNow OAuth application for external clients"
title="Using ServiceNow OAuth application for external clients"
width="1002"
height="307" />
</li>
<li>
<p>Move on by naming your OAuth application and entering a Client Secret (or let it be generated automatically).</p>
</li>
<li>
<p>Finally give it a final touch by increasing the <strong>Refresh Token Lifespan</strong> value to <code>315,360,000</code> (it should last for 10 years):</p>
<img src="https://cdn.zappysys.com/api/images/authentication/service-now/oauth/configure-oauth-application-in-service-now.png"
loading="lazy"
decoding="async"
class="img-thumbnail"
alt="Configure OAuth application in ServiceNow"
title="Configuring OAuth application in ServiceNow"
width="1002"
height="435" />
</li>
<li>
<p>Done! Now you can use <strong>Client ID</strong> and <strong>Client Secret</strong> when configuring <em>OAuth Authentication</em> parameters.</p>
</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="InstanceName" Required="True" />
<Param Name="UserName" Label="User name" Required="True"
Desc="You can create new user account and set password here https://{your-instance-id}.service-now.com/now/nav/ui/classic/params/target/sys_user.do or visit All \ Organization \ Users in the portal" />
<Param Name="Password" Required="True" Secret="True"
Desc="You can create new user account and set password here https://{your-instance-id}.service-now.com/now/nav/ui/classic/params/target/sys_user.do or visit All \ Organization \ Users in the portal" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Required="True" Secret="True" />
<Param Name="ApiVersion" Required="True" Value="v2" Options="Default=;v2=v2;v1=v1"
ValueTemplate="{$value$}/" />
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch"
Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
</Params>
</Auth>
</Auths>
<Template>
<EndPoint Name="TmpPagination">
<Params>
<Param Name="PagingRowCount" Label="Page Size (Default=2000, Max=10000)" Type="Query"
Key="sysparm_limit" Hidden="True" Required="True" Value="2000"
Desc="Max can be 10000 but it may decrease performance if you fetching less than 10K rows in some requests." />
<Param Name="PagingMode" Type="Property" Value="ByResponseHeaderRfc5988" />
</Params>
</EndPoint>
<EndPoint Name="TmpPaginationOffset">
<Params>
<Param Name="PagingRowCount" Label="Page Size (Default=2000, Max=10000)" Type="Query"
Key="sysparm_limit" Hidden="True" Required="True" Value="2000"
Desc="Max can be 10000 but it may decrease performance if you fetching less than 10K rows in some requests." />
<Param Name="PagingOffset" Type="Query" Key="sysparm_offset" Hidden="True" Required="True" Value="0" />
<Param Name="PagingMode" Type="Property" Value="ByUrlParameter" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="sysparm_offset" />
<Param Name="PagingIncrementBy" Type="Property" Value="2000"
Desc="This must match with PagingRowCount Value" />
<Param Name="PagingByUrlEndStrategy" Type="Property" Required="True" Value="DetectBasedOnResponseSize" />
<Param Name="PagingByUrlCheckResponseMaxBytes" Type="Property" Required="True" Value="20"
Desc="If Last response is less than 20 bytes stop. Last response is {result:[]}" />
<Param Name="PagingByUrlCurrentPage" Type="Property" Value="0" />
</Params>
</EndPoint>
<EndPoint Name="TmpTableOutputColumns">
<OutputColumns>
<Column Name="sys_id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="500" />
<Column Name="label" DataType="DT_WSTR" Length="500" />
<Column Name="sys_name" DataType="DT_WSTR" Length="500" />
<Column Name="create_access" DataType="DT_WSTR" Length="50" />
<Column Name="alter_access" DataType="DT_WSTR" Length="50" />
<Column Name="access" DataType="DT_WSTR" Length="500" />
<Column Name="live_feed_enabled" DataType="DT_WSTR" Length="50" />
<Column Name="sys_updated_on" DataType="DT_DBTIMESTAMP" />
<Column Name="sys_class_name" DataType="DT_WSTR" Length="500" />
<Column Name="is_extendable" DataType="DT_WSTR" Length="50" />
<Column Name="user_role" DataType="DT_WSTR" Length="500" />
<Column Name="create_access_controls" DataType="DT_WSTR" Length="50" />
<Column Name="delete_access" DataType="DT_WSTR" Length="50" />
<Column Name="sys_updated_by" DataType="DT_WSTR" Length="100" />
<Column Name="sys_class_path" DataType="DT_WSTR" Length="500" />
<Column Name="sys_created_on" DataType="DT_DBTIMESTAMP" />
<Column Name="caller_access" DataType="DT_WSTR" Length="500" />
<Column Name="client_scripts_access" DataType="DT_WSTR" Length="50" />
<Column Name="extension_model" DataType="DT_WSTR" Length="500" />
<Column Name="actions_access" DataType="DT_WSTR" Length="50" />
<Column Name="sys_scope.link" Label="sys_scope_link" DataType="DT_WSTR" Length="1000" />
<Column Name="sys_scope.value" Label="sys_scope_value" DataType="DT_WSTR" Length="500" />
<Column Name="sys_created_by" DataType="DT_WSTR" Length="100" />
<Column Name="sys_class_code" DataType="DT_WSTR" Length="500" />
<Column Name="read_access" DataType="DT_WSTR" Length="50" />
<Column Name="super_class" DataType="DT_WSTR" Length="500" />
<Column Name="sys_mod_count" DataType="DT_WSTR" Length="50" />
<Column Name="ws_access" DataType="DT_WSTR" Length="50" />
<Column Name="update_access" DataType="DT_WSTR" Length="50" />
<Column Name="sys_package.link" Label="sys_package_link" DataType="DT_WSTR" Length="1000" />
<Column Name="sys_package.value" Label="sys_package_value" DataType="DT_WSTR" Length="500" />
<Column Name="sys_update_name" DataType="DT_WSTR" Length="500" />
<Column Name="scriptable_table" DataType="DT_WSTR" Length="50" />
<Column Name="configuration_access" DataType="DT_WSTR" Length="50" />
<Column Name="number_ref" DataType="DT_WSTR" Length="500" />
<Column Name="sys_policy" DataType="DT_WSTR" Length="500" />
<Column Name="sys_scope" DataType="DT_WSTR" Length="500" />
<Column Name="sys_package" DataType="DT_WSTR" Length="500" />
<Column Name="super_class.link" Label="super_class_link" DataType="DT_WSTR" Length="1000" />
<Column Name="super_class.value" Label="super_class_value" DataType="DT_WSTR" Length="500" />
<Column Name="number_ref.link" Label="number_ref_link" DataType="DT_WSTR" Length="1000" />
<Column Name="number_ref.value" Label="number_ref_value" DataType="DT_WSTR" Length="500" />
<Column Name="user_role.link" Label="user_role_link" DataType="DT_WSTR" Length="1000" />
<Column Name="user_role.value" Label="user_role_value" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<EndPoint Name="TmpDynamicTableInputColumns">
<InputColumns>
<Column Name="[$parent.Pivot_Name$]" DataType="DT_WSTR" Length="2000" Expand="True"
DataEndPoint="get_table_columns" DataEndPointParameters="TableName=[$TableName$];"
ColumnInfoMap="DataType=Pivot_Value_internal_type"
DataTypeMap="DT_BOOL=boolean;
DT_NUMERIC=currency,price,float,decimal,percent_complete;
DT_STR=currency2,domain_id,document_id,reference,audio,video,user_image,phone_number_e164,file_attachment,GUID;
DT_DBTIMESTAMP=glide_date_time,calendar_date_time,due_date,glide_duration,insert_timestamp,glide_utc_time,glide_time;
DT_DBDATE=glide_date;
DT_I4=integer;
DT_I8=longint"
KeyIf="sys_id" />
</InputColumns>
</EndPoint>
<EndPoint Name="TmpDynamicTableOutputColumns">
<OutputColumns>
<Column Name="sys_id" DataType="DT_WSTR" Length="64" />
<Column
Name="<<[$parent.Pivot_Value_internal_type$]|~|domain_id|document_id|reference|currency2|~|[$parent.Pivot_Name$].value|~|[$parent.Pivot_Name$],FUN_IF_REGEX_MATCH>>"
Label="[$parent.Pivot_Name$]" DataType="DT_WSTR" Length="2000" Expand="True"
DataEndPoint="get_table_columns" DataEndPointParameters="TableName=[$TableName$];"
ColumnInfoMap="DataType=Pivot_Value_internal_type"
DataTypeMap="DT_BOOL=boolean;
DT_NUMERIC=currency,price,float,decimal,percent_complete;
DT_STR=currency2,domain_id,document_id,reference,audio,video,user_image,phone_number_e164,file_attachment,GUID;
DT_DBTIMESTAMP=glide_date_time,calendar_date_time,due_date,glide_duration,insert_timestamp,glide_utc_time,glide_time;
DT_DBDATE=glide_date;
DT_I4=integer;
DT_I8=longint"
KeyIf="sys_id" />
</OutputColumns>
</EndPoint>
</Template>
<EndPoints>
<!-- GET TABLES **********************************************************-->
<EndPoint Name="get_tables_admin" Template="TmpTableOutputColumns, TmpPagination"
Label="Get List of Tables - Admin (Use sys_db_object)" Url="[$ApiVersion$]table/sys_db_object"
Method="GET" Filter="$.result"
Desc="Get a list of tables by quering sys_db_object. To read data from this object make sure user has read permission to this table."
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
CachedTtl="120">
<Params>
<Param Name="SysId" ReadAs="sys_id" Key="sys_id" IsKey="True" Type="Query" />
<Param Name="Query" Key="sysparm_query" Type="Query" Value="ORDERBYname"
Options="Filter by active status=active=true;Order By name column=ORDERBYname;Order By name column (Descending)=ORDERBYDESCname;Order By Multiple Fields (ASC + DESC)=ORDERBYcolumn1^ORDERBYDESCcolumn2;" />
<Param Name="Fields" Key="sysparm_fields" Type="Query" />
</Params>
</EndPoint>
<!-- GET USER TABLES **********************************************************-->
<EndPoint Name="get_user_tables_admin" Template="TmpTableOutputColumns, TmpPagination"
Label="Get List of User Tables - Admin (Use sys_db_object)"
Url="[$ApiVersion$]table/sys_db_object?sysparm_query=sys_created_by!=system" Method="GET"
Filter="$.result" Desc="Get a list of tables not created by 'system' user"
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
CachedTtl="120">
<Params>
<Param Name="SysId" ReadAs="sys_id" Key="sys_id" IsKey="True" Type="Query" />
</Params>
</EndPoint>
<!-- GET SYSTEM TABLES **********************************************************-->
<EndPoint Name="get_system_tables_admin" Template="TmpTableOutputColumns, TmpPagination"
Label="Get List of System Tables - Admin (Use sys_db_object)"
Url="[$ApiVersion$]table/sys_db_object?sysparm_query=sys_created_by=system" Method="GET"
Filter="$.result" Desc="Get a list of tables created by 'system' user"
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
CachedTtl="120">
<Params>
<Param Name="SysId" ReadAs="sys_id" Key="sys_id" IsKey="True" Type="Query" />
</Params>
</EndPoint>
<!-- GET METADATA **********************************************************-->
<EndPoint Name="get_table_columns_admin" Template="TmpPaginationOffset"
Label="Get Table Columns - Admin (Use sys_dictionary)" Url="[$ApiVersion$]table/sys_dictionary"
Method="GET"
Desc="Get a list of columns by quering sys_dictionary. To read data from this table make sure user has read permission to sys_dictionary table."
HelpLink="https://docs.servicenow.com/bundle/utah-application-development/page/administer/data-dictionary-tables/concept/c_SystemDictionary.html"
DotAsPath="True" UseRawCache="True" CachedTtl="120">
<Params>
<Param Name="TableName" Key="name" ReadAs="name" Required="True" IsKey="True" Type="Query" />
<Param Name="Filter" Required="True" Type="Property" Value="$.result" />
</Params>
<OutputColumns>
<Column Name="sys_id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="50" />
<Column Name="element" DataType="DT_WSTR" Length="100" />
<Column Name="internal_type.value" Label="internal_type" DataType="DT_WSTR" Length="100" />
<Column Name="max_length" DataType="DT_WSTR" Length="50" />
<Column Name="column_label" DataType="DT_WSTR" Length="100" />
<Column Name="sys_name" DataType="DT_WSTR" Length="100" />
<Column Name="comments" DataType="DT_WSTR" Length="100" />
<Column Name="calculation" DataType="DT_WSTR" Length="500" />
<Column Name="dynamic_ref_qual" DataType="DT_WSTR" Length="100" />
<Column Name="choice_field" DataType="DT_WSTR" Length="100" />
<Column Name="function_field" DataType="DT_WSTR" Length="50" />
<Column Name="sys_updated_on" DataType="DT_WSTR" Length="100" />
<Column Name="spell_check" DataType="DT_WSTR" Length="50" />
<Column Name="reference_cascade_rule" DataType="DT_WSTR" Length="100" />
<Column Name="reference" DataType="DT_WSTR" Length="100" />
<Column Name="sys_updated_by" DataType="DT_WSTR" Length="50" />
<Column Name="read_only" DataType="DT_WSTR" Length="50" />
<Column Name="sys_created_on" DataType="DT_WSTR" Length="100" />
<Column Name="array_denormalized" DataType="DT_WSTR" Length="50" />
<Column Name="element_reference" DataType="DT_WSTR" Length="50" />
<Column Name="reference_key" DataType="DT_WSTR" Length="100" />
<Column Name="reference_qual_condition" DataType="DT_WSTR" Length="100" />
<Column Name="xml_view" DataType="DT_WSTR" Length="50" />
<Column Name="dependent" DataType="DT_WSTR" Length="100" />
<Column Name="internal_type_link" DataType="DT_WSTR" Length="500" />
<Column Name="sys_created_by" DataType="DT_WSTR" Length="50" />
<Column Name="use_dependent_field" DataType="DT_WSTR" Length="50" />
<Column Name="delete_roles" DataType="DT_WSTR" Length="100" />
<Column Name="virtual_type" DataType="DT_WSTR" Length="100" />
<Column Name="active" DataType="DT_WSTR" Length="50" />
<Column Name="choice_table" DataType="DT_WSTR" Length="100" />
<Column Name="foreign_database" DataType="DT_WSTR" Length="100" />
<Column Name="sys_update_name" DataType="DT_WSTR" Length="500" />
<Column Name="unique" DataType="DT_WSTR" Length="50" />
<Column Name="dependent_on_field" DataType="DT_WSTR" Length="100" />
<Column Name="dynamic_creation" DataType="DT_WSTR" Length="50" />
<Column Name="primary" DataType="DT_WSTR" Length="50" />
<Column Name="sys_policy" DataType="DT_WSTR" Length="100" />
<Column Name="next_element" DataType="DT_WSTR" Length="100" />
<Column Name="virtual" DataType="DT_WSTR" Length="50" />
<Column Name="widget" DataType="DT_WSTR" Length="100" />
<Column Name="use_dynamic_default" DataType="DT_WSTR" Length="50" />
<Column Name="sizeclass" DataType="DT_WSTR" Length="100" />
<Column Name="mandatory" DataType="DT_WSTR" Length="50" />
<Column Name="sys_class_name" DataType="DT_WSTR" Length="100" />
<Column Name="dynamic_default_value" DataType="DT_WSTR" Length="100" />
<Column Name="write_roles" DataType="DT_WSTR" Length="100" />
<Column Name="array" DataType="DT_WSTR" Length="50" />
<Column Name="audit" DataType="DT_WSTR" Length="50" />
<Column Name="read_roles" DataType="DT_WSTR" Length="100" />
<Column Name="sys_scope_link" DataType="DT_WSTR" Length="500" />
<Column Name="sys_scope_value" DataType="DT_WSTR" Length="50" />
<Column Name="create_roles" DataType="DT_WSTR" Length="100" />
<Column Name="dynamic_creation_script" DataType="DT_WSTR" Length="100" />
<Column Name="defaultsort" DataType="DT_WSTR" Length="100" />
<Column Name="use_reference_qualifier" DataType="DT_WSTR" Length="50" />
<Column Name="display" DataType="DT_WSTR" Length="50" />
<Column Name="reference_floats" DataType="DT_WSTR" Length="50" />
<Column Name="sys_mod_count" DataType="DT_WSTR" Length="50" />
<Column Name="default_value" DataType="DT_WSTR" Length="100" />
<Column Name="staged" DataType="DT_WSTR" Length="50" />
<Column Name="reference_type" DataType="DT_WSTR" Length="100" />
<Column Name="sys_package_link" DataType="DT_WSTR" Length="500" />
<Column Name="sys_package_value" DataType="DT_WSTR" Length="50" />
<Column Name="formula" DataType="DT_WSTR" Length="100" />
<Column Name="attributes" DataType="DT_WSTR" Length="100" />
<Column Name="choice" DataType="DT_WSTR" Length="100" />
<Column Name="reference_qual" DataType="DT_WSTR" Length="100" />
<Column Name="table_reference" DataType="DT_WSTR" Length="50" />
<Column Name="text_index" DataType="DT_WSTR" Length="50" />
<Column Name="function_definition" DataType="DT_WSTR" Length="100" />
<Column Name="sys_scope" DataType="DT_WSTR" Length="100" />
<Column Name="sys_package" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- GET TABLES **********************************************************-->
<EndPoint Name="get_tables" Label="Get List of Tables" Url="/doc/table/schema" Method="GET" Filter="$.result"
Desc="Get a list of tables."
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
CachedTtl="120">
<OutputColumns>
<Column Name="value" Label="name" DataType="DT_WSTR" Length="500" />
<Column Name="label" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<!-- GET METADATA **********************************************************-->
<EndPoint Name="get_table_columns" Label="Get Table Columns" Url="/ui/meta/[$TableName$]" Method="GET"
Desc="Get a list of columns of a table"
HelpLink="https://docs.servicenow.com/bundle/utah-application-development/page/administer/data-dictionary-tables/concept/c_SystemDictionary.html"
CachedTtl="120">
<Params>
<Param Name="TableName" Required="True" Desc="Table name" OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="name" />
<Param Name="Filter" Required="True" Type="Property" Value="$.result.columns" />
<Param Name="EnablePivot" Type="Property" Value="True" Hidden="True" />
<Param Name="ElementScopeSeparator" Type="Property" Value="_" Hidden="True" />
</Params>
<OutputColumns>
<Column Name="Pivot_Value_name" Label="name" DataType="DT_WSTR" Length="128" />
<Column Name="Pivot_Name" Label="element" DataType="DT_WSTR" Length="128" />
<Column Name="Pivot_Value_label" Label="label" DataType="DT_WSTR" Length="96" />
<Column Name="Pivot_Value_internal_type" Label="internal_type" DataType="DT_WSTR" Length="128" />
<Column Name="Pivot_Value_max_length" Label="max_length" DataType="DT_I8" />
<Column Name="Pivot_Value_column_type" Label="column_type" DataType="DT_WSTR" Length="128" />
<Column Name="Pivot_Value_type" Label="type" DataType="DT_WSTR" Length="60" />
<Column Name="Pivot_Value_filterable" Label="filterable" DataType="DT_BOOL" />
<Column Name="Pivot_Value_reference_display_field" Label="reference_display_field" DataType="DT_WSTR"
Length="60" />
<Column Name="Pivot_Value_reference_attributes_display_field"
Label="reference_attributes_display_field" DataType="DT_WSTR" Length="60" />
<Column Name="Pivot_Value_reference_attributes_ref_ac_columns"
Label="reference_attributes_ref_ac_columns" DataType="DT_WSTR" Length="108" />
<Column Name="Pivot_Value_reference_attributes_ref_ac_columns_search"
Label="reference_attributes_ref_ac_columns_search" DataType="DT_WSTR" Length="80" />
<Column Name="Pivot_Value_reference_attributes_ref_ac_display_value"
Label="reference_attributes_ref_ac_display_value" DataType="DT_BOOL" />
<Column Name="Pivot_Value_reference_attributes_ref_auto_completer"
Label="reference_attributes_ref_auto_completer" DataType="DT_WSTR" Length="80" />
<Column Name="Pivot_Value_canmatch" Label="canmatch" DataType="DT_BOOL" />
<Column Name="Pivot_Value_cangroup" Label="cangroup" DataType="DT_BOOL" />
<Column Name="Pivot_Value_max_unit" Label="max_unit" DataType="DT_WSTR" Length="80" />
<Column Name="Pivot_Value_mandatory" Label="mandatory" DataType="DT_BOOL" />
<Column Name="Pivot_Value_reference" Label="reference" DataType="DT_WSTR" Length="100" />
<Column Name="Pivot_Value_default" Label="default" DataType="DT_WSTR" Length="255" />
<Column Name="Pivot_Value_base_type" Label="base_type" DataType="DT_WSTR" Length="32" />
<Column Name="Pivot_Value_read_only" Label="read_only" DataType="DT_BOOL" />
<Column Name="Pivot_Value_hint" Label="hint" DataType="DT_WSTR" Length="388" />
<Column Name="Pivot_Value_attributes" Label="attributes" DataType="DT_WSTR" Length="4000" />
<Column Name="Pivot_Value_cansort" Label="cansort" DataType="DT_BOOL" />
<Column Name="Pivot_Value_multitext" Label="multitext" DataType="DT_BOOL" />
<Column Name="Pivot_Value_choice_type" Label="choice_type" DataType="DT_I8" />
<Column Name="Pivot_Value_choices" Label="choices" DataType="DT_NTEXT" />
</OutputColumns>
</EndPoint>
<!-- GET TABLE DATA **********************************************************-->
<EndPoint Name="get_table_rows" Template="TmpDynamicTableOutputColumns, TmpPagination" Label="Get Table Rows"
Url="[$ApiVersion$]table/[$TableName$][$SysId$]" Method="GET" Filter="$.result" Desc="Get table rows"
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html#title_table-GET">
<Params>
<Param Name="TableName" Required="True" Desc="Table name" OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="name" />
<Param Name="SysId" ReadAs="sys_id" IsKey="True" ValueTemplate="/{$value$}" Desc="Row 'sys_id'" />
<Param Name="Fields" Type="Query" Key="sysparm_fields" MultiSelect="True"
OptionsEndPoint="get_table_columns" OptionsEndPointValueColumn="name"
OptionsEndPointParameters="TableName=[$TableName$]"
Desc="Comma-separated field names you want to return in the response. E.g. sys_id,name,label,sys_name,sys_updated_on. Using this setting reduces the response size and speed up the processing." />
<Param Name="Fields"
Label="Fields to include in response (Keep blank to get all fields -OR- Enter comma separated list)"
Key="sysparm_fields" Type="Query" />
<Param Name="Query" Label="Query / Order By (Server Side Filter)" Type="Query" Key="sysparm_query"
Options="Equal Example=numberISINC0000060;AND Example=category=software^category=hardware;OR Example=category=software^ORcategory=hardware;IN Example=categoryINsoftware,hardware;STARTSWITH Example=categorySTARTSWITHsoftware;NOT EQUAL Example=category!=software;Filter Condition AND Order By=category!=software^ORDERBYname;Order By name column=ORDERBYname;Order By name column (Descending)=ORDERBYDESCname;Order By Multiple Fields (ASC + DESC)=ORDERBYcolumn1^ORDERBYDESCcolumn2;"
Desc="Server side search / filter condition"
HelpLink="https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html" />
<Param Name="ContineOn404Error" Hidden="True" Type="Property" Value="True" />
<Param Name="DateFormatString" Type="Property" Value="yyyy-MM-dd HH:mm:ss" />
</Params>
<OutputColumns>
<Column Name="sys_id" DataType="DT_STR" Length="64" />
</OutputColumns>
</EndPoint>
<!-- INSERT TABLE ROW **********************************************************-->
<EndPoint Name="insert_table_row" Template="TmpDynamicTableOutputColumns,TmpDynamicTableInputColumns"
Label="Delete a Table Row" Url="[$ApiVersion$]table/[$TableName$]" Method="POST"
ContentType="application/json" JsonRowFormat="Multicontent" Filter="$.result"
Desc="Insert a row into the table."
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
DotAsPath="True">
<Params>
<Param Name="TableName" Required="True" Desc="Table name" OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="name" />
</Params>
<Body>
<![CDATA[{$rows$}]]>
</Body>
</EndPoint>
<!-- UPDATE TABLE ROW **********************************************************-->
<EndPoint Name="update_table_row" Template="TmpDynamicTableOutputColumns,TmpDynamicTableInputColumns"
Label="Update a Table Row" Url="[$ApiVersion$]table/[$TableName$]/[$SysId$]" Method="PUT"
ContentType="application/json" JsonRowFormat="Multicontent" Filter="$.result"
Desc="Insert a row into the table."
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html"
DotAsPath="True">
<Params>
<Param Name="TableName" Required="True" Desc="Table name" OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="name" />
<Param Name="SysId" ReadAs="sys_id" IsKey="True" Required="True" Desc="Row 'sys_id'" />
<Param Name="ContineOn404Error" Hidden="True" Type="Property" Value="True" />
</Params>
<InputColumns>
<Column Name="SysId" Label="sys_id" ReadAs="sys_id" DataType="DT_WSTR" Length="500" MapToParam="True"
Key="True" ExcludeFromRowMap="True" />
</InputColumns>
<Body>
<![CDATA[{$rows$}]]>
</Body>
</EndPoint>
<!-- DELETE TABLE ROW **********************************************************-->
<EndPoint Name="delete_table_row" Label="Delete table row" Url="[$ApiVersion$]table/[$TableName$]/[$SysId$]"
Method="DELETE" OutputHeaders="StatusCode"
Desc="Delete the specified row in the table by row 'sys_id'."
HelpLink="https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html">
<Params>
<Param Name="TableName" Required="True" Desc="Table name" OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="name" />
<Param Name="SysId" ReadAs="sys_id" Required="True" IsKey="True" Desc="Row 'sys_id'" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{}"
ValueTemplate="{status:'Deleted', sys_id:'[$SysId$]'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
<Param Name="ContineOn404Error" Hidden="True" Type="Property" Value="True" />
</Params>
<InputColumns>
<Column Name="SysId" Label="sys_id" ReadAs="sys_id" DataType="DT_WSTR" Length="500" MapToParam="True"
Key="True" />
</InputColumns>
<OutputColumns>
<Column Name="status" DataType="DT_WSTR" Length="64" />
<Column Name="sys_id" DataType="DT_WSTR" Length="64" />
<Column Name="ResponseHeaders_StatusCode" Label="http_status" DataType="DT_I4" />
</OutputColumns>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Tables" SelectEndPoint="get_tables" />
<Table Name="[$parent.value$]" Expand="True" Order="-1" SelectEndPoint="get_table_rows"
LookupEndPoint="get_table_rows" InsertEndPoint="insert_table_row" UpdateEndPoint="update_table_row"
DeleteEndPoint="delete_table_row" DataEndPoint="get_tables" DataEndPointParameters="Fields=name">
<Params>
<Param Name="TableName" Value="[$parent.value$]" Hidden="True" />
</Params>
</Table>
</Tables>
<Examples>
<Example Group="ODBC" Label="Get a list of incidents" Default="True">
<Code>
<![CDATA[SELECT * FROM incident
--Using Primary Key
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause
--Using Filter Expression
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator) -- (number=INC0000001 and state=7) OR (number=INC0000002)
--more information about filter here https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
-- To read all available tables execute this query:
-- SELECT * FROM Tables
-- Other common tables:
-----------------------
-- SELECT * FROM sys_db_object
-- SELECT * FROM sys_dictionary
-- SELECT * FROM sys_user
-- SELECT * FROM sys_user_has_role
-- SELECT * FROM sys_user_grmember
-- SELECT * FROM task
-- SELECT * FROM task_sla
-- SELECT * FROM incident
-- SELECT * FROM incident_sla
-- SELECT * FROM change_request
-- SELECT * FROM cmdb_ci_computer
-- SELECT * FROM cmdb_ci_outage
-- SELECT * FROM cmdb_ci
-- SELECT * FROM sn_customerservice_case
-- SELECT * FROM kb_knowledge
-- SELECT * FROM kb_use
-- SELECT * FROM sc_req_item
-- SELECT * FROM sc_request
-- SELECT * FROM sc_task
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get an incident by sys_id">
<Code>
<![CDATA[SELECT * FROM incident
WHERE sys_id = 'SYS_ID_GOES_HERE']]>
</Code>
</Example>
<Example Group="ODBC" Label="Get only selected columns and speed up the query"
Desc="By default all column values are returned in the result set. However, you can instruct the ServiceNow API to send only specific columns you care about by setting 'Fields' parameter in the WITH clause. This will speed up the response significantly.">
<Code>
<![CDATA[SELECT number,name FROM incident WITH (Fields='number,name') --Setting Fields parameter will speed up the query]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get the display value of a reference field"
Desc="This example shows how to get related record(s) from the reference field. Basically, you need to know referenced table name for that referenced field. Then you can query by sys_id (Primary Key)">
<Code>
<![CDATA[--STEP#1 returns incident number and related company (sys_id)
SELECT number,company FROM incident
--STEP#2 find related table for company field
select name,reference from get_table_columns order by 1 WITH(TableName='incident')
--STEP#3 query related table with sys_id returned in step#1
select sys_id, name from core_company where sys_id='31bea3d53790200044e0bfc8bcbe5dec'
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Query table using serverside filter expression"
Desc="This example shows how to filter data from a table using server side filter expression. Its always faster to do this way rather than using in WHERE clause (Client Side Filter)">
<Code>
<![CDATA[SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--syntax (single condition)
--sysparm_query=<col_name><operator><value>
--syntax (multiple conditions)
--sysparm_query=<col_name><operator><value>[<operator><col_name><operator><value>]
--more info check below links
-- https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html#title_table-GET
-- https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
-- Some More Advanced Params listed here
-- https://developer.servicenow.com/dev.do#!/learn/learning-plans/xanadu/servicenow_application_developer/app_store_learnv2_rest_xanadu_request_parameters
--More examples
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator) -- (number=INC0000001 and state=7) OR (number=INC0000002)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Create an incident">
<Code>
<![CDATA[INSERT INTO incident(incident_state, severity, category, cause)
VALUES (3, 1, 'software', 'Missing a software feature.')]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update an incident">
<Code>
<![CDATA[UPDATE incident
SET cause = 'A bug in the software'
,severity = 3
,incident_state = 2
WHERE sys_id = 'SYS_ID_GOES_HERE']]>
</Code>
</Example>
<Example Group="ODBC" Label="Delete an incident">
<Code>
<![CDATA[DELETE incident
WHERE sys_id = 'SYS_ID_GOES_HERE']]>
</Code>
</Example>
<Example Group="ODBC" Label="Get a list of tables">
<Code>
<![CDATA[SELECT * FROM Tables]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get a list of system tables">
<Code>
<![CDATA[SELECT * FROM SystemTables]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get a list of user tables">
<Code>
<![CDATA[SELECT * FROM UserTables]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get columns of a specific table">
<Code>
<![CDATA[SELECT * FROM get_table_columns
where name = 'TableNameGoesHere']]>
</Code>
</Example>
<Example Group="ODBC" Label="Get all columns of all tables (can be slow)">
<Code>
<![CDATA[SELECT * FROM get_table_columns_admin --requires readonly permissions on sys_dictionary and sys_db_object tables]]>
</Code>
</Example>
<Example Group="ODBC" Label="Use 'generic_request' endpoint to get Accounts"
Desc="Use 'generic_request' endpoint to get Accounts (or access another ServiceNow API). Use ServiceNow API Reference and Query Builder to build custom requests.">
<Code>
<![CDATA[SELECT *
FROM generic_request
WITH(
Url='/v2/table/incident?sysparm_limit=10000&sysparm_query=numberLIKE0001^ORDERBYsys_created_on',
Filter='$.result',
Meta='sys_id:string;number:string;business_stc:int', --//comment this to see all columns (SLOW)
PagingMode='ByResponseHeaderRfc5988',
--Method='GET',
--Body='{your POST body}'
)
-- Request URL should be partial like this one:
-- /v2/some_table?some-parameter=somevalue1&some-parameter=somevalue2
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
