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;""