Invoke-CUQuery: Query Monitor Database
    • Dark
      Light
    • PDF

    Invoke-CUQuery: Query Monitor Database

    • Dark
      Light
    • PDF

    Article summary

    The Invoke-CUQuery PowerShell cmdlet allows you to interact with your monitor cluster programmatically. You can easily query information about data sources such as virtual machines, hypervisors, cloud connections or EUC environments using just one PowerShell command. As the Invoke-CUQuery cmdlet does not manipulate any data, you can safely use it in your scripts or as an ad-hoc query in a PowerShell session.

    From version 9.0, we use virtual tables to query and store the monitor data, with no need to store the data in the monitor database. You can query either data from data sources connected to the monitor cluster or data from monitors themselves. Note that the Invoke-CUQuery cmdlet can't display data for data sources that are excluded from the monitor.

    From version 9.0, you can use the Invoke-CUQuery cmdlet with API Keys and 9.0 PowerShell cmdlets. For instructions on how to use PowerShell cmdlets, we recommend you see here.

    In a Nutshell

    • The result set returned from Invoke-CUQuery is limited to 100. Although you can increase the limit with the Take parameter, we recommend to use the Export-CUQuery for queries that are expected to return a data set larger than 100.
    • Data is returned as PowerShell objects. This means you can further process the Methods and Properties of those objects in your scripts.
    • For permissions checking, the ControlUp Monitor uses the UPN of the current user when invoking the Invoke-CUQuery cmdlet. Results that aren't accessible to the current user won't be returned by this cmdlet. Ensure that the current user has sufficient permissions to read requested objects.
    Known Issue

    After you update the capacity of a monitor in the ControlUp Real-Time Console, the monitor won't show the new capacity in results from the Invoke-CUQuery cmdlet or in Splunk. You must restart the Master Monitor in your monitor cluster to apply the new capacity in Invoke-CUQuery and Splunk.

    Prerequisites

    • ControlUp version 8.6.5 or higher
    • At least one ControlUp Monitor installed

    Get All Available Schemas and Tables

    From version 8.8 and higher, you can query schema names and tables by using the following commands:

    To retrieve all schemas:

    (Invoke-CUQuery -Scheme Information -Fields SchemaName -Table Schemas).data
    

    To retrieve all tables:

    (Invoke-CUQuery -Scheme Information -Fields SchemaName, TableName -Table Tables).data 
    

    Syntax

    Invoke-CUQuery
        [[-Scheme] <string>]
        [-Table] <string> 
        [-Fields] <string[]
        [[-Sort] <string>] 
        [[-SortDir] {Asc | Desc}] 
        [[-Where] <string>] 
        [[-Take] <int>] 
        [[-Skip] <int>] 
        [[-Focus] <string>] 
        [[-Search] <string>]
        [[-SearchField] <string>] 
        [[-NodeId] <string>] 
        [[-TranslateEnums] <SwitchParameter>] [<CommonParameters>]
    

    Parameters

    Scheme

    Scheme is the name of the index the table is queried on. There are several indices for internal use of the cluster. The "Main" index holds all ControlUp entities. If you don't specify a scheme, the Main scheme is used per default.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 1
    Default value: Main
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Table

    Tables are logical containers for ControlUp entities. This parameter is mandatory and you can find all values here.
    The logic is as follows: If you are looking for data pertaining to processes, you should use -table Processes. To retrieve information about monitored sessions, use -table Sessions, etc.

    Parameter Syntax
    Type: String
    Mandatory: Yes
    Position: 2
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Fields

    The fields argument represents the column name of a specific table. Refer to the column reference table in the ControlUp Monitor: PowerShell Cmdlet Overview article to see which arguments you can use in the Fields parameter.

    Another way to see which fields are available for a specific table is to use an asterix (*) after the Fields parameter. For example, to see all the fields of the Computers table, you can use:

    (Invoke-CUQuery -Table Computers -Fields *).Data | get-member -MemberType NoteProperty
    

    SeeAllFields

    Parameter Syntax
    Mandatory: Yes
    Position: 3
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Sort

    Use the Sort parameter to sort your query results. When you sort by a specific field, make sure you specific it in the Fields parameter. Example 2 shows how to use the Sort parameter.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 4
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    SortDir

    Allowed values are asc for ascending order and desc for descending order. There is no support for multi-column sort.

    Parameter Syntax
    Type: SortDirection
    Mandatory: No
    Position: 5
    Possible values: Asc, Desc
    Default value: Asc
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Where

    Use the Where parameter if you want to specify a filter for query results. You can use the following logical operators: OR, AND, NOT. In comparison to operators, you must enclose string values in single quotes.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 6
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Take

    The Take parameter specifies the number of query results to return. By default, the result is limited to 100. If you need to display more than 100 results you can provider a higher value in the Take parameter, or you use the Export-CUQuery cmdlet, which is designed to return a higher number of results.

    Parameter Syntax
    Type: Integer
    Mandatory: No
    Position: 7
    Default value: 100
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Skip

    You can specify the offset to return by using the Skip parameter. 0 corresponds to the first result, 1 to the second, etc.

    Parameter Syntax
    Type: Integer
    Mandatory: No
    Position: 8
    Default value: 0
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Focus

    With the Focus parameter you can define a folder name in your ControlUp organization tree to show results only from a specific folder. If no value is provided, all objects from your ControlUp organization tree are returned. To learn how to use the Focus parameter, see Example 5.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 9
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    Search

    The Search parameter specifies a filter for text fields. Use an asterix (*) to match a sequence of any characters (including empty string). The parameter is case-insensitive. Example 6 provides a simple use case for the Search parameter.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 10
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    SearchField

    If you want to search your result set, use the Search parameter. If you are using multiple selectors in the Fields parameter, then using the Search parameter without SearchField might not be the right choice for your use case. A simple definition: You search for a string (Search argument) in a specific field (SpecificField argument).

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 11
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    NodeId

    You can query the internal ID of a specific monitor node by using the NodeId parameter.

    Parameter Syntax
    Type: String
    Mandatory: No
    Position: 12
    Default value:
    Accept pipeline input: True (ByPropertyName)
    Accept wildcard characters: False

    TranslateEnums

    The TranslateEnums parameter converts an enumerator into a human-readable format. In some cases the connection states are shown as integers (e.g. 0 for "Disconnected" or 1 for "Connected"). Example 7 shows how to use this parameter.

    Parameter Syntax
    Type: SwitchParameter
    Mandatory: No
    Position: 13
    Default value: False
    Accept pipeline input: False
    Accept wildcard characters: False

    Cmdlet Results

    When you run Invoke-CUQuery, the command either runs successfully or fails. A successful query will show a result set, as described below.

    CmdLetResults

    • Data. The data returned as an array.
    • Success. The success or failure of a query is displayed in the boolean flag. Since an exception will be thrown in case of an error, the value will always be True.
    • Error. Error description. The value is always an empty string as an exception is thrown in the case of an error.
    • ErrorCode. The value is always 0 as an exception is thrown in case of an error.
    • Total. Total number of results returned by the query.
    • Returned. Number of results returned by the query. If pagination parameters are used (Take, Skip) or the total number of results is greater than 100, the value of the Returned property will be smaller than the value of the Total property.
    • Stats. Provides diagnostic information about query statistics.
    • CurrentTime. The date and time when the query was executed.
    • RequestId. The ID of the request, which can be used for tracking and logging.

    Examples

    This section provides examples of how to use Invoke-CUQuery. In the first three examples, we use one-liners to teach you the basics.

    As long as the Invoke-CUQuery cmdlet is used as one command, the key property is always displayed if you query the Data object (defined by .Data after the parenthesis). You have no practical use for the key property since it's used internally by the monitor.

    Example 1 - Display all machines with more than 2 CPUs

    (Invoke-CUQuery -Table "Computers" -Fields "sName", "iCPUCount" -Where "iCPUCount>2").Data
    

    This example demonstrates how to use the Where clause to filter a field selected in the -Fields argument.

    Example1_MachinesWithMoreThan2CPUS

    Example 2 - Display all machines and sort by Name

    (Invoke-CUQuery -Table Computers -Fields sName -Sort sName -SortDir asc).Data
    

    You can use the -SortDir parameter to sort the result set. Here, we sort the host names of all machines connected to the monitor.

    Example2_SortByMachineName1

    Example 3 - Check the running EXE version of a process

    (Invoke-CUQuery -Table "Processes" -Fields "sEXEVersion", "sServerName", "sName" -Where "sName = 'cuAgent.exe' AND sEXEVersion != '8.7'" -sortdir Asc).Data
    

    In this example, we filter all cuAgent.exe processes that are not running the latest 8.6.5.389 version. This is just one of many use cases that helps you identifying which program versions your users are running.

    Example3_CheckProcessExe

    Example 4 - Show all disconnected machines and display error reason

    $machines = (Invoke-CUQuery -Fields "sName","FQDN","ConnectionError" -Scheme "Main" -Table "Computers").data
    $Disconnected = $machines|?{$_.ConnectionError -ne $_.FQDN -and $_.ConnectionError -notlike "Outdated Agent Version*"}
    $connected = $machines|?{$_.ConnectionError -eq $_.FQDN -or $_.ConnectionError -like "Outdated Agent Version*"}
    cls
    write-host "`nTotal Machines: $($machines.count) (" -nonewline
    if($connected.count){write-host "$($Connected.count) Connected" -foreground green -nonewline}
    if($connected.count -and $Disconnected.count){write-host " / " -nonewline}
    if($Disconnected.count){write-host "$($Disconnected.count) Disconnected" -foreground red -nonewline}
    write-host ")"
    write-host "`n========================`nDisconnected Machines`n========================`n"
    write-host $($Disconnected|Select sName, ConnectionError|Out-String) -foreground red
    

    This script demonstrates the powerful capabilities of the Invoke-CUQuery cmdlet. First, we query the host name, FQDN and connection error of all machines connected to the monitor cluster. After that, a filter for disconnected and connected machines is applied - by ignoring connection errors Outdated Agent Version.

    Example4_ShowDisconnectedMachines

    Example 5 - Display machines from a specific folder in the organization tree

    (Invoke-CUQuery -Table Computers -Fields sName, FQDN -Focus "ControlUp Demo\IL Datacenter").Data
    

    Use the Focus parameter to define the organization and folder.

    Example5_ShowComputerFromSpecificFolder

    In our example, ControlUp Demo is the name of the organization and IL Datacenter the name of the folder.
    To easiest way to check the path of the folder is to open the Real-Time Console, right click a folder > Properties and copy the Path (case insensitive).

    Example5_CheckPathOfFolder

    Example 6 - Search for all machines with the name monitor

    (Invoke-CUQuery -Scheme Main -Table Computers -Fields sName -Search '*monitor*').Data
    

    Example_SearchForAllMachinesWithMonitorPattern

    Example 7 - View status of all user sessions in human-readable form

    (Invoke-CUQuery -Scheme Main -Table Sessions -Fields sServerName, iSessionID, eConnectState -TranslateEnums -sort iSessionID -SortDir desc).Data
    

    This examples shows how to use the -TranslateEnums parameter. It adds the state of the connection inside parentheses so that you can easily interpret it. The parameter can only be used with enumerators.

    Example6_SeeUserSessions

    Example 8 - Search a computer name and show all information for that computer

    (Invoke-CUQuery -Table Computers -Fields FQDN -Search '*Monitor*.controlup.demo' -SearchField FQDN).Data
    

    In this command, we search for an FQDN that contains the string "Monitor" in the controlup.demo domain, and we output all FQDNs matching the search.

    image.png


    Was this article helpful?