Filters - cfxdm - dm:filter

Filtering related cfxdm:filter tag functionality

dm:filter: This cfxdm bot allows the user to apply basic simple to complex filters on the retrieved data from a different extension/data source using CFX query language.

This bot is useful when an extension /data source does not support filtering the data at the source using cfx query language. So, the pre-requisite is to retrieve the data before using this (dm:filter) bot.

dm:filter syntax:

• dm:filter <cfx-ql-query> : Use CFX query language to apply the filter. If you do not want to apply any query and to include all of the ingested data, use '*' (without any quotes).

• dm:filter <cfx-ql-query> get COLUMN_Name_1, COLUMN_Name_2, COLUMN_Name_3 .... : Use this syntax to limit the scope to only the selected columns. It also maintains the selected column's specified order.

• dm:filter <cfx-ql-query> get COLUMN_Name_1 as 'New_COLUMN_Name_1', COLUMN_Name_2 as 'New_COLUMN_Name_2', COLUMN_Name_3 as 'New_COLUMN_Name_3'... : Use this syntax to limit the scope to only the selected columns and rename them with new column names. It also maintains the selected column's specified order.

Please refer CFX query language section for detailed information on supported queries and their usage/syntax with examples.

The current example will use "surveys.csv" provided as a dataset to explain the functionality.

Step 1: Download the "surveys.csv" file to the local machine as shown below using a standard web browser.

Step 2: Upload (load) the above file into your RDA system as shown below.

• Using a web browser, connect to the RDA system <https://<rda-ipaddress>:9998>

• Click on the CFXDX Python 3 icon from the main landing page of the RDA launcher (as shown below)

• Launch AIOps studio using the RDA provided text field to start AIOps studio.

Type "studio()" into the text field of RDA to load AIOps studio UI

Step 3: Upload the above downloaded CSV file into AIOps studio as shown below.

Select the upload option from the studio environment (as shown below).

Select the file and upload it into the studio environment (as shown below).

Select 'surveys.csv' file for upload

The studio will show the uploaded file in the left panel (as shown below).

Uploaded file is visible on the left panel of studio environment

Step 4: Create an empty dataset 'cfxdm-basic-filter-example" pipeline and save that in AIOps studio and get the data from surveys.csv file using *dm: filter

Create a empty cfxdm pipeline

Example 1: This example explains RDA 'dm:filter' functionality to filter selective columns/data from the entire dataset.

Step a: Get the data from surveys.csv using 'dm:filter'. Below shown columns (highlighted) are from the surveys.csv file. The user selects only the following filtered columns instead of a complete set of columns.

• record_id

• plot_id

• year

• month

• other columns

Note --> symbol represents piping the data between two extension bots, the output of an extension bot becomes an input to another extension bot. It is similar to using the pipe (I) command in Unix/Linux OS.

Step b: Add the following pipeline code/commands into the empty pipeline text field that you have created in the above step.

You can copy the below code into your pipeline and execute that in your environment. ##### This pipeline loads surveys.csv file into AIOps studio stores ##### the data loaded from surveys.csv file into local dataset named ##### 'cfxdm-basic-filter'. Once the data is saved in 'cfxdm-basic-filter' ##### dataset, RDA functionality '*dm:filter' is used filter only few ##### names from the complete dataset that was read from csv file. @files:loadfile filename = "surveys.csv" --> @dm:save name = 'cfxdm-basic-filter' --> *dm:filter * get record_id,plot_id, month,year

Step c: Verify the pipeline code using the 'Verify' button on AIOPs studio.

Verify the pipeline code via Studio. Studio will print OK after validation.

Step d: Execute the pipeline code using the 'Execute' button on AIOPs studio as shown below.

Step e: Verify the dm:filter functionality is filtering the dataset and prints the selected columns as shown below.

AIOps Studio --> Studio --> Execute --> Inspect --> Data --> Get Data (button)

Example 2: This example explains RDA 'dm:filter' functionality to filter selective columns/data from the entire dataset. In addition, this showcases how to dynamically rename the existing dataset columns to user defined column names.

Step a: Get the data from surveys.csv and modify the column names. Below shown columns (highlighted) are from the surveys.csv file. Now, we will use RDA's 'dm:filter' mechanism to select only few columns and rename those into custom column names.

• record_id

• plot_id

• year

• month

• other columns

Step b: Add the following pipeline code/commands into the empty pipeline text field that you have created in the above step.

You can copy the below code into your pipeline and execute that in your environment. ##### This pipeline loads surveys.csv file into AIOps studio stores ##### the data loaded from surveys.csv file into local dataset named ##### 'cfxdm-filter-with-column-rename'. Once the data is saved in ##### 'cfxdm-filter-with-column-rename' dataset, RDA functionality ##### '*dm:filter' is used to filter only few columns/names from ##### the complete dataset that ##### was read from csv file and rename #### those with user defined names as follows. @files:loadfile filename = "surveys.csv" --> @dm:save name = 'cfxdm-basic-filter' --> *dm:filter * get sex as 'Gender',record_id as 'Record_Indentifier', plot_id as 'Plot_Identifier', month as 'Month', year as 'Year'

Step c: Verify the pipeline code using the 'Verify' button on AIOPs studio.

Verify the example pipeline code via Studio

Step d: Execute the pipeline code using 'Execute' button on AIOPs studio as shown below.

Execute the pipeline without any error

Step e: Verify the dm:filter functionality is filtering the dataset and prints the selected columns as shown below.

AIOps Studio --> Studio --> Execute --> Inspect --> Data --> Get Data (button)

End-Output after pipeline execution with renamed column names

Example 3:

Get the data from 'survey.csv' data file and using CFX query language, filter the data that matches 'Male' under column 'Gender' and limit the column's scope only to Year '1983'

Step a: Get the data from surveys.csv and modify the column names using filtering mechanism to get columns with conditional logic using CFXQL query to select data with column 'Gender' is 'M' and limit the year to '1983.

Step b: Add the following pipeline code/commands into the empty pipeline text field that you have created in the above step.

You can copy the below code into your pipeline and execute that in your environment. ##### This pipeline loads surveys.csv file into AIOps studio stores the #### data loaded from surveys.csv file into local dataset named #### 'cfxdm-filter-with-column-logic'. Once the data is saved in #### 'cfxdm-filter-with-column-logic' dataset, RDA functionality #### '*dm:filter' is used to filter the columns with user defined #### logic (of Gender is 'M'and Year is '1983') @files:loadfile filename = "surveys.csv" --> @dm:save name = 'cfxdm-basic-filter' --> *dm:filter * get sex as 'Gender',record_id as 'Record_Indentifier', plot_id as 'Plot_Identifier', month as 'Month', year as 'Year' --> *dm:filter Gender equals 'M' & Year equals '1983'

Step c: Verify the pipeline code using the 'Verify' button on AIOPs studio.

RDA Validates pipeline code and prints status 'OK'

Step d: Execute the pipeline code using the 'Execute' button on AIOPs studio as shown below.

RDA executes the above mentioned pipeline without any errors.

Step e: Verify the dm:filter functionality is filtering the dataset and prints the selected columns as shown below.

AIOps Studio --> Studio --> Execute --> Inspect --> Data --> Get Data (button)

After pipeline execution, RDA filters data based on the user defined logic Gender is 'M' & Year is '1983'

Users can download the incidents.csv file and explore the 'dm:filter' functionality.

Follow the above-mentioned Step 1, Step 2, Step 3 to upload the 'incidents.csv' file to AIOps studio. Once you upload the file into AIOps studio, you will see the file under the left panel of AIOps studio (as shown below).

incidents.csv file is visible on the left panel of AIOps studio

Example 4:

Step a: Create an empty dataset and load incidents.csv file into AIOps studio

Step b: Add the following pipeline code/commands into the empty pipeline text field that you have created in the above step.

You can copy the below code into your pipeline and execute that in your environment. #### This pipeline loads incidents.csv file into AIOps studio stores the data #### loaded from incidents.csv file into local dataset named 'cfxdm-incidents'. #### Once the data is saved in 'cfxdm-incidents' dataset, RDA functionality #### '*dm:filter' is used filter #### only few names from the complete #### dataset that was read from csv file. @files:loadfile filename = "incidents.csv" --> @dm:save name = 'cfxdm-incidents'

This is depicted in the following screen capture.

Pipeline code added to AIOps studio new pipeline 'cfxdm-incidents-example'

Step c: Verify the above pipeline code and execute the pipeline. AIOps studio will verify the pipeline code and execute the pipeline as shown below.

AIOps Studio --> Studio -->Verify

Pipeline validation using AIOps studio

AIOps Studio --> Studio --> Execute

Succefull execution of cfxdm-incidents-example pipeline.

Step d: Verify the data from the execution of the pipeline as shown below.

AIOps Studio --> Studio --> Execute --> Inspect --> Data --> Get Data (button)

Step e: Users can now access the metadata from 'incidents.csv' to query data/meta-data from the loaded dataset. In addition, can query and manipulate data using dm: filter functionality.

i. Accessing metadata for the above-loaded dataset is shown in the below screen capture.

Above screen capture shows the metadata details of cfxdm-incidents dataset.

ii. Following example explains a simple use case that provides the above-explained dataset along with renaming of columns followed by filtering user required data.

Add the following pipeline code/commands into the empty pipeline text field that you have created in the earlier steps to query only two columns using the *dm:filter option.

You can copy the below code into your pipeline and execute that in your environment. ##### This pipeline loads incidents.csv file into AIOps studio stores ##### the data loaded from ##### incidents.csv file into local dataset ##### named 'cfxdm-incidents'. Once the data is saved in 'cfxdm-incidents' ##### dataset, RDA functionality '*dm:filter' is used filter #### only few names from the complete dataset that was read from csv file. @files:loadfile filename = "incidents.csv" --> @dm:save name = 'cfxdm-incidents' --> @dm:map from = 'Incidents ID' & to = 'cfxdm-incidents' --> *dm:filter Status equals 'Resolved' & Source not equals 'Grafana' & Priority = '1 - Critical'

Additional logic added to pipeline code to filter and query only two columns

Users can copy the above pipeline code and execute to retrieve data for only two columns 'Incident_ID' that is renamed as 'Ticket' and Summary from the complete incident list. This is shown in the following screenshot.

*dm:filter filters out rest of the columns and outputs only selected renamed column data.

In addition to the above examples, users are free to explore various ticketing-based queries using the above incidents.csv file and/or using the logically stored datasets within RDA/AIOps studio.