ServiceNow Connector for SSRS How to Get Attachments
Introduction
In this article we will delve deeper into ServiceNow and SSRS integration, and will learn how to get attachments. We are continuing from where we left off. By this time, you must have installed ODBC PowerPack, created ODBC Data Source, and configured authentication settings in your ServiceNow account .
So, let's not waste time and begin.
Use Query Builder to generate SQL query
-
The first thing you have to do is open Query Builder:
ZappySys API Driver - ServiceNowRead and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.ServicenowDSN
-
Then simply select the Get Attachments endpoint (action).
-
Continue by configuring the Required parameters. You can also set optional parameters too.
-
Move on by hitting Preview Data button to preview the results.
-
If you see the results you need, simply copy the generated query:
-
That's it! You can use this query in SSRS.
Let's not stop here and explore SQL query examples, including how to use them in Stored Procedures and Views (virtual tables) in the next steps.
SQL query examples
Use these SQL queries in your SSRS data source:
How to Get Attachments Information (With Optional Attachment Filter)
-- Get all attachments for the parent table 'problem'
SELECT * FROM get_attachments WITH(TableName='problem')
-- Get attachments where file name ends with .png or .zip (attachment table filter)
SELECT * FROM get_attachments WITH(TableName='problem', Query='file_nameENDSWITH.png^ORfile_nameENDSWITH.zip')
-- Get all attachments for a specific parent record (by parent sys_id)
SELECT * FROM get_attachments WITH(TableName='problem', ParentSysId='62304320731823002728660c4cf6a7e8')
-- Get a single attachment metadata row by exact attachment sys_id
SELECT * FROM get_attachments WITH(SysId='4b2d41168396b21032ddb9f6feaad38f')
-- Get attachments uploaded today
SELECT * FROM get_attachments WITH(TableName='problem', Query='sys_created_on>=<<today,FUN_TO_DATETIME>>^sys_created_on<=<<today|~|yyyy-MM-dd 23:59:59,FUN_TO_DATETIME>>')
-- Get attachments uploaded in the last 2 days
SELECT * FROM get_attachments WITH(TableName='problem', Query='sys_created_on>=<<today-2d,FUN_TO_DATETIME>>')
-- Get attachments uploaded after a specific date
SELECT * FROM get_attachments WITH(TableName='problem', Query='sys_created_on>=2026-01-01')
-- Get attachments with exact file name match
SELECT * FROM get_attachments WITH(TableName='problem', Query='file_name=dump.png')
-- Get attachments where file name starts with 'dump'
SELECT * FROM get_attachments WITH(TableName='problem', Query='file_nameSTARTSWITHdump')
-- Get attachments where file name contains 'error'
SELECT * FROM get_attachments WITH(TableName='problem', Query='file_nameLIKEerror')
-- Get attachments where file name ends with .pdf
SELECT * FROM get_attachments WITH(TableName='problem', Query='file_nameENDSWITH.pdf')
-- Get only image attachments
SELECT * FROM get_attachments WITH(TableName='problem', Query='content_typeSTARTSWITHimage/')
-- Get attachments larger than 1 MB
SELECT * FROM get_attachments WITH(TableName='problem', Query='size_bytes>1048576')
-- Get attachments uploaded by a specific user
SELECT * FROM get_attachments WITH(TableName='problem', Query='sys_created_by=admin')
-- Order attachments by created date (ascending)
SELECT * FROM get_attachments WITH(TableName='problem', Query='ORDERBYsys_created_on')
-- Order attachments by size descending
SELECT * FROM get_attachments WITH(TableName='problem', Query='ORDERBYDESCsize_bytes')Stored Procedures and Views
Create Custom Stored Procedure
You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.
Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
-
Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
-
Enter the desired Procedure name and click on OK:
-
Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
CREATE PROCEDURE [usp_get_orders] @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>' AS SELECT * FROM Orders where OrderDate >= '<@fromdate>';
-
That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
Exec usp_get_orders '1996-01-01';
Create Custom Virtual Table
ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.
If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.
Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.
-
Go to Custom Objects Tab and Click on Add button and Select Add Table:
-
Enter the desired Table name and click on OK:
-
And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.
-
Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here
SELECT "ShipCountry", "OrderID", "CustomerID", "EmployeeID", "OrderDate", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode" FROM "Orders" Where "ShipCountry"='USA'
-
That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:
SELECT * FROM "vt__usa_orders_only"
Get Attachments in SSRS
-
Open Visual Studio and create a new SSRS project.
-
Then add a new Shared Data Source (you can create a non-shared data source inside report too):
-
Continue with creating the Shared Data Source. Select Microsoft SQL Server as Type and hit Build button to proceed further:
-
Once a window opens, configure it similarly. Configure "ServicenowDSN" as database name. Finally, hit Test Connection and OK:
ServicenowDSN
-
Another window opens, and it should look similarly to this one below which ends the creation of a Data Source:
DataSource=localhost,5000;Initial Catalog=ServicenowDSN
-
Now it's time to create a Dataset. If you don't have a report created, in one of the wizard's steps it will look like this:
SELECT * FROM incident --Using Primary Key --SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause --OR-- --Use below to query system / hidden tables (faster response - No Table name lookup needed in below) SELECT * FROM get_table_rows WITH(TableName='incident', Query='number=INC0000001') --================================= --Examples: Using Filter Expression --================================= -- Incremental Load Pattern: Load only incidents updated in the last 24 hours (see FUN_TO_DATETIME function help for more information, you can use +/- d, m, y, min,h,s along with now, today, yesterday, weekstart, weekend, monthstart, monthend, yearstart, yearend functions) --SELECT * FROM incident WITH(Query='sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>' --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
-
Finally, once you complete the report, similar results will show up:
More actions supported by ServiceNow Connector
Learn how to perform other actions directly in SSRS with these how-to guides:
- Delete a Table Row
- Delete Attachment
- Delete table row
- Download Table Attachment (Single - Using Id)
- Download Table Attachments (Multiple - Using Query)
- Download Table Attachments By Parent Row Search
- Get Attachments By Parent Row Search
- Get List Of System Tables Admin Use Sys Db Object
- Get List of Tables
- Get List Of Tables Admin Use Sys Db Object
- Get List Of User Tables Admin Use Sys Db Object
- Get Record Labels (Tags)
- Get Table Columns
- Get Table Columns Admin Use Sys Dictionary
- Get Table Row Count
- Get Table Rows
- Test Connection
- Update a Table Row
- Upload Attachment
- Make Generic API Request
- Make Generic API Request (Bulk Write)
