The Now Platform® Washington DC release is live. Watch now!

Help
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Madison8
Tera Contributor

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;

find_real_file.png                   find_real_file.png

                                                                                                                                           

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

 

 

Comments
Sam Dey1
Kilo Guru

Nice works, saved me so much to time 🙂

Mwatkins
ServiceNow Employee
ServiceNow Employee

Note from product docs: "Important:System properties store configuration information that rarely or never changes. Each time you change or add a system property, the system flushes the cache to keep all nodes in the cluster in synch. This cache flush has a very high performance cost for one to ten minutes, which can potentially cause an outage if done excessively. To prevent such outages, do not use a system property to store configuration information that changes more than once or twice a month. Instead, use a custom table to store regularly changing configuration information."

https://docs.servicenow.com/bundle/helsinki-platform-administration/page/administer/reference-pages/...

Please đź‘Ť if Helpful

"Simplicity does not precede complexity, but follows it"

Madison8
Tera Contributor

So you’re saying because you set a new system property the overhead is too high?

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/reference-pages/task/t_AddAPropertyUsingSysPropsList.html

“If the Ignore cache check box is selected, the system flushes the server cache when the parameter is changed.”

I guess the main takeaways are I’m not regularly changing the system property and my initial point was to created environmentally specific settings and have a means to auto detect and use in workflow, I’m doing so without a custom table and the flush issue is no longer valid.

Thanks for your feedback.

Daniel Oderbolz
Tera Guru

Dear @Madison8 
I also think that ignore cache solves that issue. You have no other values that are dependent on this setting.

Can you please comment on this @Mwatkins ?

Mwatkins
ServiceNow Employee
ServiceNow Employee

That's right Daniel. If you have a property value that will be frequently updated (more than, say, once a month) and you know that there are no other caches that might depend on the value of the property, then set ignore_cache = true. That way the system will only flush the property-specific cache when the property is updated and not the whole Glide System cache, avoiding the performance hit mentioned in my previous comment.

(see https://www.servicenow.com/community/now-platform-articles/caching-data-to-improve-performance/tac-p... for more details about the ignore_cache setting)

Daniel Oderbolz
Tera Guru

@Mwatkins The only thing that is a bit strange is this comment in the documentation of gs.setProperty:

 

"Care should be taken when setting system properties (sys_properties) using this method as it causes a system-wide cache flush. Each flush can cause system degradation while the caches rebuild. If a value must be updated often, it should not be stored as a system property. In general, you should only place values in the sys_properties table that do not frequently change."

 

Does this imply that gs.setProperty ignores (no pun intended) ignore_cache?

(It seems as if this function just writes a fresh property and ignoring if a property of the given name ever existed. That would be bad.

 

Best

Daniel

 

Mwatkins
ServiceNow Employee
ServiceNow Employee

Thanks for the question, @Daniel Oderbolz! I did some testing and I read the related code. Here's the verdict:

 

gs.setProperty does not ignore the sys_properties.ignore_cache setting.

 

In other words, when someone changes the value of an existing property via the gs.setProperty(name, value) method, the current configuration of the sys_properties.ignore_cache field will be respected. If ignore_cache is set to "true" then no system-wide cache flush will occur. If ignore_cache is set to "false" then a system-wide cache flush will occur.

 

On the other hand, if someone creates a new property via the gs.setProperty(name, value) method, the new property will be created with sys_properties.ignore_cache = false and a system-wide cache flush will be triggered.

 

Hope that clears it up!

Madison8
Tera Contributor

Not sure why this is being debated, I never call setProperty but rather getProperty, as long as as ignore-cache is selected on the object property the. There is no question of performance!

Daniel Oderbolz
Tera Guru

Hi @Madison8 
Because if you want to use Properties to Cache Data (not really your Use Case), this really matters.

Every time you need to update the data in the Cache, you need to call setProperty().

And if you check in the Docs, it is not properly documented.

Thanks @Mwatkins for clarifying this.

Version history
Last update:
‎10-17-2020 06:52 PM
Updated by: