Clarity Service Management

SUPER GRLOADER (z_loader_sdm_v103_en.zip) 

02-20-2017 09:36 AM

Hi Guys,

If you need a more efficient utility than the native GRLOADER utility and more practical than the PDM_LOAD and PDM_USERLOAD utilities, know the new LOADER_SDM.
Developed in SPEL and BATCH languages, this utility provides an easy way to load data files (.CSV) into the CA Service Desk Manager database.
This utility was developed using logic similar to the TEXT_API utility and it is not necessary to inform the ID's of the objects affected by the data synchronization. In addition, it is compatible with all objects in the SDM tool and not just 'configuration items', such as GRLOADER.
The utility is totally parametrizable and also allows the possibility to execute loads of several files simultaneously in a sequential way.
Soon I will publish an explanatory video explaining in detail its operation.
For now, download the source code and try it.

 

Last update 1.03 (02/22/2017):

- Included support for specifying composite key attributes in the 'key_in' option. Now you can specify more than one attribute in this option. This possibility is useful for data loads involving BREL/QREL/LREL objects that require at least two key attributes (Ex.: bmhier).
- Fixed bugs related to some types of data that were not currently supported in the 'key_in' option;
- Including sample model files of loading involving BREL object called 'template_bmhier.csv' and 'template_bmhier.properties' in the distribution of this utility.
- Fixed various bugs reported by users.

 

Update 1.02:

- Allow define object manager parameter (domsrvr) in configuration files.

 

 

Example of a data load configuration file involving the nr object

 

##############################################################################
## LOADER_SDM load settings used by the SPEL method z_loader_sdm
## - It is necessary to encode this file in the UTF-8 encoding.
## - This property file should use the .properties extension and the data file, must have the same name as the properties file, but with the .csv extension.
## - To make comments in this file, use the prefix '##'.
##############################################################################
## Author:            Daniel Becker Bighelini
## Modified:        20/02/2017
##############################################################################

## [REQUIRED] Sets the name of the object involved in SDM.
object_out=nr

## [REQUIRED] Defines the name of the key attributes that will be used in the CSV file to uniquely identify the record in SDM.
## To enter more than one key attribute, use the ';' tab.
key_in=serial

## [REQUIRED] Sets the name of all the attributes in CSV file.
attributes_in=serial;nome;classe;ativo;dns;funcao;data_ativacao;local

## [REQUIRED] Sets the name of all matching attributes in SDM. The attributes must be entered in the same order defined in the 'attributes_in' option.
attributes_out=serial_number;name;class;delete_flag;dns_name;assoc_har_serx.role;assoc_har_serx.active_date;location

## [OPTIONAL] Sets the name of the attributes in the CSV file that should be synchronized with SDM. It is not necessary to specify these attributes in the
## same order defined in the CSV file or 'attributes_in' option.
## DEFAULT: All attributes defined in the 'attributes_in' option.
attributes_sync_in=nome;serial;classe;ativo;dns;funcao;data_ativacao;local

## [OPTIONAL] Sets the filter that will be used to query the records in the SDM base to limit the records affected by the sync.
## DEFAULT: All existing records in the SDM object will be compared to the CSV file.
where_out=serial_number like 'SRV%'

## [OPTIONAL] Defines the userid of the privileged user in the SDM that will be used to perform the modifications in the database and to shoot notifications by email when the option 'action_on_fail = email'.
## DEFAULT: 'servicedesk'.
user_admin=servicedesk

## [OPTIONAL] Sets whether the simulation mode is activated during synchronization of data. If the value is true, no changes will be made to the SDM. The use of this option is recommended for loads being tested.
## DEFAULT: 'true'
simulation=true

## [OPTIONAL] Defines whether the routine should continue with the next case records an error occurs. 'FATALS' errors are not affected by this option.
## DEFAULT: 'false'
ignore=false

## [OPTIONAL] Defines whether new records should be created in SDM if they do not exist.
## DEFAULT: 'false'
create=false

## [OPTIONAL] Sets the date and time conversion format to be used in attributes of type DATE in the CSV file. The date format must have the same syntax described in the file '$NX_ROOT\bopcfg\www\web.cfg'. The 'am/pm' format is not yet supported.
## DEFAULT: 'MM/DD/YYYY HH:mm:ss'.
date_format_in=DDMMYYYYHHmm

## [OPTIONAL] Defines which action should be performed in case of an unexpected error. The possible values are 'incident', 'log' or 'email'. Being that to use the action 'email' it is necessary to have version 14.1 of the SDM tool and enable the 'email' notification method on the user defined in the 'user_admin' option.
## DEFAULT: 'log'
action_on_fail=log

## [OPTIONAL] Defines the attributes of the incident that will be generated if the parameter 'action_on_fail' is equal to 'incident'. To enter more than one attribute,
## use the ';' tab.
## DEFAULT: 'created_via=CMD;log_agent=servicedesk;customer=servicedesk;summary=Error LOADER_SDM;description=[error message]
incident_params=category=SISTEMA;affected_service=USD;affected_resource=SIWDES1300;group=DES-USD;assignee=Daniel Becker Bighelini;customer=Daniel Becker Bighelini;log_agent=Daniel Becker Bighelini;symptom_code=Produção.Rotina com erro;summary=Erro na rotina LOADER_SDM;description=Ocorreu um erro durante a execução da rotina LOADER_SDM.

## [OPTIONAL] Sets one or more notification recipient email addresses if the 'action_on_fail' parameter is equal to 'email'. To report more than one email address, use the ';' tab.
## DEFAULT: If a valid email is not defined with the option 'action_on_fail', the
## email will not be sent and an error will be logged.
email_address=daniel-bighelini@procergs.rs.gov.br

## [OPTIONAL] Sets how many attempts to make when errors occur of type "lock" of registry. The first attempt, in the event of an error, in 1 second, the second in 2, the third in 4, and so on exponential, until the limit is reached.
## DEFAULT: '5'.
retry_limit=5

## [OPTIONAL] Defines the tolerable standard deviation for the number of records processed. Eg.: If the last execution of the routine has processed 100 records and the level of tolerance is set at 20%, the next execution will be aborted if there are less than 80 records or more than 120.
## DEFAULT: '100%'
tolerance=100%

## [OPTIONAL] Sets the name of the used object manager (domsrvr) in SDM to execute the SPEL LOADER_SDM.
## DEFAULT: 'domsrvr'
object_manager=domsrvr

## [OPTIONAL] Sets the field delimiter used in the configuration and in the CSV file.
## DEFAULT: ';'
delimiter_field=;

## [OPTIONAL] Sets the field delimiter used in the configuration and in the CSV file.
## DEFAULT: '"'
delimiter_text="

## [OPTIONAL] Sets whether data mirroring mode should be run in SDM. If this option is enabled, all records found in SDM that do not exist in the file of data will be inactivated at the end of the process. This option uses the 'inactive_field_out' and 'inactive_value_out'.
## DEFAULT: 'false'
mirror=false

## [OPTIONAL] Defines the name of the attribute that determines whether a record is active or not in SDM. Required when the 'mirror' option is used.
inactive_field_out=delete_flag

## [OPTIONAL] Sets the value that should be set in the disable attribute when the object is disabled in SDM. Required when 'inactive_field_out' is used.
## Required when the 'mirror' option is used.
inactive_value_out=Inactive

 

CSV file example of loading data involving object nr

"serial";"nome";"classe";ativo;"dns";"funcao";"data_ativacao";local
"SRVBIGHELINI19";"BIGHELINI19";"Servidor Virtual";"Ativo";"";"Servidor do Bighelini";"28/02/2017 10:11:23";"01#DDPA"
"SRVTAU";"TAU4";"Servidor Virtual";"Ativo";"TAU.PROCERGS.COM.BR";"RS (SINTEGRA) 2";"29/04/2015 10:11:24";""
"SRVDELTA";"DELTA";"Servidor Físico";"Inativo";"DELTA.PROCERGS.COM.BR";"Servidor de NTP";28/02/2016 10:11:24;""

Statistics
0 Favorited
2 Views
1 Files
0 Shares
0 Downloads
Attachment(s)
zip file
z_loader_sdm_v103_en.zip   35K   1 version
Uploaded - 05-29-2019

Tags and Keywords

Comments

05-10-2017 06:47 AM

Kyle_R

Yes. It works.

Enjoy!

05-10-2017 01:55 AM

This looks fantastic. Well done! And thank you for sharing.

 

Does it handle custom fields? eg z_My_CI_field

 

Thanks, Kyle_R.

02-24-2017 07:06 AM

Got it.

The operation of your script is similar to what I used before LOADER.

Thanks by attention.

02-24-2017 04:17 AM

Hi Daniel,

I will definitively have a look to compare performance and come back to you.

It's always good learning to see how others are doing so thanks for this sharing

 

I have to say that our tool has been optimized for lookup and caching so performance is more that acceptable either for large amount of data.

We run it for many years now and never have any problem for performance and/or data integrity.

Configuration is at ease as you only have to specify the target object as parameter and all the attributes target  are simply piped headers in your tab delimited import file.

 

useridemail_address delete_flag|actbool|enumaccess_type|acctyp|sym

myuserid   myname@mycompany.com   0   Analyst

the first attribute is taken by default at the key for but we can either specify more key for upload like LREL and others just by adding [k] at the end of the corresponding header

 

last_name   first_name[k] notes

mylastname   myfirstname   VIP person

We support special field like this by specify this extra arguments at attributes level like [d] for converting datetimestamp from string to UTC server time or [t] to specify you need to be tenant aware

 

name install_date[d]

PQYI03623/06/2007 16:13:5

 

Unfortunately I can't share here as this is property of my company but that give you an idea how we do.

the tool is a simple exe so easy to add in another script or scheduled job

Everything is logged in a separate log file for tracking purpose too

 

/J

02-23-2017 04:18 PM

jmayer,

 

I also did it this way for webservices (REST & SOAP). But I ended up changing strategy when the amount of data and the runtime increased significantly.
Moreover, what motivated me to develop this loader was the ease of configuration of new loads, the greater security of execution and logically the performance. Loads that took more than hours to execute began to run in minutes.

Test and do a comparison. You will be amazed at the result.

02-22-2017 11:09 AM

Well we created our own tool in .net that will parse our file and use the SOAP web service to loop each record and insert/update the record based on the key we provide.

Avantage is  you fully go trough the object layer, you don't need to deref any data as all lookup function as be embedded in the tool and process both insert/update from one single file

Hope that give you an overview

/J

02-22-2017 10:13 AM

Hi Jerome,

 

Could you please explain on high level on how you use the web services to load data into SDM.

 

Thanks

Venkat

02-22-2017 02:11 AM

Thanks for sharing!

I personally use web service to load any data into SDM but will for sure have a look on your approach

/J

Related Entries and Links

No Related Resource entered.