Invoke-CUQuery: Query Monitor Database

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.

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.

Before using this cmdlet, we recommend you see Monitor Cluster PowerShell API cmdlets for instructions on how to use PowerShell cmdlets.

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.

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

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.

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

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.

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.

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.

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.

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

Example 6 - Search for all machines with the name monitor

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

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.

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.