POST api/publicapi/getentitylistbyquery
This method can be used to fetch most entities in NSP, for example SysTicket, Ticket, Person, Organization, Incident, Problem, Change, Ci, Contract, SysPriority, SysEntityStatus, and all user-created types. It is possible to specify filters, sorting and exactly which data to fetch.
This method returns human-readable list of entities with localized texts. For example, Priority: “High” is returned, not just PriorityId 6.
Basic call info:
POST api/publicapi/getentitylistbyquery
{
"entityType": "Ci",
"page": 1,
"pageSize": 20,
}
If “page” and “pageSize” are not included, all existing records will be returned. Generally this is bad practise and should be avoided, only omit paging where it is known beforehand that table has a small number of records, for example fetching priorities (SysPriority). Paging may be omitted in some examples in this documentation for brevity.
If you want to have DynamicProperties for some dynamic entity like: Ci, Contract,… you need to set LoadDynamicProperties on true:
POST api/publicapi/getentitylistbyquery
{
"entityType": "Ci",
"page": 1,
"pageSize": 20,
"loadDynamicProperties": true
}
If you want to have details for reference columns in your objects, you can set extra parameters in request like:
POST api/publicapi/getentitylistbyquery
{
"entityType": "WorklogView",
"page": 1,
"pageSize": 20,
"loadDynamicProperties": true,
"loadExtraReferenceDetails": {"RequesterOrganizationId": "Organization", "TicketEndUserId": "Person"}
}
When you want to load extra reference details you always need to have true value on loadDynamicProperties. In parameter for load extra reference details you should send field name from object (for example TicketEndUserId) and entity name where you need to find data (for example Person).
Set which columns to fetch
Specify which exact data (fields) to fetch, in “columns” array:
{
"entityType": "SysTicket",
"page": 1,
"pageSize": 20,
"columns": ["ReferenceNo", "Priority", "BaseHeader", "AgentGroup", "CreatedDate", "BaseEntityStatus"]
}
If “columns” is null or empty, all fields will be loaded. It is recommended to only load fields that are required, this will use less system resources and the data will load faster.
Sort data
By specifying “sorts” array:
{
"entityType": "Ci",
"page": 1,
"pageSize": 20,
"sorts": [{"field": "ReferenceNo", "direction": "Asc"}],
}
Both “dir” and “direction” are valid property-names, with possible values either “asc”, “ascending”, “desc”, or “descending”.
Sort on multiple fields:
{
"entityType": "Person",
"page": 1,
"pageSize": 20,
"sorts": [{"field": "Organization", "dir": "asc"},
{"field": "LastName", "dir": "asc"},
{"field": "FirstName", "dir": "asc"}],
}
Filter query
Set a “filter”-property:
{
"entityType": "Organization",
"columns": ["OrganizationName", "City", "StreetAddress", "Phone"],
"filters": {
"logic": "or",
"filters": [
{"field": "City",
"operator": "eq",
"value": "Helsingborg"},
{"field": "City",
"operator": "eq",
"value": "Arhus"}
]
}
}
Example above returns all organization-records located either at “Helsingborg” or “Arhus”.
Filter can be of any complexity and depth:
{
"entityType": "Person",
"columns": ["FirstName", "LastName", "Email", "Phone"],
"filters": {
"logic": "and",
"filters": [
{"field": "IsVIP",
"operator": "eq",
"value": true },
{"logic": "or",
"filters": [
{"field": "Organization.OrganizationName",
"operator": "eq",
"value": "Google"},
{"field": "Organization.OrganizationName",
"operator": "eq",
"value": "Microsoft"}
]
}
]
}
Valid filter operators:
Eq (equal to), neq (not equal to), lt (less than), lte (less than or equal), gt (greater than), gte (greater than or equal), isnull, isnotnull, startswith, notstartswith, contains, notcontains, endswith, notendswith, isempty, isnotempty.
Operators applicable only for string/html fields:
startswith, notstartswith, contains, notcontains, endswith, notendswith
Valid filter logic:
Or, And
If there’s only a single filter-condition, it’s possible to assign it directly and skip the “logic-operator”. Below illustrates this, with a filter for “my tickets” (if current user has Id 1):
{
"entityType": "SysTicket",
"page": 1,
"pageSize": 20,
"columns": ["ReferenceNo", "Priority", "BaseHeader", "BaseEntityStatus", "BaseEndUser.Organization"],
"filters": {
"field": "Assignee",
"operator": "eq",
"value": 1
}
}
Search entities
If there is need to search records, developer can build a filter with contains-rules. However it is recommended to use a “search”-property and the “searchColumns”-array, and let NSP build the appropriate filter-logic:
{
"entityType": "SysTicket",
"page": 1,
"pageSize": 20,
"columns": ["ReferenceNo", "Priority", "BaseHeader", "BaseEntityStatus", "BaseEndUser.Organization"],
"search": "testing",
"searchColumns": ["BaseHeader", "BaseDescription"]
}
If search-columns is not specified, search will be performed on fields in columns-property.
Fetch inactive or deleted records
Inactive or soft-deleted records will not be included in result by default. To fetch these types of records, add parameters “fetchInactive”: true, or “fetchDeleted”: true in query data. If fetchDeleted is true, both inactive and soft-deleted records will be included.
Below is an example where both active & inactive category items are fetched:
{
"entityType": "SysDefCategory",
"fetchInactive": true
}
Below is an example where all users, including deleted, are searched:
{
"entityType": "Person",
"columns": ["Email", "FullName", "FirstName", "LastName", "Organization"],
"search": "testuser@nilex.se",
"searchColumns": ["Email"],
"fetchDeleted": true
}
Response data
Example of response json for getting lists of SysTicket:
{
"Total": 2,
"Data": [
{
"Id": 260,
"ReferenceNo": "REQ0000064",
"Priority": "Medium",
"BaseHeader": "test",
"BaseEntityStatus": "Closed",
"BaseEndUser.Organization": "Nilex AB",
"EntityTypeId": 112,
"EntityType": "Ticket",
"Priority.Id": 7,
"BaseEntityStatus.Id": 11,
"BaseEndUser.Organization.Id": 17,
},
{
"Id": 261,
"ReferenceNo":"CHG0000006",
"Priority": "Medium",
"BaseHeader": "Test change",
"BaseEntityStatus": "Released",
"BaseEndUser.Organization": "Nilex AB",
"EntityTypeId": 292,
"EntityType": "Change",
"Priority.Id": 7,
"BaseEntityStatus.Id": 17,
"BaseEndUser.Organization.Id": 17,
},
],
"Errors": null,
"IsError": false
}
Example json if you have dynamic properties:
{
"Total": 2373,
"Data": [
{
"Entity": {
"ReferenceNo": "CI0358222",
"ExternalReference": null,
"CiGroupIds": null,
"DepartmentId": 8,
"OrganizationId": null,
"SerialNumber": null,
"ImpactId": null,
"CiStateId": 1,
"FunctionId": null,
"ProductId": null,
"VendorId": null,
"SlaId": null,
"LocationId": null,
"ManagedById": null,
"UsedById": null,
"LastAuditDate": "2017-10-27T11:19:32Z",
"InventoryNumber": "8595564500124",
"InventoryNumberType": 48,
"Name": "MASTER SERVER",
"NameKey": null,
"Description": "desc",
"DescriptionKey": null,
"EntityTypeId": 4168,
"InsertUserId": 1,
"InsertTime": "2017-10-27T11:19:30Z",
"InsertIpAddress": "null",
"LastUpdateUserId": 1,
"LastUpdateTime": "2017-10-27T11:19:30Z",
"LastUpdateIpAddress": "null",
"Id": 358222,
"Status": 1
},
"DynamicProperties": {
"Procurement type": null,
"Invoice number": null,
"Purchase date": null,
"Purchase price": null,
"Residual value": null,
"Book Value": null,
"Depreciation Value": null,
"Depreciation Type": null,
"Depreciation Period": null,
"Depreciation end date": null
}
},
{
"Entity": {
"ReferenceNo": "CI0358223",
"ExternalReference": null,
"CiGroupIds": null,
"DepartmentId": 8,
"OrganizationId": null,
"SerialNumber": null,
"ImpactId": null,
"CiStateId": 1,
"FunctionId": null,
"ProductId": null,
"VendorId": null,
"SlaId": null,
"LocationId": null,
"ManagedById": null,
"UsedById": null,
"LastAuditDate": null,
"InventoryNumber": "1234fsf\n",
"InventoryNumberType": 57,
"Name": "HERCULEX PC",
"NameKey": null,
"Description": "desc",
"DescriptionKey": null,
"EntityTypeId": 4168,
"InsertUserId": 1,
"InsertTime": "2017-10-31T10:00:33Z",
"InsertIpAddress": "null",
"LastUpdateUserId": 1,
"LastUpdateTime": "2017-10-31T10:00:33Z",
"LastUpdateIpAddress": "null",
"Id": 358223,
"Status": 1
},
"DynamicProperties": {
"Procurement type": null,
"Invoice number": null,
"Purchase date": null,
"Purchase price": null,
"Residual value": null,
"Book Value": null,
"Depreciation Value": null,
"Depreciation Type": null,
"Depreciation Period": null,
"Depreciation end date": null
}
}
],
"Errors": null,
"IsError": false
}
If you set load extra reference details response will looks like:
{
"Total": 46,
"Data": [
{
"Entity": {
"LanguageId": 1,
"Id": 47,
"CustomerAccountId": 1,
"EntityTypeId": 2,
"DiscountPercent": null,
"EntityTypeName": "new type with all properties",
"BillingMethodId": 1,
"EntityReferenceNo": "REQ0000057",
"BillingMethodName": "Per Hour",
"BillingMethodNameKey": "Common.PerHour",
"EntityTypeNameKey": "WorklogTypes.newtypewithallproperties_1",
"TableRepositoryId": 159,
"AgentName": "Dzenan (Testexa) Strujic",
"AgentUrl": "/Images/userImage.png",
"TicketId": 244,
"ReferenceNo": "WL0000047",
"Subject": "test",
"EntityStatus": "Closed",
"TranslatedEntityStatus": "Closed",
"AgentId": 144,
"AgentEmail": "dzenan@testexa.local",
"TicketAgentGroupId": 1,
"TicketAgentOrganizationId": 87,
"RequesterOrganizationId": 87,
"TicketEndUserId": 444,
"TicketTypeId": 112,
"TicketStatus": 1,
"RequesterName": "Dzenan Strujic",
"RequesterEmail": "dzenan.strujic@textexa.local",
"StartTime": "2018-11-07T23:00:00Z",
"EndTime": "2018-11-08T05:00:00Z",
"DurationHours": 6,
"DurationMinutes": 0,
"CurrencyId": 1,
"CurrencyName": "EUR",
"Status": 1,
"IsCanceled": false,
"Description": null,
"AgentCost": 780,
"Discount": 0,
"IsInvoiceable": false,
"IsInvoiced": null,
"IsExported": false,
"TimeSpent": 21600,
"TimeSpentText": "06h:00m",
"MaintenanceRateTypeId": 6,
"MaintenanceRateValue": 130,
"MaintenanceRateTypeName": "Sunday",
"TotalCharge": 624,
"Remarks": null,
"InvoiceId": 0,
"InvoiceReferenceNumber": null,
"AgentCostWithDiscount": 624,
"CustomerAccountName": "DZenan test - per hour",
"EntityTypePath": "|1||2|",
"EntityTypeMembershipLevel": null,
"EntityTypeIcon": "f-nsp-manage",
"TicketTypeName": "Request",
"AdditionalCharges": 0,
"TicketAssignee": "Dzenan (Testexa) Strujic (ServiceDesk)",
"InsertUserId": 144,
"InsertTime": "2018-11-08T14:07:23Z",
"InsertIpAddress": "192.168.111.4",
"LastUpdateUserId": 144,
"LastUpdateTime": "2018-11-08T14:07:23Z",
"LastUpdateIpAddress": "192.168.111.4"
},
"DynamicProperties": {},
"ExtraReferenceDetails": {
"RequesterOrganizationIdExtra": {
"u_CustomField1": null,
"u_CustomField2": null,
"u_CustomField3": null,
"OrganizationName": "Testexa AB",
"DomainEmail": "testexa.local",
"Image": "",
"StreetAddress": null,
"CustomerNumber": null,
"PostalCode": null,
"City": null,
"Country": null,
"EmailAddress": null,
"Phone": null,
"Fax": null,
"ParentOrganizationId": null,
"TypeId": 110,
"OwnerId": null,
"Version": 6,
"BaseStatus": 1,
"CreatedDate": "2018-10-29T06:46:03Z",
"CreatedBy": 1,
"UpdatedDate": "2018-11-07T09:08:51Z",
"UpdatedBy": 86,
"IPAddress": "192.168.111.4"
},
"TicketEndUserIdExtra": {
"u_PersonalNumber": null,
"FirstName": "Dzenan",
"LastName": "Strujic",
"EMailAddress": "dzenan.strujic@textexa.local",
"Phone": "",
"MobilePhone": "",
"AddressId": null,
"Department": "-",
"JobTitle": "-",
"PersonTitleId": null,
"DateOfBirth": null,
"Image": null,
"FullName": "Dzenan Strujic",
"OrganizationId": 87,
"DefaultLanguageId": 1,
"IsVIP": false,
"VIPNotes": null,
"Room": null,
"CostCenter": null,
"MaxStorage": 0,
"Description": "-",
"Comment": "-",
"Notes": "-",
"Company": "-",
"FaxNumber": "",
"HomeFolder": "-",
"HomeDrive": "-",
"HomePhone": "",
"HomeAddress": "-",
"IpPhoneNumber": "",
"Manager": "-",
"PagerNumber": "",
"WebPageAddress": "-",
"MemberOf": "NSP_Agent",
"RoomNumber": "-",
"OfficeLocation": "-",
"ExtraCustomField1": "-",
"ExtraCustomField2": "-",
"ExtraCustomField3": "-",
"PhoneClean": null,
"MobilePhoneClean": null,
"SkypeName": "",
"LyncAddress": "Dzenan@testexa.local",
"DoNotSendEmailNotification": false,
"LyncTel": "",
"AdManagerUserId": null,
"IsActive": true,
"IsLicensed": true,
"DisplayName": "Dzenan Strujic",
"UserTypeId": 1,
"Email": "dzenan.strujic@textexa.local",
"PasswordMD5HexHash": "ad89ea12768486f9f7cef1e2c777de72",
"LastPwdDate": null,
"AuthToken": null,
"AuthTokenGeneratedOn": null,
"WindowsUserName": "dzenan",
"WindowsSidString": "S-1-5-21-296481471-456928714-2885353289-52402",
"LdapServerId": 2,
"TypeId": 111,
"OwnerId": null,
"Version": 2,
"BaseStatus": 1,
"CreatedDate": "2018-11-07T13:00:22Z",
"CreatedBy": 141,
"UpdatedDate": "2018-11-07T13:00:22Z",
"UpdatedBy": 141,
"IPAddress": null
}
}
},
{
"Entity": {
"LanguageId": 1,
"Id": 55,
"CustomerAccountId": null,
"EntityTypeId": 1,
"DiscountPercent": null,
"EntityTypeName": "Worklogs",
"BillingMethodId": 0,
"EntityReferenceNo": "REQ0000025",
"BillingMethodName": null,
"BillingMethodNameKey": null,
"EntityTypeNameKey": "Common.WorklogType",
"TableRepositoryId": 159,
"AgentName": "Dzenan (Testexa) Strujic",
"AgentUrl": "/appattachment/getattachmentresult/253",
"TicketId": 190,
"ReferenceNo": "WL0000055",
"Subject": "Dzenan Ticket 2",
"EntityStatus": "Closed",
"TranslatedEntityStatus": "Closed",
"AgentId": 140,
"AgentEmail": "dzenan@testexa.local",
"TicketAgentGroupId": 1,
"TicketAgentOrganizationId": 17,
"RequesterOrganizationId": 87,
"TicketEndUserId": 444,
"TicketTypeId": 112,
"TicketStatus": 1,
"RequesterName": "Dzenan Strujic",
"RequesterEmail": "dzenan.strujic@textexa.local",
"StartTime": "2018-11-08T01:30:00Z",
"EndTime": "2018-11-08T03:30:00Z",
"DurationHours": 2,
"DurationMinutes": 0,
"CurrencyId": 1,
"CurrencyName": "EUR",
"Status": 1,
"IsCanceled": false,
"Description": null,
"AgentCost": 20,
"Discount": 0,
"IsInvoiceable": false,
"IsInvoiced": null,
"IsExported": false,
"TimeSpent": 7200,
"TimeSpentText": "02h:00m",
"MaintenanceRateTypeId": 0,
"MaintenanceRateValue": 10,
"MaintenanceRateTypeName": "-",
"TotalCharge": 20,
"Remarks": null,
"InvoiceId": 0,
"InvoiceReferenceNumber": null,
"AgentCostWithDiscount": 20,
"CustomerAccountName": null,
"EntityTypePath": "|1|",
"EntityTypeMembershipLevel": null,
"EntityTypeIcon": "f-nsp-cube",
"TicketTypeName": "Request",
"AdditionalCharges": 0,
"TicketAssignee": "Dzenan (Testexa) Strujic (ServiceDesk)",
"InsertUserId": 140,
"InsertTime": "2018-11-08T16:06:28Z",
"InsertIpAddress": "192.168.111.65",
"LastUpdateUserId": 140,
"LastUpdateTime": "2018-11-08T16:06:28Z",
"LastUpdateIpAddress": "192.168.111.65"
},
"DynamicProperties": {},
"ExtraReferenceDetails": {
"RequesterOrganizationIdExtra": {
"u_CustomField1": null,
"u_CustomField2": null,
"u_CustomField3": null,
"OrganizationName": "Testexa AB",
"DomainEmail": "testexa.local",
"Image": "",
"StreetAddress": null,
"CustomerNumber": null,
"PostalCode": null,
"City": null,
"Country": null,
"EmailAddress": null,
"Phone": null,
"Fax": null,
"ParentOrganizationId": null,
"TypeId": 110,
"OwnerId": null,
"Version": 6,
"BaseStatus": 1,
"CreatedDate": "2018-10-29T06:46:03Z",
"CreatedBy": 1,
"UpdatedDate": "2018-11-07T09:08:51Z",
"UpdatedBy": 86,
"IPAddress": "192.168.111.4"
},
"TicketEndUserIdExtra": {
"u_PersonalNumber": null,
"FirstName": "Dzenan",
"LastName": "Strujic",
"EMailAddress": "dzenan.strujic@textexa.local",
"Phone": "",
"MobilePhone": "",
"AddressId": null,
"Department": "-",
"JobTitle": "-",
"PersonTitleId": null,
"DateOfBirth": null,
"Image": null,
"FullName": "Dzenan Strujic",
"OrganizationId": 87,
"DefaultLanguageId": 1,
"IsVIP": false,
"VIPNotes": null,
"Room": null,
"CostCenter": null,
"MaxStorage": 0,
"Description": "-",
"Comment": "-",
"Notes": "-",
"Company": "-",
"FaxNumber": "",
"HomeFolder": "-",
"HomeDrive": "-",
"HomePhone": "",
"HomeAddress": "-",
"IpPhoneNumber": "",
"Manager": "-",
"PagerNumber": "",
"WebPageAddress": "-",
"MemberOf": "NSP_Agent",
"RoomNumber": "-",
"OfficeLocation": "-",
"ExtraCustomField1": "-",
"ExtraCustomField2": "-",
"ExtraCustomField3": "-",
"PhoneClean": null,
"MobilePhoneClean": null,
"SkypeName": "",
"LyncAddress": "Dzenan@testexa.local",
"DoNotSendEmailNotification": false,
"LyncTel": "",
"AdManagerUserId": null,
"IsActive": true,
"IsLicensed": true,
"DisplayName": "Dzenan Strujic",
"UserTypeId": 1,
"Email": "dzenan.strujic@textexa.local",
"PasswordMD5HexHash": "ad89ea12768486f9f7cef1e2c777de72",
"LastPwdDate": null,
"AuthToken": null,
"AuthTokenGeneratedOn": null,
"WindowsUserName": "dzenan",
"WindowsSidString": "S-1-5-21-296481471-456928714-2885353289-52402",
"LdapServerId": 2,
"TypeId": 111,
"OwnerId": null,
"Version": 2,
"BaseStatus": 1,
"CreatedDate": "2018-11-07T13:00:22Z",
"CreatedBy": 141,
"UpdatedDate": "2018-11-07T13:00:22Z",
"UpdatedBy": 141,
"IPAddress": null
}
}
}
],
"Errors": null,
"IsError": false
}
“Data”-property will contain an array of the result records, following the paging-settings. “Total”-property will contains the total number of records that matches the criteria, regardless of paging.
All entity-records will include an “Id”-property, which is the primary key in db. Most types will also return an integer “EntityTypeId” and a string “EntityType” to further identify what kind of entity the record is classed as in system.
Please note that if querying for a base type in ticketing, any derived types will be returned also (derived means another entity type that inherits fields from base type). To remove specific derived types from result, create a filter. Above JSON-response example illustrates how derived types are returned from a query on base type “SysTicket”, where two records have the types “Ticket” and “Change”.
If there is need to include or filter on user-created fields, the entity type in query-data must either be the same where field was created or a derived type. For example, if there is a field “u_MyField” created on “Incident”-type, it is not possible to include in filter or columns when query-entity is “SysTicket” (which is base type to “Incident”), because this field is not known at “SysTicket”.