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.

warning

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.

NOTE

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:

LicenseAnalyzer™ - Level 0 (Inventory) Data Types

ID	Data Type Name	Columns
117	Dongle Data Inventory	interval time datatype dongle_type serial vendor vendor_code feature feature_code host user record_count duration
118	Product Inventory	interval time datatype product product_id feature serial release license_type domain host os installed exe_path record_count
135	License Subscription Inventory	interval time datatype vendor contract_id subscription_id product license_type max_available valid_at expired_at record_count
136	License Product Assignment	interval time datatype vendor product license_type group user record_count
139	License Subscription Inventory v2	interval time datatype product package contract subscription license_type max_available valid_at expired_at record_count
140	License Product Assignment v2	interval time datatype product package license_type user_group user record_count

Data Types' Columns

LicenseAnalyzer™ - Level 1 (Runtime Usage) Data Types

ID	Data Type Name	Columns
31	Windows Periodic Summary App Usage	interval time datatype domain host architecture os exe application exe_path primetime user exit_status record_count elapsed_time
46	Individual License Use v2.0	interval time datatype product package feature feature_version primetime user_group user record_count elapsed_time max_in_use
49	Host User License Use	interval time datatype product package feature feature_version prime_time user_group user host_group host record_count elapsed_time max_in_use
50	Host User License Use Logfile	interval time datatype product feature primetime user_group user host_group host record_count elapsed_time max_in_use
52	Denied License use Logfile	interval time datatype product feature user host status primetime record_count
53	Windows Module Usage	interval time datatype domain host architecture os module exe application exe_path primetime user record_count elapsed_time
61	Individual License Queued	interval time datatype product package feature feature_version primetime user_group user record_count elapsed_time max_queued
75	License Logfile Events	interval time datatype product feature primetime event_type user_group user host_group host details forward_product record_count events
77	Individual Queued Logfile	interval time datatype product feature primetime user_group user record_count elapsed_time max_queued
79	Internet Explorer URL Use	interval time datatype domain host architecture os url title primetime user pid exe_path record_count elapsed_time
84	Individual License Use Logfile	interval time datatype product feature primetime user_group user record_count elapsed_time max_in_use
89	Total License Use Licenseevents	interval time datatype product feature feature_version primetime group_type group user record_count duration avg_in_use max_in_use min_in_use avg_utilization max_utilization min_utilization elapsed_time
93	Olap Raw Hourly	time duration product package feature feature_version entry_version featureset featureset_version primetime user host license_type used_licenses vendor_daemon max_available license_manager record_count elapsed_time handle offline_duration exe_path pricing
94	Olap User Concurrency	interval time datatype product pacakge feature feature_version featureset user license_type record_count elapsed_time max_in_use duration
95	Usergroup License Use v3.0	interval time datatype product package feature feature_version featureset featureset_version group_type group license_type record_count elapsed_time max_in_use avg_in_use min_in_use duration
102	Olap Total Use	interval time datatype product package feature feature_version featureset featureset_version max_available used_licenses daemon_status license_manager record_count duration avg_in_use max_in_use min_in_use avg_utilization max_utilization min_utilization elapsed_time
104	License Logfile Events v2.0	interval time datatype product package feature feature_version event_type event_subtype user host details record_count events
105	Individual Module Usage	interval time datatype feature model host user primetime record_count elapsed_time
107	Denied License Use Licenseevents	interval time datatype product feature feature_version user host status primetime record_count
110	License Application Version	interval time datatype product package feature feature_version application_version user host license_manager record_count elapsed_time
112	User License Use Appversion	interval time datatype product package feature application_version featureset_version user license_type record_count elapsed_time max_in_use duration
114	Total PPU License Use	interval time datatype product package license_type feature feature_version usage_type max_available used_licenses record_count running_time avg_in_use max_in_use min_in_use elapsed_time elastic_usage
116	Total PPU License Use Logfile	interval time datatype product package license_type feature usage_type max_available used_licenses record_count running_time avg_in_use max_in_use min_in_use elapsed_time elastic_usage
119	Dongle License Use	interval time datatype host dongle_type serial vendor vendor_code feature feature_code user used_licenses max_available record_count running_time avg_in_use max_in_use min_in_use avg_utilization max_utilization min_utilization elapsed_time
120	Web App browser Log	interval time datatype application domain url title browser host user record_count
124	License Model User Concurrency	interval time datatype product feature feature_version user max_available used_licenses daemon_status licensing_model licensing_model_status licensing_model_assignment record_count running_time avg_in_use max_in_use min_in_use elapsed_time active_time
127	License Host User Usage Distinct Host	interval time datatype product package feature feature_version user_group user host_group host record_count max_in_use elapsed_time
129	FlexNet Log Events	interval time datatype product feature event_type user host details cascade_origin record_count events
130	Windows Application Usage	interval time datatype domain host architecture os exe application exe_path primetime user exit_status record_count elapsed_time
132	V-Ray License User Concurrency	interval time datatype product feature feature_version license_type category user used_licenses record_count elapsed_time max_in_use duration
137	License Token Individual Usage	interval time datatype product package feature feature_version user_group user record_count total_used
138	License Token Total Usage	interval time datatype product package feature feature_version record_count total_used

Data Types' Columns

