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

These queries can be used to create reports using the following 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

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
  • </ul
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

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

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

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. 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://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

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. 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://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

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

# Examples

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

This example combines the data type (139) License Subscription Inventory v2 for September 1, 2022, with a custom table called cost.

SQL query output

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

SQL multi-line query output