Playing with BP 5.x and queue item data dump

Hi guys, this is just a brief post about queue items dumping.
I found this very useful with BP version 5.x and high-intensive volume data which caused failure in GetNextItem action.
So i need to dump data to another table in order to keep DB as small as possible waiting for the permission from the customer, to wipe all the old data (or restoring just a part of the amount of data).

The SQL script is self explaining and deals with ID consistency, nothing hard to do, but if you're asking if you can do this operation without corrupting the DB, yes you can, so have fun:

-- We need to create just one table to dump data, otherwise you can use SELECT * INTO TABLE which creates a new table everytime
DROP TABLE [dbo].[BPAWorkQueueItem_TEMP]
CREATE TABLE [dbo].[BPAWorkQueueItem_TEMP](
 [id] [uniqueidentifier] NOT NULL,
 [queueid] [uniqueidentifier] NOT NULL,
 [keyvalue] [nvarchar](255) NULL,
 [status] [nvarchar](255) NULL,
 [attempt] [int] NULL,
 [loaded] [datetime] NULL,
 [completed] [datetime] NULL,
 [exception] [datetime] NULL,
 [exceptionreason] [nvarchar](max) NULL,
 [deferred] [datetime] NULL,
 [worktime] [int] NULL,
 [data] [nvarchar](max) NULL,
 [queueident] [int] NOT NULL,
 [ident] [bigint] IDENTITY(1,1) NOT NULL,
 [sessionid] [uniqueidentifier] NULL,
 [priority] [int] NOT NULL,
 [prevworktime] [int] NOT NULL,
 [attemptworktime]  AS ([worktime]-[prevworktime]) PERSISTED,
 [finished]  AS (isnull([exception],[completed])) PERSISTED,
 [exceptionreasonvarchar]  AS (CONVERT([nvarchar](400),[exceptionreason])),
 [exceptionreasontag]  AS (CONVERT([nvarchar](415),N'Exception: '+replace(CONVERT([nvarchar](400),[exceptionreason]),N';',N':'))) PERSISTED,
 [encryptid] [int] NULL,
 [lastupdated]  AS (coalesce([completed],[exception],[loaded])) PERSISTED,
 [locktime] [datetime] NULL,
 [lockid] [uniqueidentifier] NULL)
GO

-- We enable identity insert, this will preserve ID assigned by BluePrism, then we dump data
-- in the where condition we use queue name and completed date (so we do not take any pending or deferred item)
SET IDENTITY_INSERT [dbo].[BPAWorkQueueItem_TEMP] ON

INSERT INTO [dbo].[BPAWorkQueueItem_TEMP](
 [id]
 ,[queueid]
 ,[keyvalue]
 ,[status]
 ,[attempt]
 ,[loaded]
 ,[completed]
 ,[exception]
 ,[exceptionreason]
 ,[deferred]
 ,[worktime]
 ,[data]
 ,[queueident]
 ,[ident]
 ,[sessionid]
 ,[priority]
 ,[prevworktime]
 ,[locktime]
 ,[lockid])
 SELECT wqi.[id]
    ,wqi.[queueid]
    ,wqi.[keyvalue]
    ,wqi.[status]
    ,wqi.[attempt]
    ,wqi.[loaded]
    ,wqi.[completed]
    ,wqi.[exception]
    ,wqi.[exceptionreason]
    ,wqi.[deferred]
    ,wqi.[worktime]
    ,wqi.[data]
    ,wqi.[queueident]
    ,wqi.[ident]
    ,wqi.[sessionid]
    ,wqi.[priority]
    ,wqi.[prevworktime]
    ,wqi.[locktime]
    ,wqi.[lockid]

   FROM [BluePrism].[dbo].[BPAWorkQueueItem] as wqi
   JOIN [BluePrism].[dbo].[BPAWorkQueue] as wq on wqi.queueid = wq.id
   WHERE 
  wq.name = 'Queue.BonificiEsteroEntrata.ContiAttesaLavorazione' AND 
  finished < DATEADD(MONTH, -3, GETDATE())
GO

-- Here we restore identity insert value
SET IDENTITY_INSERT [dbo].[BPAWorkQueueItem_TEMP] OFF

