Get Data using Query

To view data stored in multiple tables or to fetch data which is highly complex and requirement driven, use the getdata API. In addition, you need to use the right Query which has to be executed from Body tab. Use the POST method to get data for complex scenarios.

Example1 of Query passed in Body Tab:

{
    "RawSQL": "SELECT workgroup_name, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage FROM mart_im_ticket_master WHERE status IN ('Closed', 'Resolved', 'Completed', 'Cancelled', 'Rejected') GROUP BY workgroup_name LIMIT 1000 OFFSET 0"
}

Example2 of a Join Query passed in Body Tab:

select location_id,customer_id,tenant_id,C.rating

  FROM mart_sr_service_ticket_master AS A

  LEFT OUTER JOIN mart_sr_service_ticket_feedback_details AS B

    ON A.ticket_id = B.ticket_id

  LEFT OUTER JOIN mart_sr_service_ticket_feedback AS C

    ON C.ticket_id = A.ticket_id

    where A.location_id is not null and A.customer_id is not null

    and location_id not in (5527)

To fetch data based on a Query, perform the following steps:

Steps

1. Copy the Token generated without the comma or quotes.

2. Go to Postman or any other API tool.

3. Select Post method from the drop-down.

4. Specify the URL as https://<novatech.com>/api_report/API/getdata 

5. Click on Authorization tab and select Bearer and paste the Token.

6. Click Body tab and specify the query based on which you want to fetch the data. Query based method is very useful when you want to retrieve data based on complex requirements. Such as, you want to query using ‘Select, JOINs across multiple tables, or use WHERE, Group by, Order by options.

   1. Allowed statements in Query:

      1. SELECT statements

      2. JOINs across reporting tables

      3. WHERE, GROUP BY, ORDER BY

   2. Not Allowed in Query:

      1. INSERT,

      2. UPDATE

      3. DELETE

      4. COMMIT

7. Click Send.

Response

{
        "pagination": {
        "page_size": 100,
        "prev_offset": -1,
        "current_offset": 0,
        "next_offset": -1,
        "prev_page": -1,
        "current_page": 0,
        "next_page": -1,
        "total_pages": 0,
        "total_items": 0,
        "prev_page_url": null,
        "current_page_url": null,
        "next_page_url": null
        },
        "results": [
        {
        "workgroup_name": "Helpdesk",
        "percentage": 36.0000000
        },
        {
        "workgroup_name": "Application Support",
        "percentage": 58.0000000
        },
        {
        "workgroup_name": "Infrastructure Helpdesk",
        "percentage": 1.3333333
        },
        {
        "workgroup_name": "Exchange Server",
        "percentage": 2.0000000
        },
        {
        "workgroup_name": "Employee services team",
        "percentage": 2.6666667
        }
        ],
        "Status": {
        "message": "",
        "status": "success",
        "isValid": true,
        "warning": "",
        "Id": 0
        }
        }