LicenseAnalyzer™ - Level 2 (True Active Usage) Data Types

ID	Data Type Name	Columns
32	Unix Application Usage	interval time datatype domain host user group os application exit_status account label primetime record_count elapsed_time user_time system_time max_resident_size shared_memory unshared_data unshared_stack minor_page_faults major_page_faults swaps blocks_read blocks_write msg_sent msg_received signals voluntary_ctx unvoluntary_ctx
54	Extended Pacct	interval time datatype gid uid host architecture os command exit_status domain primetime source record_count cpu elapsed_time io accumulated_memory avg_memory accumulated_rss avg_rss
56	UsageAnalyzer Break	interval time datatype host architecture domain os primetime user application break_type record_count avg_break max_break total_break
57	UsageAnalyzer Work	interval time datatype host architecture domain os primetime user application period_type record_count avg_period max_period total_period
58	UsageAnalyzer Events	interval time datatype host architecture domain os primetime application user event_type modifiers label record_count events
62	License Optimizer Use	interval time datatype product feature feature_version application exe_path user_group user host_group host disable record_count elapsed_time
64	License Optimizer Events	interval time datatype product feature feature_version application exe_path user host primetime disable record_count keyboard mouse cpu io
87	Process Accounting	interval time datatype gid uid host architecture os command exit_status domain primetime application record_count cpu elapsed_time io avg_cpu_utilization avg_process_count avg_memory_utilization avg_used_rss accumulated_used_rss
92	License Optimizer Action	interval time datatype product application user host primetime action record_count
98	License Optimizer Individual Usage	interval time datatype product feature feature_version featureset featureset_version application exe_path user_group user disable record_count elapsed_time max_seen

Data Types' Columns

ComputeAnalyzer™ Data Types

ID	Data Type Name	Columns
2	LSF (Batch Jobs)	interval time datatype project user queue job_status command exit_status execution_host domain exec_cpus exec_hosts record_count queue_time system_time user_time cpu_time cpu_cost block_i block_o block_io max_rss max_vm elapsed_time total_time
4	PSM-Shell	interval time datatype host architecture os account user uid gid exit_status command record_count elapsed_time cpu_time max_rss integral_rss block_i block_o
5	LSF (LSF Acct)	interval time datatype uid host command exit_status record_count system_time user_time cpu elapsed_time block_i block_o
48	Grid Engine	interval time datatype department project account user queue job job_status execution_host resources slots record_count wait_time avg_wait_time system_time user_time cpu_time block_i block_o block_io max_rss max_vm wall_time total_time integral_memory_usage
78	LSF Pending Reason	interval time datatype project user queue pending_reason command domain requested_resource pending_hosts record_count elapsed_time
80	PBS Accounting	interval time datatype user group account queue job exit_status host resources cpu_count record_count wait_time avg_wait_time wall_time total_time cpu_time max_rss max_vm

Data Types' Columns

StorageAnalyzer™ Data Types

ID	Data Type Name	Columns
29	Filespace	interval time datatype domain host project file_system account uid gid file_type file_status read_temperature modify_temperature record_count file_count logical_size physical_size cost

Data Types' Columns

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, join, and sql.

select

The select query is used to get one or more data types. It concatenates two tables to show two data types.

Parameters

Name	Description
output	The output directory where the report will be saved.
date	The date of the report. The default value is last month.
start	The start date of the report.
end	The end date of the report. The default value is the current time.
months	The number of months to include in the report. Default is one.
tz	The timezone of the report. Default is the Core Server's local timezone.
freq	Frequency of the report: M (monthly), D (daily), Y (yearly). Default is M.
datatype	The data type(s) to include in the report.

Parameters of select query

Example

Here's an example select query:

Example

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.

SELECT query output

note

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

Name	Description
output	The output directory where the report will be saved.
date	The date of the report. Default is last month.
start	The start date of the report.
end	The end date of the report. Default is current time.
months	The number of months to include. Default is one.
tz	Timezone. Default is Core Server's local timezone.
freq	Frequency of report: M, D, Y. Default is M.
datatype	Data types to include.
on	Common column(s) between data types.
how	Join method. Default is left join. Refer to this link for values.

Parameters of join query

Example

Here's an example join query:

Example

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.

JOIN query output

sql

The sql query is used to create reports that include data that aren't stored in the database.

Parameters

Name	Description
output	Output directory of the report.
date	Date of the report. Default is last month.
start	Start date.
end	End date. Default is current time.
months	Number of months. Default is one.
tz	Timezone. Default is Core Server's local timezone.
freq	Frequency (M, D, Y). Default is M.
datatype	Data type(s) to include.
custom	Comma-separated list of custom tables (CSV files in $DATA/custom). Filename only.
id	Columns to use as index for each data type.
name	Name for each SQL query. Use this to reference tables in --query. Must match --query count.
query	SQL query to run (can be used multiple times). Last one is returned.
body	Alternative to query, accepts multi-line SQL query.

Parameters of sql query

Example

Here's an example sql query with custom table:

Example (with custom table)

http://desktop123win:8080/api/queries/sql?datatype=139&date=2022-09-01&freq=D&custom=cost&body=SELECT * FROM dt139,cost

NOTE

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.

SQL query output

Another example with multi-line SQL query.

Example (multi-line)

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

SQL multi-line query output