ScriptRunner 2018R1 with dynamic PowerShell queries
14 min read
By: Frank Kresse Feb 17, 2018 11:55:28 AM
Table of Contents
- PowerShell Script Policies with Dynamic Queries
- Configuring Dynamic Queries for PowerShell
- Complex queries in Active Directory
- Queries via PowerShell Script
- Support for implicit PowerShell remoting
- Azure AD 2.0 Support in Office 365
- Support for Edge Browser
- Security settings now available in setup
- Test ScriptRunner for free
Fig. 1 – Schema of a dynamic Powershell query
The query shown above returns the display and parameter values for all OUs in Active Directory matching the search input “user”. When the display value “Users | Germany” is selected, the script variable $OUPath is passed as the parameter value of the Distinguished Name of the OU.
Cascaded queries are particularly interesting for daily use cases. Several queries are logically connected in series. The query sources can be used mixed. For cascaded queries, the parameter value of the previous query is used as input variable in the current query. It thus controls the query behavior itself.
Fig. 2 – How Cascaded PowerShell Queries Work
In the first query, the displayed cascade determines the Distinguished Name of a selected OU in the Active Directory, here for Germany/Customer/ASR/LOCAL, and uses it to control the second query. This second query determines all groups in the selected OU. When the group is selected, the result provides the corresponding SAMAccountName, here “G-GRP-012”. This is passed to the $Group script variable and the third query. The third and last query now completely dissolves the group into its members. The selection of a member passes the SAMAccountName of the user, here “G001”, as parameter value to the script variable $User.
For example, such a request cascade could parameterize a PowerShell script to remove a user from a particular group. The main advantage is the generic reusability of the queries and the script for different OUs, groups and users without having to change the script itself. In combination with various preconfigured ScriptRunner actions, subadministration scenarios for specific OUs can be implemented very easily.
Users or directories, databases, virtual machines etc. could also be automatically queried for rights changes and the results could be made available to the user for selection. In the service desk team, an AD user can be determined via his UPN, e-mail address or other properties and his SAMAccountName automatically passed as a parameter to the script.
Configuring Dynamic Queries for PowerShell
The very simple creation of queries and their integration into the ScriptRunner actions, the set of rules for script execution, was one of the main focuses during the development of ScriptRunner 2018R1. The most conspicuous feature of the queries is the extended main menu. There you can immediately select the desired source for a query from a list with the menu “New+”.
Fig. 3 – Creating a Query by Selecting the Source
Depending on the source of the query, settings for its execution are possible.
There are different possibilities of the list with the display values for the user:
- Display of values as simple dropdown menu
- Display of the values as a simple drop-down menu with multiple selection, provided that the script variable has the data type String Array.
- Display of the values as dropdown menu with a multifunctional search field. Letters, syllables, or words can be entered in the search field BEFORE the query to limit the result set displayed. AFTER the query, the search field is used directly to filter the result set of the query.
Fig. 4 – Display of the query results in the Delegate App
In addition, the queries differ in their cache mechanism:
- Queries in real time are sent directly to the source at runtime; this is especially necessary for cascaded queries.
- Queries with time-controlled cache, e.g. for regularly changing files or queries without real-time requirements
- Queries with automatic cache algorithm; the query is automatically executed in the context of the update level in the background by the ScriptRunner Host
Fig. 5 – Query setting options
The configuration of the actual query depends on the possibilities of the source. The query of groups from the Active Directory is presented here as an example. Objects from the Active Directory are queried via LDAP. In order to be able to carry out the query effectively, it is useful to parameterize the query. The following parameters can help:
- Domain in which the group is to be searched for
- Restriction to the object type “Group
- Restriction to the type of group; safety group or distribution group
- Restriction to a subtree of the Active Directory
- Required parameter value to be determined
- Information on the display of the respective display value
Fig. 6 – Configuration of a query of groups from the Active Directory
In the figure above, the following settings have been made for querying groups.
- is searched in the domain, in which ScriptRunner resides as well
- Only security groups without additional attribute filters are searched for; the use of attribute filters is described in the next section.
- is searched in the entire domain; the search is only restricted to a partial area if a higher-level query controls another SearchBase in an action; the SearchBase can also be restricted directly, e.g. by entering “OU=Customer,DC=asr,DC=local”.
- The attribute “SAMAccountName” is used as parameter value.
- The attribute “cn”, i.e. the Common Name, is used as the display value in the dropdown menu.
The assignment of the queries to a script execution is done via the ruleset for the ScriptRunner action. Among other things, this defines on which systems the script may be executed, who is authorized to start an action, and so on. The central part of an action is the possible preassignment of script variables. This allows different default actions to be created with the same script.
By assigning queries to script variables in ScriptRunner actions, fully dynamic use cases can now be mapped. The assignment is made for individual variables.
Fig. 7 – Assigning Queries to Script Variables in an Action
The script variable $OUPath is only used to control the second query of groups from the Active Directory. This script variable has been assigned a query to a static list of entries.
The script variable $Group was assigned the query “Group list from AD root …” and at the same time the variable $OUPath was assigned as input value from the first query and so on. This restricts the SearchBase to the previously selected OU.
Complex queries in Active Directory
Configurations with specific query settings are particularly interesting in the application. You can set additional filters or create LDAP queries directly.
Fig. 8 – Specific query of marketing department users
In the figure above, users are asked which are in the OU Customer and which fulfill the AD attribute [department] = “Marketing”. The display has been set so that not only the names of the users, but also their e-mail address and location (if occupied) are displayed.
A filter on the AD attribute [cn] can be useful at this point for a query on groups, for example, in order to filter on all groups with “Admin” in the name.
A test mode for the query is used for quick verification of the configured query.
Fig. 9 – Result of the test run of an AD query with specification of the runtime.
The complex query of all user accounts below an OU, for which the password has to be changed at the next login, can be implemented with a direct LDAP query.
Fig. 10 – Complex query with LDAP
Queries via PowerShell Script
The full flexibility of queries is achieved with query scripts, as arbitrary queries can be implemented via PowerShell. Depending on the option selected, you can specify where the query is to be executed:
- Local execution on the ScriptRunner host
- Remote execution always on the same system. This allows you to query SQL databases, for example, regardless of the system on which the actual script is executed.
- Remote execution always on the system on which the actual script is to be executed. This allows you to query different virtualization hosts for active VMs with a single query at runtime.
The following figure shows a generic query of Exchange databases.
Fig. 11 – Querying Exchange Databases
Script queries can also be used in a cascade. In the following example, a workflow to be selected can be started on a previously selected list in SharePoint Online. By generically designing the scripts and queries, actions for different SharePoint sites can now be designed with the same queries and the same workflow script.
The first query script queries a SharePoint site for SharePoint lists with workflows. The script has two variables, $SiteURL and $userCredential. Both are controlled via the default settings of the action. The second query creates a selection of workflows for the previously selected SharePoint list. The query script has an additional $listTitle parameter to control the query workflow for the previously selected sharepoint list.
Fig. 12 – Cascaded queries using PowerShell scripts
By delegating this action, a service desk member or a selected group of end users would now be able to select a SharePoint list and start updating it. The execution of the PowerShell scripts on SharePoint Online is still completely subject to the ScriptRunner guidelines. The separation of the user rights for starting the workflow and the necessary administrative rights for executing the query and function scripts ensures the highest possible reproducibility and security.
Service desk users and administrators benefit equally with the queries from very powerful functions:
- High degree of interactivity
- Dynamic parameter input through targeted queries
- Versatile, granular setting options for queries
- Once configured, queries can be applied equally to different actions, such as users and group queries.
- Simple search and selection at runtime in the browser interface
- Reduction of PowerShell code through cascaded query options
- high security through PowerShell Script Policies in ScriptRunner
Support for implicit PowerShell remoting
The ability to run PowerShell has now been enhanced for additional scenarios. With the possibility of implicit remoting, ScriptRunner now offers all the possibilities offered by Import-PSSession on the command line. This option is especially, but not exclusively, interesting for using ScriptRunner with Microsoft Exchange. When using this option ScriptRunner behaves similar to the Exchange Management Shell. A reconfiguration of the PowerShell settings on the Exchange Server will no longer be necessary. The commands imported into the session can also be limited.
Fig. 13 – PowerShell Remoting Session Settings
Azure AD 2.0 Support in Office 365
ScriptRunner directly supports Office 365 Service Endpoints for Directory, Exchange Online, SharePoint Online and Skype4Business Online. With the support of Azure AD 2.0, both the features of MS online and the latest version of Microsoft’s Azure AD are now fully available.
Support for Edge Browser
In addition to support for Internet Explorer 11, Google Chrome, Firefox and Opera, the new version also supports Microsoft’s new Browser Edge. Both the Web App for Administrators and the Delegate App for service desk teams can now use Edge. The Java Script based Windows Apps are still available as an option.
Security settings now available in setup
Until now some security settings had to be configured manually in the ScriptRunner Web Service. As of version 2018, these settings can be made with the setup, as long as valid certificates are available on the ScriptRunner host. In addition, these settings have been added to the Set-ASRUri PowerShell cmdlet.
Test ScriptRunner for free
We provide you with ScriptRunner as a 30-day test installation free of charge or give you a short product demo. You can find more information here.
About the author:
Frank Kresse is the Head of Product and CEO of ScriptRunner. As the inventor of the automation and delegation solution for PowerShell, he advises clients on use case scenarios and develops solutions for the automation and the digitalization of their processes. He is also involved in technology start-ups.