We’re upgrading your documentation experience!
A new doc website is live at docs.openit.cloud — a temporary preview during our transition. This website is now in maintenance-only mode (only critical issues will be addressed).
Explore the new website and send us your feedback!
#
Open iT Queries
The Open iT queries allow users to generate Analysis Server-like reports without Analysis Server installation. It also connects directly to the Core Server flat data files for report creation to avoid loading a massive amount of data in the SQL server, which may cause delays.
This dataset is not intended to serve as a ready-made report. Instead, it acts as a data source for generating reports. Additional data formatting and manipulation may be required to achieve the desired output.
Make sure that there is sufficient data on the Core Server before generating queries.
These queries can be used to create reports using the following data types:
To understand the meaning of each column for a specific data type, please refer to the corresponding data types pages. These descriptions can help you determine how to interpret and manipulate the data as needed to generate your desired reports.
#
Queries
There are three available query types that you can use to generate custom Analysis Server–like reports:
#
select
The select query is used to get one or more data types. It concatenates two tables to show two data types.
#
Parameters
#
Example
Here's an example select query:
http://desktop123win:8080/api/queries/select?datatype=49&freq=D&date=2025-06-01
This example generates a report showing data from data type (49) Host User License Use for June 1, 2025.
An additional column, datetime, containing the current date (i.e., the date when the query was made) will be automatically appended to the end of every SELECT query output.
#
join
The join query is used to combine data types based on their common column. This query uses left join
by default.
#
Parameters
#
Example
Here's an example join query:
http://desktop123win:8080/api/queries/join?datatype=139,140&on=datetime,interval,time,product,package,license_type&date=2022-09-01&freq=D
This example demonstrates how to generate a report by combining data from data types (139) License Subscription Inventory v2 and (140) License Product Assignment v2, using the date, interval, product, package, and license type information for September 1, 2022.
#
sql
The sql query is used to create reports that include data that aren't stored in the database.
#
Parameters
#
Examples
Here's an example sql query with custom table:
http://desktop123win:8080/api/queries/sql?datatype=139&date=2022-09-01&freq=D&custom=cost&body=SELECT * FROM dt139,cost
Please ensure that the custom table name is accurate and located in the $DATA_DIR/custom
directory of the Core Server.
This example combines the data type (139) License Subscription Inventory v2 for September 1, 2022, with a custom table called cost.
Another example with multi-line SQL query.
http://desktop123win:8080/api/queries/sql?datatype=139,140&date=2021-06-21&freq=D&id=datetime,product,package&body=WITH x AS (SELECT id, COUNT(DISTINCT dt140.user) as distinct_user FROM dt140 GROUP BY id) SELECT datetime, product, package, max_available, x.distinct_user FROM dt139 JOIN x ON dt139.id = x.id