For custom attribute value pick lists, use only STATIC VARAs

[Michael A. Lowry]

Yesterday we finally got to the bottom of a problem that had been preventing us from using the AWI with our newly-upgraded v12.1 system. The root cause turned out to be related to the way custom attributes (a.k.a. grouping criteria) were defined in this system.

• In this system, we had defined several custom attributes inUC_CUSTOM_ATTRIBUTES.
• For two of these custom attributes, the data source for values was anEXEC VARAthat ran an SQL job.
• The SQL agent on which these jobs ran was down.

The Automation Engine apparently just assumes that the data sources for custom attribute values will always be accessible. If these data sources are STATIC VARAs, everything will work fine because the AE will be able to load the results from its own database. If dynamic VARAs are used however, these VARAs may not always return results, or may not return them in a timely fashion.  The AE does not appear to be designed to handle the scenario wherein a custom attribute data source does not return results immediately.

If the data source for a custom attribute value pick list is a dynamic VARA object (e.g., an EXEC or SQL VARA), and this VARA does not return results immediately (perhaps because an agent is down), the graphical user interfaces are affected in the following ways:

• Java User Interface: The JUI will work fine for most operations; however, if you try to load the value pick list for one of these custom attributes, the JUI will hang. It will be necessary to kill the app and restart it.
• Automic Web Interface: It will not be possible to log in to the Automic Web Interface. The login will time out, apparently because the AWI pre-caches all custom attribute value pick lists when the user is logging in.
  

I’m not sure whether Automic considers this a bug, but the documentation might provide a clue.

No reference values are available when there is no reference VARA object. This means that you cannot use the grouping criterion in the Process Monitoring perspective. The same applies when you use a dynamic variable that cannot be resolved (incorrect login info or invalid SQL).

The quoted passage (emphasis mine) suggests that problems with resolution of dynamic VARAs should result in the pick list’s being empty. In any event, this is not how it works in v12.1, and until this issue is resolved, it is probably advisable to use only STATIC VARA as the data source for custom attribute value pick lists, particularly if you rely on the AWI.

[Michael A. Lowry]

I have worked around this problem in the following way:

1. I created STATIC VARAs to seve as data sources for the values of these custom attributes.
   
2. I created SQL jobs to periodically repopulate these STATIC VARAs with the results from the original SQL queries.
   

This way, the AE will always be able to retrieve the data for custom attribute pick lists immediately, from local copies in the AE DB. These local copies of the data will be kept reasonably up-to-date and consistent with information in the external data source. Any failures encountered when refreshing these lists will not impact the AE.

Here is the post-process I am using to take the results from the SQL job and insert them into a static VARA. I thought this might be useful to others. Note that this bit of scripting relies on an include object to create the static VARA. I posted this include object in a separate discussion thread: Create static VARA if it does not already exist.

:SET &Static_VARA_Name#  = "SYSTEM.APPLICATION_PREFIXES.VARA_STATIC"
:SET &Static_VARA_Path#  = "APPS/SYSTEM/CLIENT_MANAGER/VARA"
:SET &Static_VARA_Title# = "Values for the APPLICATION_PREFIXES custom attribute"
:SET &Static_VARA_Overwrite# = "YES"
:INCLUDE UC0.CREATE_STATIC_VARA.JOBI

! Verify that at least one result was returned.
:SET &SQL_RC# = GET_UC_OBJECT_STATUS(, , "RETCODE")= 0
:IF &SQL_RC# = 0
:  PRINT "Query RC: 0"
! Populate static VARA with results from SQL query.
:  SET &RepHnd# = PREP_PROCESS_REPORT(, , "REP", "*", "COL=DELIMITER", "DELIMITER=@;

)
:  PROCESS &RepHnd#
:    SET &Value# = GET_PROCESS_LINE(&RepHnd#,1)
:    IF &Value# <>

"
:      PUT_VAR &VARA_NAME#,&Value#,""
:    ENDIF
:  ENDPROCESS
:  CLOSE_PROCESS &RepHnd#
:  ELSE
:    PRINT "SQL ERROR!"
:    PRINT "Query RC: &SQL_RC#"
:ENDIF

This SQL query returns just one column, which I am inserting into the key column as required by the custom attributes feature. For some reason :PUT_VAR requires specifying at least one value column too, even if it’s always empty as in this example.