How to create an automation policy that will add computers to a filter based on a SQL query

Products

• Client Management Suite

Issue/Introduction

As an administrator, you would like to create an automation policy to automatically run a SQL query that will put the returned computers into a filter.  

Environment

Release: 8.6

 

Resolution

While you can accomplish this by just putting the SQL into a filter, you may want to have this done through an automation policy.  In that case, you can follow these steps:

1. From the SMP console, open Manage > Filters.  Right-click and choose new Filter.  The filter can be empty.
2. From the SMP console, click Manage > Jobs and Tasks:
3. Right-click in an area of your choosing and choose New > Task.
4. Find the task Add to Filter and select it.
5. As shown in the following screenshot, click Select Filters and choose the filter you created in the steps above.  Click OK when done to save the task.
6. Navigate to Manage > Automation Policies
7. Click New Policy to create a new automation policy.
8. Give your new automation policy a name and then set a schedule according to your needs.
9. Data Source:  RAW SQL Query
10. Click the Edit Query link and put your SQL query into the parametrized query window.
11. Leave for nonempty data selected
12. Next, to select/job or task, click the link to open and choose the Add to Filter task you created above.
13. Click the edit input parameter link
    
14. After clicking the edit input parameter link, under Resources, use the drop-down to select [guid] (value list). Click OK to go back to the automation policy.
15. Save your automation policy and test it.  When running it you should see the filter that you selected show the computers returned by your SQL query.  Remember that you might have to click on update membership when viewing the filter to see the computers listed.

A few notes about the SQL query.  Since this is going to populate a filter, you want to make sure that your SQL query is returning only GUIDs and no other data.  For example, using the following query:

select top 2 guid from vcomputer