Tuesday, May 22, 2018

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

Tuesday, April 24, 2018

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()

Wednesday, March 21, 2018

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



Thursday, March 15, 2018

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

Friday, February 23, 2018

Importing a PKCS12 keystore into a JKS keystore

Wow, I did not know keytool can actually do this.

keytool -importkeystore -destkeystore keystore.jks -srckeystore keystore.p12 -alias tomcat

Via Jim Connor's Blog.

Wednesday, February 21, 2018

Grails: adjust Hibernate settings to prevent "org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement"

Grails 3.3.2 project. GORM, Hibernate, JTDS (with Microsoft SQL Server). I kept getting the following exception:


Error executing DDL via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:524)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:470)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:273)
        at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:203)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:65)
        at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:478)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:422)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:711)
        at org.grails.orm.hibernate.cfg.HibernateMappingContextConfiguration.buildSessionFactory(HibernateMappingContextConfiguration.java:274)
        at org.grails.orm.hibernate.connections.HibernateConnectionSourceFactory.create(HibernateConnectionSourceFactory.java:86)
        at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:39)
        at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:23)
        at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:64)
        at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:52)
        at org.grails.datastore.mapping.core.connections.ConnectionSourcesInitializer.create(ConnectionSourcesInitializer.groovy:24)
        at org.grails.orm.hibernate.HibernateDatastore.(HibernateDatastore.java:196)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.springsource.loaded.ri.ReflectiveInterceptor.jlrConstructorNewInstance(ReflectiveInterceptor.java:1076)
        at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:142)
(...)

Adding the default_catalog: (name of the database here) and default_schema: (name of the schema, usually dbo) underneath the hibernate: section in application.yml solved the issue.