-- Finally we erase data from original table
DELETE FROM [dbo].[BPAWorkQueueItem] WHERE 
 EXISTS (SELECT * FROM [dbo].[BPAWorkQueueItem_TEMP] WHERE [BPAWorkQueueItem].ident = [BPAWorkQueueItem_TEMP].ident)

Read More

Morning routine with BluePrism and PowerShell

If you have deployed lots/tons of robots, you know that maintenance is the big elephant in the room.
Blue Prism scheduler is not so "friendly" and sometimes is not so responsive.

Lately I implemented some warmup scripts that simply restart SQL Server service at 00.00 AM and Blue Prism application server at 1:00AM. Resource PCs are rebooted as well at 2:00 AM.
Resource PCs are scheduled to start working at 3:00 AM even if nobody is at the office, but the problem come when the first colleague start to work at 7:00 AM.

Imagine you have something like 40 machines and you have to connect to each one and check if the schedule has started correctly... i know, it's a bit boring task.
The script i will show you is made of different subscript, according to your scenario you can use split it in more pieces.

Please note that in out project we have very complex schedule, every machine need to run different processes at different hours, with different cut-offs.
For every machine we have two schedule:

1. The one with include login and then alternates different processes (eg. MACHINE01)
2. Equal to the previous one without login (e.g. MACHINE01.Backup)

The script knows what machines are involved and their respective Backup schedule.
So at first, open as many RDP sessions as the involved machines, when all machines are visible and tiled on the screen, it starts to check (using telnet) what machines are running something.
If the machine running the script is equipped with Blue Prism, it will try lo launch the schedule using AutomateC.exe.

The code is quite self-explaining, so please have a look below and enjoy

# BEGIN Configs
$PATH_BUFFERFILE = "C:\temp\roboot.txt"
$PATH_AUTOMATEC = "C:\Program Files\Blue Prism Limited\Blue Prism Automate\AutomateC.exe"
$PATH_RDPFILES = "P:\Users\Varro\Desktop\RDP\"

$SCEHDULE_LASTCHECK = '20:30'

$RESOURCEPC_PORT = 8182
$RESOURCEPC_SCHEDULES = @{}
$RESOURCEPC_SCHEDULES.Add('MACHINE01_HOSTNAME','MACHINE01.Backup')
$RESOURCEPC_SCHEDULES.Add('MACHINE02_HOSTNAME','MACHINE02.Backup')
$RESOURCEPC_SCHEDULES.Add('MACHINE03_HOSTNAME','MACHINE03.Backup')
$RESOURCEPC_SCHEDULES.Add('MACHINE04_HOSTNAME','MACHINE04.Backup')
$RESOURCEPC_SCHEDULES.Add('MACHINE05_HOSTNAME','MACHINE05.Backup')
# END Configs

# BEGIN - Functions written by someone smarter than me
Function Show-Process($Process, [Switch]$Maximize)
{
  $sig = '
    [DllImport("user32.dll")] public static extern bool ShowWindowAsync(IntPtr hWnd, int nCmdShow);
    [DllImport("user32.dll")] public static extern int SetForegroundWindow(IntPtr hwnd);
  '
  
  if ($Maximize) { $Mode = 3 } else { $Mode = 4 }
  $type = Add-Type -MemberDefinition $sig -Name WindowAPI -PassThru
  $hwnd = $process.MainWindowHandle
  $null = $type::ShowWindowAsync($hwnd, $Mode)
  $null = $type::SetForegroundWindow($hwnd) 
}

Function Get-Telnet
{   Param (
        [Parameter(ValueFromPipeline=$true)]
        [String[]]$Commands = @("username","password","disable clipaging","sh config"),
        [string]$RemoteHost = "HostnameOrIPAddress",
        [string]$Port = "23",
        [int]$WaitTime = 1000,
        [string]$OutputPath = "\\server\share\switchbackup.txt"
    )
    #Attach to the remote device, setup streaming requirements
    $Socket = New-Object System.Net.Sockets.TcpClient($RemoteHost, $Port)
    If ($Socket)
    {   $Stream = $Socket.GetStream()
        $Writer = New-Object System.IO.StreamWriter($Stream)
        $Buffer = New-Object System.Byte[] 1024 
        $Encoding = New-Object System.Text.AsciiEncoding

        #Now start issuing the commands
        ForEach ($Command in $Commands)
        {   $Writer.WriteLine($Command) 
            $Writer.Flush()
            Start-Sleep -Milliseconds $WaitTime
        }
        #All commands issued, but since the last command is usually going to be
        #the longest let's wait a little longer for it to finish
        Start-Sleep -Milliseconds ($WaitTime * 4)
        $Result = ""
        #Save all the results
        While($Stream.DataAvailable) 
        {   $Read = $Stream.Read($Buffer, 0, 1024) 
            $Result += ($Encoding.GetString($Buffer, 0, $Read))
        }
    }
    Else     
    {   $Result = "Unable to connect to host: $($RemoteHost):$Port"
    }
    #Done, now save the results to a file
    $Result | Out-File $OutputPath
    return $Result
}
# END - Functions written by someone smarter than me

# MAIN
# 1. Close all RDP session
Get-Process | Where-Object { $_.Path -like "*mstsc*" } | Stop-Process -Force

# 2. Wait all RDP sessions are closed
Do {
    $rdpSessions = Get-Process | Where-Object { $_.Path -like "*mstsc*" }
    Start-Sleep -s 1
} While ($rdpSessions.Count -ne 0)

#3. Open RDP sessions
ForEach ($resourcePc in $RESOURCEPC_SCHEDULES.Keys) {   
    $arg = $PATH_RDPFILES + $resourcePc + ".rdp"
    Start-Process "mstsc" -ArgumentList """$arg"""
    Start-Sleep -s 1
}

#4. Wait all RDP sessions are opened
Do {
    $rdpSessions = Get-Process | Where-Object { $_.Path -like "*mstsc*" }
    Start-Sleep -s 2
} While ($rdpSessions.Count -ne $RESOURCEPC_SCHEDULES.Keys.Count)


#5. Wait to be logged in every RDP session the script opened, then click enter or something else
$process = Get-Process -Id $PID
Write-Host $process
Show-Process -Process $process -Maximize

$key = Read-Host "Press ENTER key when all are connected"

#6. Tile all RDP sessions vertically
$ShelleExp = New-Object -ComObject Shell.Application
$ShelleExp.TileVertically()

#7. Just deciding what to do according to the hour of the day (don't start if current time > 8.30 in this case or on weekend days)
$now = (Get-Date)
$nowDay = $now.DayOfWeek.value__
Write-Host "Today is $nowDay - $now.TimeOfDay"

if ($now.TimeOfDay -gt $SCEHDULE_LASTCHECK -And $nowDay -ne 6 -And $nowday -ne 7)
{
    Write-Host "Too late... maybe tomorrow" -BackgroundColor Red -ForegroundColor White
} 
else 
{

    #8. Checking if machines are working (using telnet to get those data)
    [System.Collections.ArrayList]$resourcePC_Problematic = @()
    foreach ($resourcePc in $RESOURCEPC_SCHEDULES.Keys)
    {
        Write-Host "Check $resourcePc machine" -ForegroundColor Yellow
        # Remove buffer file, call telnet and get message
        Remove-Item $PATH_BUFFERFILE -ErrorAction Ignore
        $telnetContent = Get-Telnet -RemoteHost $resourcePc -Port $RESOURCEPC_PORT -Commands "status" -OutputPath "$PATH_BUFFERFILE"
        Write-Host $telnetContent -ForegroundColor Yellow
        
        #9a. If contains running, something is running, so move on
        if ($telnetContent.Contains("RUNNING")) 
        { 
            Write-Host "$resourcePc is working" -ForegroundColor Green
        }
        #9b. If does not contains running, time to run the schedule
        else
        {
            $scheduleName = $resourcePc_Schedulazioni[$resourcePc]
            Write-Host "Run now scheduled task $scheduleName on $resourcePc" -BackgroundColor Red -ForegroundColor White
            $cmd = "cmd.exe /C ""$PATH_AUTOMATEC"" /sso /startschedule /schedule $scheduleName"
            Invoke-Expression -Command:$cmd
        }
        
        #9c. Clean it up
        Remove-Item $PATH_BUFFERFILE
        Write-Host 
        Write-Host 
    }

    #10. Check again all machines to check are running something, if not, write who's doing nothing
    foreach ($resourcePc in $RESOURCEPC_SCHEDULES.Keys)
    {
        Remove-Item $PATH_BUFFERFILE -ErrorAction Ignore
        $telnetContent = Get-Telnet -RemoteHost $resourcePc -Port $RESOURCEPC_PORT -Commands "status" -OutputPath "$PATH_BUFFERFILE"
        if (!$telnetContent.Contains("RUNNING")) 
        { 
            $resourcePC_Problematic.Add($resourcePc)
        }
    }

    Write-Host "Resource PC not booted: $resourcePC_Problematic" -BackgroundColor Red -ForegroundColor White
}


