Hello friend!

The beta release of Open iT version 10.1 is now available!
Try it out and help us enhance your Open iT experience. Contact support@openit.com to get started.

# 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.

Make sure that there is sufficient data on the Core Server before generating queries.

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. If not specified, the default value is one.
tz	The timezone of the report. The default value is the local timezone of the Core Server.
freq	The frequency of the report. The default value is M (monthly). Aside from the default value, this parameter also accepts D (daily) and Y (yearly) values.
datatype	The data type(s) to include in the report.

Parameters of select query

# Example

Here's an example select query:

Example
http://mnl3174win: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. 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. If not specified, the default value is one.
tz	The timezone of the report. The default value is the local timezone of the Core Server.
freq	The frequency of the report. The default value is M (monthly). Aside from the default value, this parameter also accepts D (daily) and Y (yearly) values.
datatype	The data types to include in the report.
on	The common column between the data types.
how	The default method is left join. Use this argument to change the method to join data. Kindly refer to this link for other values of the parameter.

Parameters of join query

# Example

Here's an example join query:

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

This example demonstrates how to generate a report by combining data from data types 94 and 75, using the date, product, and package information 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	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. If not specified, the default value is one.
tz	The timezone of the report. The default value is the local timezone of the Core Server.
freq	The frequency of the report. The default value is M (monthly). Aside from the default value, this parameter also accepts D (daily) and Y (yearly) values.
datatype	The data type(s) to include in the report.
custom	The comma-separated list of custom tables to load. The custom tables are CSV files inside the $DATA/custom folder. Use the filename without including the file extension.
id	The comma-separated list of columns to use as the index for each data type loaded.
name	The name of each SQL query. Each SQL query will be registered as a table with this name so you can reference it in other SQL query in `--query`. This can be specified multiple times and must be the same length as `--query`.
query	The SQL query to run. This can be specified multiple times. The last query will be returned as the output.
body	The SQL query to run. This is a multi-line SQL query that can be used as an alternative to `query`.

Parameters of sql query

# Example

Here's an example sql query with custom table:

Example
http://mnl3174win:/40152/queries/sql?datatype=139&date=2021-06-21&freq=D&custom=cost

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

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