Try Now

How to Use ScriptRunner 'FilePicker' Query in an Action

Table of Contents

 

 

Post Featured Image

As we are in close exchange with ScriptRunner users, many of their suggestions and ideas have been incorporated into the software. Using ScriptRunner, you may have noticed that it is not yet possible to upload files for processing. We are working on it! This feature is currently under development and will be included in the next version, Portal Edition R4. In the following article, we present a workaround to process files of different types in ScriptRunner, so that you don't have to wait until the release of our Portal Edition R4.

We have written a query script in PowerShell that makes it possible to select files via a dialog and then process the contents of those files in an action. At the end of this post, you will find the complete instructions on how to create and use the query. The script currently only works with a network share, a corresponding UNC path and the appropriate credentials with permissions on the network share.
There are two ways to retrieve the data from the corresponding path. In the first variant, the definition is done in the ScriptRunner target. In the second variant,  a $PSDrive is used within a script.

 

Variant #1 – Credentials in the Target

The credentials that have access to the share are specified in the target. We use a simple AD account with permissions to the network share as credential.

Create Credentials - Credential Properties

The target must then be structured as follows:


Create Target - Select Target Type


Configure Target - Local Execution

The target itself must be created as "Local Execution" and operated in "Simple Run-as Process Mode".

Attention: If there are parameters of the type "PSCredential" in the script, and if they contain a value, the query or the action will not start, because in the "Run-as" mode no PSCredential parameters are supported. This is also pointed out in the target as a note text.

 

Variant #2 – Credentials as Parameter

The credentials with permissions to the share are passed to the parameter "$DomainAccount". The UNC path and the credentials are mapped to a PSDrive and can then be retrieved or used. After the query is finished, the connection to the PS drive is closed again and the PS drive is deleted. If you want to be on the safe side, you can also comment the "Finally" area of the try-catch block in the script.

comment - screenshot of the code

The more elegant variant is certainly the second, since here the flexibility is preserved and one does not necessarily depend on the target with the suitable Credentials. In both variants, nothing has to be changed in the script itself. The logic is inside the code.

The script can only be used in the query if it contains the "_QUERY_" tag. The tag can be assigned via the name of the script ("QRY_Get-FileFromPath.ps1"), manually in the "Set appropriate Tags" area in the script itself, or via the checkbox "This is a query script returning a query result list" in the same area. In the following screenshot, you can see the appropriate tag, it is marked in yellow:

Script Properties

How to use the script? Let's take a look at the procedure with the help of several screenshots.

 

Step 1

In the first step, you create a query of the type "With a Script".

Create Query - Select Query Type

 

Step 2

Now choose a suitable name for the query and set the "Caching Strategy" to "Real-time Query without Caching" and "...with Automatic Query Execution". The last point is only necessary if the query should start automatically. The UNC path can also be entered manually and passed to the query via "Cascading", for this, the option is deselected here at the checkbox.  

Create Query - Configure Query 

 

Step 3

Depending on the variant you have chosen, a corresponding target is now stored. With the credentials in the parameter, the configuration should look like on the screenshot. The query runs via "Direct Service Execution" and the network path including credentials is mapped to a PSDrive. 

Note: The PSDrive gets a GUID as name. Thus the name is unique and cannot collide with other drives or similar.

The $Include parameter specifies which file extensions are searched for. If the parameter remains empty, all files are displayed. For example, if you want to display only TXT files, the entry must be *.txt.

Query Configuration - Execute Query

If you want the authorization on the share to run via the target, then the $DomainAccount parameter must remain empty and the target will be set as the target as described above.

 

Step 4

Now the query can be tested. If everything is set correctly, the result should look like this:

Execute Query - Query Result

 

Here comes the whole code to provide the complete overview: 


<#
.SYNOPSIS
.DESCRIPTION
.NOTES
.COMPONENT
.LINK
.Parameter NetworkPath
[sr-en] Enter filepath in UNC format
[sr-de] Bitte den Verzeichnispfad im UNC Format eintragen
.Parameter Include
[sr-en] Specify the file extensions (e.g. *.csv)
[sr-de] Nach Dateiendungen filtern (z.B. *.csv)
.Parameter DomainAccount
[sr-en] Specify a domain account with permission for the network share
[sr-de] Bitte einen Domänen Benutzer mit Berechtigungen für das Netzlaufwerk eintragen
#>
param (
[ValidatePattern('(?:[^\\\/:*?"<>|\r\n]+\\)*[^\\\/:*?"<>|\r\n]*')]
[string]$NetworkPath,
[string]$Include,
[pscredential] $DomainAccount
) try { if ($null -ne $DomainAccount) {
try {
$GUID = [guid]::NewGuid().toString()
[hashtable]$cmdArgs = @{
"Name" = $GUID
"PSProvider" = "FileSystem"
"Root" = $NetworkPath
"Credential" = $DomainAccount
}
$PSDrive = New-PSDrive @cmdArgs
$TestPath = Test-Path -Path $PSDrive.Root
if ($TestPath -eq "True") {
$Files = Get-ChildItem -Path $PSDrive.Root -Include $Include -File
if ($null -ne $Files) {
foreach ($itm in $Files) {
if ($null -ne $SRXEnv) {
$null = $SRXEnv.ResultList.Add($itm) $null = $SRXEnv.ResultList2.Add("$($itm.Name)") } else { Write-Output "$($itm.Name)" } } } else { Write-Output "No files in specified folder $($PSDrive.Root) found." } } else { Write-Output "Either path $($PSDrive.Root) does not exist or access is denied!" } } catch { throw } finally { <#if ($PSDrive) { Remove-PSDrive -Name $GUID }#> } } else { $TestPath = Test-Path -Path $NetworkPath if ($TestPath -eq "True") { $Files = Get-ChildItem -Path $NetworkPath -Include $Include -File if ($null -ne $Files) { foreach ($itm in $Files) { if ($null -ne $SRXEnv) { $null = $SRXEnv.ResultList.Add($itm) $null = $SRXEnv.ResultList2.Add("$($itm.Name)") } else { Write-Output "$($itm.Name)" } } } else { Write-Output "No files in specified folder $($NetworkPath) found." } } else { Write-Output "Either path $($NetworkPath) does not exist or access is denied!" } } } catch { throw }

 

Conclusion 

There are of course other ways to process files within ScriptRunner Actions. In addition, this script can be customized to show subfolders in the directories. This is only a small workaround until ScriptRunner incorporates the functionality by default.

 


 

Related Posts

10 min read

Exchange Online Quarantine Policies

9 min read

Automating and Managing VMware vSphere with PowerShell

5 min read

Azure-Billings with ScriptRunner

About the author: