UCCX scripts can haz String templates

" (double quotes) are _usually_ a problem within a String that must be enclosed by "'s.

Now, pretend you need to construct a JSON string in a UCCX/IP IVR AEF script - which needs you to use double quotes around Strings - but it also has to be dynamic: there must be a way to add values that may come from an external source.

String concatenation is sooo 80's and ugly anti pattern and not elegant. 

Let's implement String interpolation instead - with templates. It's simple, it's reusable and easy to test.

Of course, the UCCX scripting toolbox has got everything we need:

• the u prefix* which allows escaping double qoutes using \" (without this prefix it just won't accept \") and
• the String.format( String template, Object[] arguments ) static method.

First, the template:

String template = u"{\"fruit\":\"%1s\"}"

This just translates to a simple JSON object, a Map or a list of key-value pairs if you insist, with a one key: fruit.
%1s is placeholder, meaning "take the first argument and format it as a String". In this example, template is just a UCCX String variable: 

Pretend, at some point we collect the value we need in order to construct our JSON string. Perhaps from a database. This value is stored in a String variable externalValue. In this example, externalValue = "banana".

Let's put template and externalValue together in order to create the value of completeJsonString.

Set completeJsonString = String.format(template, new Object[] {externalValue} )

What just happened? 

String.format is an elegant, yet underrated Java method. UCCX scripting is Java scripting, but instead of varargs it uses an array of objects.

The first argument is the template. The template value has a placeholder. Placeholders start with the percent sign, followed by the position (1, 2, n) and a format - in our case, s is just an instruction to pass it along (~ format the incoming String as a String).

Format what: the second argument's values. This needs to be an array of Objects (Object[]) which we construct and initialize on the fly, this is why new Object[] { externalValue }.

Let's test it (easiest: by using the debug methods available in CCX Editor):

And hey presto, we receive a nice JSON string:  U"{\"fruit\":\"banana\"}" which is of course {"fruit":"banana"}.

* Thanks @dmacias for the tip with the u prefix.

Groovy one-liner to check passwords stored in ICM Person table

Passwords are stored as MD5 hashes in the UCCE/PCCE/ICM Person table*. If you ever need to check for weak passwords, use the following one-liner:

assert passwordEncryptedInDb.replace("{enc:1}","").decodeBase64()
==
passwordPlain.digest('md5').decodeHex()

And of course, we get the passwordEncryptedInDb value using

SELECT per.Password FROM Person per 
JOIN Agent ag ON ag.PersonID = per.PersonID 
WHERE ag.PeripheralNumber = @loginId

* non-SSO

WAV file / Youtube video → GStreamer → RTP/UDP stream → CiscoTerminal

GStreamer makes streaming "from" a G.711 audio file easy.

6001.wav is already encoded as G.711uLaw:

$ gst-launch-1.0 -v filesrc location = /home/greg/jars/audio/6001.wav ! wavparse ! rtppcmupay ! udpsink host=127.0.0.1 port=19001

 

And with ffmpeg and yt-dlp:

