DB variable for retrieving an interface IP

• Trying to use a DB variable to retrieve the IP address for a specific loopback interface but without much success. Have tried variations on this added to the CLITemplateDbVariablesQuery.properties file. 
• Lo11IP=select ip.ipaddress_address from ipaddress ip where ip.name like 'Loopback11' AND ip.owningEntityId =
• Loopback11IP=select STR(u.ipaddress_address) from ipaddress u where u.name like 'Loopback11' AND u.owningEntityId = 
• But whenever tried to use these in a CLI template the preview just shows $variable_name instead of the values. The above commands work fine using dbquery in ncsdiag.
• Also, on a side note - when you add a new definition into CLITemplateDbVariablesQuery.properties should they work immediately or is it needed to re-sync the target devices or schedule an archive or even restart Prime.

You'll need to restart in order for pretty much any change in the conf folder to take effect.  Most configurations are read in during startup and not read from again until the next restart.  If the values look incorrect, then that's where doing a sync to refresh the inventory in Prime would come in handy (note that we're working on the ability to trigger a sync via the API for 3.2) but would also recommend checking that the query you wrote is correct. 

Lastly, be sure that in your template you've defined the variable as a DB type variable with a default value of your new property (in your case, Lo11IP or Loopback11IP). 

See the PI user guide's section on this for more details.

• Managed to create other DB lookup variables which work so okay with the general concept and how to use them within templates but wasn't aware PI needed a restart for changes to take effect (couldn't find that in the documentation).
• For this particular task just can't get the information needed whatever tried but wonder if its to do with the seeming disparity between the tables displayed in ncsDiag and the tables used by the lookups. 
• For example, in ncsDiag the following query works exactly as expected: 
• "select u.ipaddress_address from ipaddress u where u.name like 'Loopback11' and u.owningEntityId = blah" 
• but isn't working as a DB variable - perhaps the ipaddress table isn't exposed in the same way.
•  As the db variables appear to use different tables (DB views?) that aren't visible in ncsDiag - InterfaceProtocolEndpoint , EthernetProtocolEndpoint and EthernetProtocolEndpointExtended aren't visible in DB query and ManagedNetworkElement seems to have different column structure (productSeries and managementAddress for example). 
• Perhaps one of the above views contains the IP address trying to extract but haven't found any documentation on the schema or any way to interrogate the DB at this level. 
• Another use case is extracting the BGP AS number from a device, the bgpVrfSettings table is empty and  haven't found it in any of the other tables so far which leaves downloading the latest running config for a device and parsing that which isn't very elegant or fast. 
• Is there any logging of DB variable usage or any debug operations that could pinpoint the problem.

Found the answer to the question about logs - in ifm_template.log  found entries similar to:

 [2016-12-14 14:38:08,953] [http-nio-443-exec-15] [service] [ERROR] - IFM_TEMPLATE_EXCEPTION: [com.cisco.xmp.persistence.common.util.DMMCRUDException:,message=errorId=20,componentName=CRUD executeQuery Failed! errorId=20,componentName=CRUD executeQuery Failed! ipaddress is not mapped [select u.ipaddress_address from ipaddress u where u.name like 'Loopback11' AND u.owningEntityId = '187661132_x.x.x.x']]

If the fully qualified path to the table it would work. Something like:

select u.ipaddress_address from com.cisco.xmp.model.foundation.connectivity.ipAddress u where ...

or

select u.ipaddress_address from com.cisco.ifm.model.connectivity.ethernet.ipAddress u where ...

It's not actually SQL queries defined in that file (which is what the dbQuery page linked to from ncsDiag runs).  Instead, they're EJB queries that query objects from our model.  We don't have a place in the UI where the entirety of the model is exposed. Also we don't have an ncsDiag/dbQuery like page to run EJB queries either. 

You can try reaching out to ask-prime-infrastructure@cisco.com to get support from the config template team, but not sure if you'll get support there, based on what the docs say ("only advanced developers should attempt this. We recommend you use the variables defined in the [f]ile only") they might decline to help.