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