gst-launch-1.0 -v souphttpsrc location=$(yt-dlp -x -g https://www.youtube.com/watch?v=ljnxZypkjn0) ! decodebin ! audioconvert ! audioresample ! mulawenc ! rtppcmupay ! udpsink host=127.0.0.1 port=19001

Grails: inject bean from a config file (via grailsApplication) in resources.groovy directly without BootStrap.groovy

I used to do this: resources.groovy:

beans = {
        envSettings(ConcurrentHashMap) {bean ->
        /* k: String, v: String */
    }
}

And then in BootStrap.groovy:

String label = grailsApplication.config.getProperty('envSettings.label', String, 'N/A')
Boolean nonProd = grailsApplication.config.getProperty('envSettings.nonProd', Boolean, true)
envSettings["label"] = label
envSettings["nonProd"] = nonProd

It looks like we can do that without BootStrap.groovy. It's sooo simple. resources.groovy:

beans = {
	prefixDflt String, grailsApplication.config.getProperty("prefix.dflt", String, "")
}

Transact-SQL percentage

DECLARE @t TABLE (id INT PRIMARY KEY, name VARCHAR(10), amount INT)
INSERT INTO @t VALUES (1,'apple',10)
INSERT INTO @t VALUES (2,'pear',20)
INSERT INTO @t VALUES (3,'plum',50)
SELECT 
t.id,
t.name,
t.amount,
CAST(t.amount AS FLOAT)/SUM(amount) OVER () AS [percent]
FROM @t t

This was a life saver yesterday - haveged

https://stackoverflow.com/a/47097219 Also here: http://www.issihosts.com/haveged/

VMWare PowerCLI backup of ESXi 5.5 hosts.

Set-ExecutionPolicy RemoteSigned
Add-PSSnapin VMware.VimAutomation.Core
$server="10.123.45.67"
$user="user"
$pass="1superSecretP@ssword!!!"
Connect-VIServer -Server $server -User $user -Password $pass
$now=Get-Date -format "yyyy-MM-dd_HH-mm-ss"
$newdir="b:\vmware-backups\$now"
new-item $newdir -itemtype directory
get-vmhost | get-vmhostfirmware -BackupConfiguration -DestinationPath $newdir
Disconnect-VIServer -Server $server -Confirm:$false

Apache POI 3.17 with Groovy/Grapes kept reporting javax.xml.stream.FactoryConfigurationError: Provider com.bea.xml.stream.EventFactory not found

Like this:

Caught: javax.xml.stream.FactoryConfigurationError: Provider com.bea.xml.stream.EventFactory not found
javax.xml.stream.FactoryConfigurationError: Provider com.bea.xml.stream.EventFactory not found
 at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.(PackagePropertiesMarshaller.java:41)
 at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:161)
 at org.apache.poi.openxml4j.opc.OPCPackage.(OPCPackage.java:141)
 at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:97)
 at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:324)
 at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:184)
 at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:149)
 at org.apache.poi.ss.usermodel.WorkbookFactory$create.call(Unknown Source)
 at poitest.run(poitest.groovy:14)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

Solution here. This works:

@GrabConfig(systemClassLoader=true)
@Grapes([
   @Grab(group='org.apache.poi', module='poi', version='3.17'),
   @Grab(group='org.apache.poi', module='poi-ooxml', version='3.17'),
   @Grab(group='org.apache.poi', module='ooxml-schemas', version='1.3'),
   @Grab(group='com.fasterxml', module='aalto-xml', version='1.1.0') 
])

import org.apache.poi.ss.usermodel.*
import org.apache.poi.ss.usermodel.WorkbookFactory

System.setProperty("javax.xml.stream.XMLInputFactory", "com.fasterxml.aalto.stax.InputFactoryImpl")
System.setProperty("javax.xml.stream.XMLOutputFactory", "com.fasterxml.aalto.stax.OutputFactoryImpl")
System.setProperty("javax.xml.stream.XMLEventFactory", "com.fasterxml.aalto.stax.EventFactoryImpl")

Workbook wb = WorkbookFactory.create(new FileInputStream("./blah.xlsx"))
println wb.dump()

Apply traffic light icons to a custom range of cells in Excel using VBA

Sub iconsets0()
Dim rg As Range
Dim iset As IconSetCondition
Dim positiveCells As Range
Dim negativeCells As Range
'observed range:
Set rg = Range("C7", "G25")
rg.FormatConditions.Delete
For Each c In rg
    If c.Value >= 0 Then
        If Not positiveCells Is Nothing Then
            Set positiveCells = Union(positiveCells, c)
        Else
            Set positiveCells = c
        End If
    End If
    If c.Value < 0 Then
        If Not negativeCells Is Nothing Then
            Set negativeCells = Union(negativeCells, c)
        Else
            Set negativeCells = c
        End If
    End If
Next c

' apply to cells with positive number values:
Set positiveSet = positiveCells.FormatConditions.AddIconSetCondition

With positiveSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = True
    .ShowIconOnly = False
End With
With positiveSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "10"
End With
With positiveSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "25"
End With

' apply to cells with negative number values:
Set negativeSet = negativeCells.FormatConditions.AddIconSetCondition

With negativeSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = False
    .ShowIconOnly = False
End With
With negativeSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-25"
End With
With negativeSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-10"
End With


End Sub

To install posh-gvm

1. Set JAVA_HOME to the root directory of the JDK.
2. Run this before invoking gvm:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12