Read-Host "Premere ENTER to exit"

Read More

Anatomy of a BP consumer template

After a lot of work, Antonio Durante and I decided to create a template to standardize someway how we build consumer processes in Blue Prism.
So let me introduce you DaTemplate.

DaTemplate overview

This is the consumer roundtrip for a typical process that works with queues: i use it everytime.
The concept is, raise exception alwais to the hightest level.

Let's examine the right part first.
After start i reference two pages:

1. Reset: no matter what happened, close every application that can someway create problems to out process;
2. Config: configure the environment if needed and get the process global variables

Then i start with the get next, but i put all the data item data in global variables, so they are accessible everywhere. No more global variables except item one and config one, all other variables must be local to the page they are declared in.
In the end i call a page "Item Logic" which is typical of the process.

The right part

So in the process pages, you will use recover resume block to manage exceptions, but, if the result is BusinessException/Managed Exception or Technical Exception, don't do anything and let the left part of the template work for you.
The exception message is extracted and the right action addressed according to exception type.
If Business or Managed Exception, item will be Mark Success (i consider a recognized exception as a success only in the technical log), and logged as an exception in the business log.
If the exception is unknown or a technical one, the process for that item will be repeated for MaxAttemps times before becaming a Managed exception.
Error is screenshotted for further investigations.

The left part for managing exceptions

Please note that on get next and Check Attempts there is a recover resume block.
This is due to the potential failure of the get next, and also a potential failure in the left part of the template that could create an infinite loop.
If we got an unexptected exception, those recover resume blocks will take the template to a new get next operation, just taking 5 seconds to breath (in the case of get next failure).

That's all folks, i hope you find it useful

Read More

BP and JSON

As you already know, BP works on queues.

What if a process is not based on queue items but is based on a sequence of bulk operations?
You can think every bulk operation as fake queue item and manage them from control room.
But maybe you want to apply some logic, for example, let's assume your process id made of 4 subprocesses (A, B, C, D), if something go wrong with A you don't want to execute C, no problem on B and D.

Yeah, maybe too simple as an example, but use you imagination... believe me, you don't want to know anything about the process that, in the real world, lives on this solution (Alberto Cannas became mad on this).
So think about a process that doesn't rely on queue and where you want to play with subprocesses that can be seen as transactions.

We could store the data in a DB, but what if we cannot use a DB... we can use a JSON file as a DB, in order to have a flexible DB that helps us.
For example, in the process we are dealing with, we use a JSON file that is generated everyday, and the process state is represented by data in a collection.

I will show you a little process that simply export data only at 10.00/12.00/14.00/16.00 using JSON  to make all clear. This is what BP object Utility - JSON wrote when using Collection to JSON action.

{"Log 10":false, "Log 12":false, "Log 14":false, "Log 16":false} 

So you can read the informations you store in your information in a JSON file, implement your logic, and write again only when needed.
You should take care about 2 things:

1. The subprocess that works on file must be encapsulated in enviroment lock. This will be helpful in a multi-robot environment;
2. Pay attention to what you write in the JSON, when you use JSON to Collection action, the objects write the collection as an array, so you have to remove brackets at beginning and at the end of the JSON (thank Manuel Mascia for the trick)

Yeah... tips'n'tricks

Now you can push this solution beyond this example.
That's all folks.

Read More

BP and Angular

Working with Antonio (Durante ndr), we faced a problem automating modern web application written in Angular.
Let me explain you the scenario, we spied all the textboxes with HTML spy mode and we wrote values into the textboxes, but the system returned an error.
I cannot show you anything else because... come on you know why, but focus on the red border and trust me that the message showed by the system is: please enter a value.

And the system said: please enter a value

Why this happened?
Simply because, even if we wrote something, the ng-pristine tag is still here.
What is ng-pristine? A tag that tells the model that no fields has been modified.

