Using Nested Queries

Nested Query functionality enables one query to refer to results from another query. This section describes how to set up and use nested queries for the three supported scenarios:

Outer CMDB Query, Inner Event Query

The following generalized steps describe how to create a nested query where the outer query targets CMDB and the inner event query targets events.

If you want to reuse an existing query, then skip Step 1 and go to Step 2. Note that for a nested query to work correctly, the data type of the filter attribute in the outer query must "match up" with the data type of one certain display column in the inner query.

Step 1: Construct the Inner Event Query

  1. Go to the ANALYTICS page.
  2. Construct the query and make sure it produces the desired results.
    1. Click the Edit Filter and Time Range field and select Event Attribute in the query tab.
    2. Set the Time Range. For example, you can set it to the last 1 hour. This time period is not important if you use this query as an inner query.
    3. If it has the Event Source, then you can set it as Online.
    4. Click Apply to save your changes.
    5. Click the Change Display Fields icon and enter the attributes you want to display.
    6. Click Apply & Run.
  3. Click Action > Save as Report.
  4. Ensure Save Definition is checked.

Step 2: Construct the Outer CMDB Query by Referring to the Query in Step 1

  1. Go to the ANALYTICS page.
  2. Click in the Search bar--it will open the Filter dialog box.
  3. Select CMDB Attribute.
  4. Select the appropriate target from the drop-down list.
  5. Set the query condition.
    1. Select the Attribute.
    2. Select the Operator--the most common operators are IN and NOT IN.
    3. Click in the Value field and select Select from Report.
    4. Select the Report name, saved in Step 1, Substep 4.
    5. Open the Attribute drop-down list and choose the attribute that matches the attribute in Step 2, Substep 5a.
    6. Click OK.
  6. Choose the Nested Time Range: the inner report will be run for this time range.

  7. Click the Change Display Fields icon and choose the attributes you want to display.
  8. Click Apply & Run.
  9. To save the report, click Actions > Save as Report. Enter the name of the nested query.

Outer Event Query, Inner Event Query

The following generalized steps describe how to set up and use nested queries for an outer event query and an inner event query.

Step 1: Construct the Inner Event Query

  1. Go to the ANALYTICS page.
  2. Click the Edit Filter and Time Range field.
    1. Select Event Attribute and create the Filter Condition.
    2. Set the Time Range.

      For example, you can set it to the last 1 hour. This time period is only useful to check if this query produces the desired results. If used as an inner query, time range would be set separately in Step 2 below.

    3. If it has the Event Source, then you can set it as Online.
    4. Click Apply to save your changes.
    5. Click the Change Display Fields icon and enter the attributes you want to display. This query needs to be an aggregate query to be used as an inner query. One of the Group By attributes must match (meaning compatible value sets) an attribute chosen in the outer query in Step 2, Substep 2.d.ii
    6. Click Apply & Run.
  3. If you are happy with the result, then click Actions > Save as Report. Ensure Save Definition is checked.

Step 2: Construct the Outer Event Query

  1. Go to the ANALYTICS page.
  2. Click the Edit Filter and Time Range field.
    1. Select Event Attribute in the query tab.
    2. Choose an Attribute.
    3. Choose an Operator. The most common operators are IN and NOT IN.
    4. Click the Value field and select Select from Reports.
      1. Select the Report name, saved in Step 1, Substep 3.
      2. Open the Attribute drop-down list and choose the attribute that matches the attribute in Step 1, Substep 2e.
      3. Click OK.
  3. Choose the time ranges.

    1. Choose the time range for outer query.
    2. Choose Nested Time Range for the inner query.
    3. If it has the Event Source, then you can set it as Online.
  4. Click Apply to save your changes.
  5. Click the Change Display Fields icon and enter the attributes you want to display.
  6. Click Apply & Run.
  7. You can save the results by clicking Actions > Save as Report. Ensure Save Definition is checked.

Outer Event Query, Inner CMDB Event Query

The following generalized steps describe how to set up and use nested queries for an outer event query and an inner CMDB query.

Step 1: Construct the Inner CMDB Query

  1. Go to the ANALYTICS page.
  2. Click the Edit Filter and Time Range field and select CMDB Attribute in the query tab.
    1. Select the appropriate Target from the drop-down list.
    2. Set the query condition.
      1. Select the Attribute.
      2. Select the Operator
      3. Click in the Value field. Do not select Select from Report.
    3. Click Apply to save your changes.
  3. Click the Change Display Fields icon and enter the attributes you want to display.
  4. Click Apply & Run.
  5. If you are happy with the result, then click Actions > Save as Report. Ensure Save Definition is checked.

Step 2: Construct the Outer Event Query

  1. Go to the ANALYTICS page.
  2. Click the Edit Filter and Time Range field and select Event Attribute in the query tab.
    1. Choose an Attribute.
    2. Choose an Operator - the most common operators are IN and NOT IN.
    3. Click in the Value field and select Select from Report.
      1. Select the Report name, saved in Step 1 Substep 5.
      2. Open the Attribute drop-down list and choose the attribute that matches the attribute in Step 1 Substep3.
      3. Click OK.
  3. Choose the Time Range for outer query.
  4. If it has the Event Source, then you can set it as Online.
  5. Click Apply to save your changes.
  6. Click the Change Display Fields icon and enter the attributes you want to display.
  7. Click Apply & Run.
  8. You can save the results by clicking Actions > Save as Report. Ensure Save Definition is checked.