Export-CUQuery: Query and Export Monitor Database

The Export-CUQuery PowerShell cmdlet queries the current state of the monitor cluster and exports the results to a CSV or JSON file. The difference between this cmdlet and the Invoke-CUQuery cmdlet is that Export-CUQuery exports query results to a CSV or JSON file, rather than to PowerShell.
Use Invoke-CUQuery to query a dataset less than 100 (cmdlet limit). If the result set is expected to be more than 100, we recommend you use Export-CUQuery to reduce the load on the monitor cluster.

From version 9.0 and higher, you can use the Export-CUQuery cmdlet with cmdlets for environment configuration.

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

Prerequisites

• At least one ControlUp Monitor installed to import the ControlUp.PowerShell.User module

Syntax

Export-CUQuery
[[-OutputFolder] <string>] 
[[-Scheme] <string>] 
[-Table] <string> 
[-Fields] <string[]> 
[[-Where] <string>] 
[[-Focus] <string>] 
[[-Search] <string>] 
[[-SearchField] <string>] 
[[-NodeId] <string>] 
[[-TranslateEnums] <SwitchParameter>] 
[[-FileName] <string>] 
[[-FileFormat] <int>] [<CommonParameters>]

Parameters

OutputFolder

Defines the local or network path to the folder to write the output export file. If you don't provide this parameter, the current PowerShell directory is used.

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

Examples
Define output folder explicitly:

Export-CUQuery -OutputFolder "c:\temp\exports" -Table Computers -Fields

Use current directory for output:

Export-CUQuery -Table Computers -Fields

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 do not specify a scheme, the Main scheme is used per default.

Parameter Syntax
Type: String
Mandatory: No
Position: 2
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: 3
Default value: N/A
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:

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

Parameter Syntax
Mandatory: Yes
Position: 4
Default value: N/A
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: 5
Default value: N/A
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.

Parameter Syntax
Type: String
Mandatory: No
Position: 6
Default value: N/A
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.

Parameter Syntax
Type: String
Mandatory: No
Position: 7
Default value: N/A
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: 8
Default value: N/A
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: 9
Default value: N/A
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").

Parameter Syntax
Type: SwitchParameter
Mandatory: no
Position: 10
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

FileName

Defines the file name for the export file. If you don't provide this parameter, the file name is generated via the following pattern from the queried table and the current date and time: Table_2022_03_28_13_30_45.

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

Examples
Define output file name explicitly:

Export-CUQuery -FileName computers.csv -Table Computers -Fields 

Use default output file name:

Export-CUQuery -Table Computers -Fields 

FileFormat

Defines the format of the output file. You can either use “CSV“ or “JSON”. If you don't provide this parameter, CSV format is used by default.

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

Examples
Define CSV file format explicitly:

Export-CUQuery -FileFormat csv -Table Computers -Fields

Define JSON file format:

Export-CUQuery -FileFormat json -Table Computers -Fields

Use default CSV file format:

Export-CUQuery -Table Computers -Fields

Cmdlet Results

When you run Export-CUQuery successfully it will show a result set, as described below.

• FullFilePath. Full path to the output file with export results.
• 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 exported by the query.
• Returned. Number of results exported. Total and Returned values are always the same because all results are exported.
• Stats. Provides diagnostic information about query statistics.
• CurrentTime. The date and time when query was executed.
• RequestId. ID of the request, which can be used for tracking and logging.

Examples

The following examples show how to use Export-CUQuery. We use examples from the Invoke-CUQuery cmdlet with the three parameters specific to Export-CUQuery.

Example 1 - Get all monitored machines and save result set to specific folder

(Export-CUQuery -OutputFolder C:\temp -Table Computers -Fields *).Data

This command creates a CSV file that contains information about machines. The OutputFolder parameter defines the location of the exported file. The file that is created is a CSV file with the file name Table_YYYY_MM_DD_HH_mm_ss.csv.

Example 2 - Get all running monitored processes and save result to a JSON file

(Export-CUQuery -OutputFolder C:\temp -Table Processes -Fields * -FileName AllProcesses -FileFormat Json).Data

As mentioned above, the only difference between the Invoke-CUQuery and Export-CUQuery cmdlets are the parameters OutputFolder, FileName and FileFormat. All are used in this example which exports information about all processes to a AllProcesses.json file in the C:\temp folder.

Example 3 - Search for a process name and export all information related to this process

Export-CUQuery -Fields * -Table Processes -FileName ProcessesInfo -Search chrome -SearchField sName

This example shows how to search for a string in a particular field. Here, we search for chrome in the sName field in the Processes table. The ProccessInfo.csv file contains data for the results found by this command.

Example 4 - Query all user sessions (without system sessions)

In this example, all user sessions are exported, excluding system sessions.

Export-CUQuery -Scheme Main -Table Sessions -Fields iSessionID,sUserAccount -TranslateEnums -Where "sUserAccount!=''"