How to solve this problem? Convince Angular you did something on the interface.
In the example below, we wrote the value 7200 in the field with name agenziaOrdinante.
After that we force angular to call the change event and then the blur event, in this way we notify to the model that something has changed, with the blur event indeed we are sure to trigger other potential events that are fired when we click elsewhere.

JS = lifesaver

In the end mind to create a VBO that writes data using JS to generate and launch the javascripts one by one.
Instead of ElementName input, in the final version i preferred to use JS selector as input to make the code more generic.

Write data action example

And that's all folks. As usual thanks to Antonio Durante for the fun trip.

Read More

Add credentials from BP... programmatically

As you know, you can use BP not also for automate business processes.
For example, in our scenario (we are talking about a RPA program with more than 30 complex automations), we have credentials that expire if you don't login with those credentials for a certain period of time.
On the other hand, because of, every time we provision a new process, we have to add to credential manager, all the credential needed by the process itself (call this value x) for all the machines involved (call this value y), it's easy to understand that the process of provisioning and keeping credentials alive led me to a new limit: BP does not provide anything to create credentials, so SQL is out ally.
So the key is to launch someway (ODBC or whatever) this custom SQL store procedure from BP.
The result will be a credentials with all roles, with permissions on all processes and resource PCs and with a void password.
Please note that void is 3uDCB67zyUqBsHym7o635w==:JM6brcdYVFsZhbEKISRtaQ== for BP since because the value is encoded and may vary depending on Encryption Scheme so make sure you already check what is the null password value on your farm.

Add credentials script

USE [BluePrism]
GO
/****** Object:  StoredProcedure [dbo].[BPAddUser]    Script Date: 04/10/2018 14:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[BPAddUser]
       @userLogin nvarchar(128)
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here
       BEGIN 

             if not exists (select * from BPACredentials where login = @userLogin AND name = @userlogin)
                    BEGIN
                           declare @newid uniqueidentifier = NEWID();

                           INSERT INTO [dbo].[BPACredentials]
                                  ([id],[name],[description],[login],[password],[expirydate],[invalid],[encryptid])
                           VALUES
                           (@newid,@userLogin,'Created by script',@userLogin,'3uDCB67zyUqBsHym7o635w==:JM6brcdYVFsZhbEKISRtaQ==',NULL,0,1)

                           INSERT INTO [dbo].[BPACredentialRole]
                                  ([credentialid],[userroleid])
                           VALUES
                                  (@newid, NULL)

                           INSERT INTO [dbo].[BPACredentialsProcesses]
                                  ([credentialid],[processid])
                           VALUES
                                  (@newid, NULL)

                           INSERT INTO [dbo].[BPACredentialsResources]
                                  ([credentialid],[resourceid])
                           VALUES
                                  (@newid, NULL)
                    END
             Else
                    BEGIN
                    
                    DECLARE @credentialId uniqueidentifier;
                    select @credentialId = id from BPACredentials where login = @userLogin AND name = @userlogin;
                    
                    -- Create or overwrite 
                    if not exists (select * from BPACredentialRole where credentialid = @credentialId)
                           INSERT INTO BPACredentialRole([credentialid],[userroleid]) values (@credentialId, NULL)
                    else
                           update BPACredentialRole set userroleid = NULL where credentialid = @credentialId
                           

                    -- Create or overwrite 
                    if not exists (select * from BPACredentialsProcesses where credentialid = @credentialId)
                           INSERT INTO BPACredentialsProcesses([credentialid],[processid]) values (@credentialId, NULL)
                    else
                           update BPACredentialsProcesses set processid = NULL where credentialid = @credentialId


                    -- Create or overwrite
                    if not exists (select * from BPACredentialsResources where credentialid = @credentialId)
                           INSERT INTO BPACredentialsResources([credentialid],[resourceid]) values (@credentialId, NULL)
                    else
                           update BPACredentialsResources set resourceid = NULL where credentialid = @credentialId
             END

       END
END

Read More

Embarassing stories: repair lookup fields

You deployed lists with lookup in old fashioned way and something went wrong.
Your lookup field points to nothing because you team forgot the difference between list definition and list instance.
The script will change (brutally) the schema of the field using CSOM: I don't like it but it works like a charm, so don't panic, you can use this script and you'll be at home asap.

Just change the uppercase values with yours.

Read More