Hi,

Recently I came up with the idea to store values specific to an instances for a workflow all in one place as a JSON object on the sys_property table.

The use case was due to the use of a JDBC connector in workflow and the settings (midserver/username/password) all needing to differ depending on the instance (DEV/UAT/PROD).

The basic concept is as follows;

Create a new system property

System Property

Table: sys_property

Name: sql.account.provisioning.config

Type: string

{
    "devInstanceName": { // in sys_property instance_name
        "environment": "dev",
        "sqlInsert": {
            "midServer": "mid_server_for_dev",
            "server": "sql_server_dev",
            "DbName": "sql_db_dev",
            "username": "username",
            "password": "password",
            "storedProc": "x.dbo.x"
        }
    },
    "UatInstanceName": { // in sys_property instance_name
         "environment": "uat",
         "sqlInsert": {
            "midServer": "mid_server_for_uat",
            "server": "sql_server_uat",
            "DbName": "sql_db_uat",
            "username": "username",
            "password": "SEE: sql.account.provisioning.sqlInsert.password", // Password2 Type
            "storedProc": "x.dbo.x"
        }
    },
    "prodInstanceName": { // in sys_property instance_name
         "environment": "prod",
         "sqlInsert": {
            "midServer": "mid_server_for_prod",
            "server": "sql_server_prod",
            "DbName": "sql_db_prod",
            "username": "username",
            "password": "SEE: sql.account.provisioning.sqlInsert.password",// Password2 Type
            "storedProc": "x.dbo.x"
        }
    },
    "default": {
        "name": ""
    }
}

Usage

In my workflow, I set my first activity to map the current instance to the corresponding instance settings as above, accessible via workflow.scratchpad.config throughout the workflow;

Then in a "Run Script' later in my WF;

config = workflow.scratchpad.config.sqlInsert;

var j = new JDBCProbe(config.midServer);
j.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
j.setConnectionString("jdbc:sqlserver://" + config.server + ";databaseName=" + config.DbName + ";user=" + config.username + ";password=" + config.password);
j.setFunction("custom");
j.addParameter("work");
j.addParameter("sql_statement", "USE sqladmin; exec "+config.storedProc+" @snowformid='"+current.number+"',@payload ='"+ payload+ "',@processed = '0'");
var result = j.create();

Conclusion

One could argue that this could be done via multiple sys_properties with a similar naming convention but I feel that method would be harder to adopt and maintain.

I see the main benefits demonstrated are that it allows for seamless deployment whilst following the standard SDLC model, but beyond that, it would allow production changes without the need to alter the workflow (should the password update for example), this would mitigate the need for a change request if your company employs strict change practices, thus making a dev task into a admin task.

I’m sure there are other applications and for me it just makes sense, it would be great to hear some feedback or for ServiceNOW to perhaps add a JSON type..

Thanks for reading,

Madison Courto