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.

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:

ID	Data Type Name	Columns
2	LSF (Batch Jobs)	project user queue job_status command exit_status execution_host domain exec_cpus exec_hosts
4	PSM-Shell	host architecture os account user uid gid exit_status command
5	LSF (LSF Acct)	uid host command exit_status
29	Filespace	domain host project file_system account uid gid file_type file_status read_temperature modify_temperature
31	Windows Periodic Summary App Usage	domain host architecture os exe application exe_path primetime user exit_status
32	Unix Application Usage	domain host user group os application exit_status account label primetime
46	Individual License Use v2.0	product package feature feature_version primetime user_group user
48	Grid Engine	department project account user queue job job_status execution_host resources slots
49	Host User License Use	product package feature feature_version prime_time user_group user host_group host
50	Host User License Use Logfile	product feature primetime user_group user host_group host
52	Denied License use Logfile	product feature user host status primetime
53	Windows Module Usage	domain host architecture os module exe application exe_path primetime user
54	Extended Pacct	gid uid host architecture os command exit_status domain primetime source
56	UsageAnalyzer Break	host architecture domain os primetime user application break_type
57	UsageAnalyzer Work	host architecture domain os primetime user application period_type
58	UsageAnalyzer Events	host architecture domain os primetime application user event_type modifiers label
61	Individual License Queued	product package feature feature_version primetime user_group user
62	License Optimizer Use	product feature feature_version application exe_path user_group user host_group host disable
64	License Optimizer Events	product feature feature_version application exe_path user host primetime disable
75	License Logfile Events	product feature primetime event_type user_group user host_group host details forward_product
77	Individual Queued Logfile	product feature primetime user_group user
78	LSF Pending Reason	project user queue pending_reason command domain requested_resource pending_hosts
79	Internet Explorer URL Use	domain host architecture os url title primetime user pid exe_path
80	PBS Accounting	user group account queue job exit_status host resources cpu_count
84	Individual License Use Logfile	product feature primetime user_group user
87	Process Accounting	gid uid host architecture os command exit_status domain primetime application
89	Total License Use Licenseevents	product feature feature_version primetime group_type group user
92	License Optimizer Action	product application user host primetime action
93	Olap Raw Hourly	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	product pacakge feature feature_version featureset user license_type
95	Usergroup License Use v3.0	product package feature feature_version featureset featureset_version group_type group license_type
98	License Optimizer Individual Usage	product feature feature_version featureset featureset_version application exe_path user_group user disable
102	Olap Total Use	product package feature feature_version featureset featureset_version max_available used_licenses daemon_status license_manager
104	License Logfile Events v2.0	product package feature feature_version event_type event_subtype user host details
105	Individual Module Usage	feature model host user primetime
107	Denied License Use Licenseevents	product feature feature_version user host status primetime
110	License Application Version	product package feature feature_version application_version user host license_manager
112	user License Use Appversion	product package feature application_version featureset_version user license_type
114	Total PPU License Use	product package license_type feature feature_version usage_type max_available used_licenses
116	Total PPU License Use Logfile	product package license_type feature usage_type max_available used_licenses
117	Dongle Data Inventory	dongle_type serial vendor vendor_code feature feature_code host user
118	Product Inventory	product product_id feature serial release license_type domain host os installed exe_path
119	Dongle License Use	host dongle_type serial vendor vendor_code feature feature_code user used_licenses max_available
120	Web App browser Log	application domain url title browser host user
124	License Model User Concurrency	product feature feature_version user max_available used_licenses daemon_status licensing_model licensing_model_status licensing_model_assignment
127	License Host User Usage Distinct Host	product package feature feature_version user_group user host_group host
129	FlexNet Log Events	product feature event_type user host details cascade_origin
130	Windows Application Usage	domain host architecture os exe application exe_path primetime user exit_status
132	V-Ray License User Concurrency	product feature feature_version license_type category user used_licenses
135	License Product Assignment	vendor contract_id subscription_id product license_type max_available valid_at expired_at
136	License Product Assignment	vendor product license_type group user
137	License Token Individual Usage	product package feature feature_version user_group user
138	License Token Total Usage	product package feature feature_version
139	License Subscription Inventory v2	product package contract subscription license_type max_available valid_at expired_at
140	License Product Assignment v2	product package license_type user_group user

Data Types' Columns

Queries

There are three available queries that can be used 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

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

Example

http://desktop123win:8080/api/queries/select?datatype=102,75&freq=D&date=2020-10-01

This example generates a report showing data from data types 102 and 75 for October 1, 2020.

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

Example

http://desktop123win:8080/api/queries/join?datatype=94,75&on=datetime,product,package&date=2021-06-21&freq=D

This example joins data from data types 94 and 75 using datetime, product, and package for June 21, 2021.

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

Example

http://desktop123win:8080/api/queries/sql?datatype=139&date=2021-06-21&freq=D&custom=cost

This combines data type 139 for June 21, 2021, with a custom table named cost.

NOTE

Please ensure that the custom table name is accurate and located in the $DATA_DIR/custom directory of the Core Server.