topic Re: DB variable for retrieving an interface IP in Controllers

DB variable for retrieving an interface IP

Dave Lewis — Mon, 12 Dec 2016 16:14:24 GMT

Hello,

I've been trying to use a DB variable to retrieve the IP address for a specific loopback interface but without much success. I've 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 I try 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 do I have to re-sync the target devices or schedule an archive or even restart Prime?

Cheers,

Dave

Re: DB variable for retrieving an interface IP

Spencer Zier — Mon, 12 Dec 2016 18:06:01 GMT

Dave, yes, 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 I 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.

Re: DB variable for retrieving an interface IP

Dave Lewis — Wed, 14 Dec 2016 11:18:00 GMT

Thanks Spencer,

I've managed to create other DB lookup variables which work so I'm 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 I just can't get the information I need whatever I try but I 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?

I ask because 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 I'm trying to extract but I haven't found any documentation on the schema or any way to interrogate the DB at this level?

Another use case I'm struggling with at the moment is extracting the BGP AS number from a device, the bgpVrfSettings table is empty and I haven't found it in any of the other tables so far which leaves me with 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?

Any help with these two use cases would be much appreciated.

Cheers,

Dave

Re: DB variable for retrieving an interface IP

Dave Lewis — Wed, 14 Dec 2016 15:37:02 GMT

Think I've found the answer to the question about logs - in ifm_template.log I 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']]

So I guess I was along the right lines with the ipaddress table not being available to the db variables?

Maybe if I knew 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 ...

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

Re: DB variable for retrieving an interface IP

Spencer Zier — Wed, 14 Dec 2016 17:03:25 GMT

Yeah, 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. I don't think we have a place in the UI where the entirety of the model is exposed. I don't think we 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 I'm 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.

Re: DB variable for retrieving an interface IP

Dave Lewis — Thu, 15 Dec 2016 16:57:12 GMT

Thanks Spencer,

Its a shame because I think that's where the real power of Prime CLI templates lies (within the DB variables), to provide a sort of poor-man's NSO.

A quick work around would be to provide an API call that allows you to run an SQL query and retrieve the results. That'd be awesome.

Cheers,

Dave

Re: DB variable for retrieving an interface IP

mstraessle — Tue, 27 Dec 2016 13:57:20 GMT

Hi Dave

And did you get some answer on the question to the mail-alias?

I have a problem which is quite the same: I want to look for the access-vlan on a switchport and allow to deploy some conifglets on only specific port in access-vlan 10-20. I know the information is in the DB (can be shown on Monitor / Managed Elements / Network Devices / Device Groups / All Devices / SWITCH under Configuration> Security> TrustSec> Wired 802_1x >, but I not know how to access them since I have no clue what object to look for (it is not VlanId=select STR(u.vlanId) from VLANInterface u where u.owningEntityId = since this is only the management VLAN ID...).

Thanks, Marco

Re: DB variable for retrieving an interface IP

Dave Lewis — Thu, 29 Dec 2016 13:30:14 GMT

Hi Marco,

Unfortunately I didn't bother sending an email because the documentation says they only recommend using their provided variables. I looked through the SQL tables in ncsDiag and didn't see anything interesting related to dot1x. Maybe the 'accessvlan' field in the 'vlanswitchportsettings' table gets dynamically updated once ISE has assigned one? Until Cisco publish the EJBQL model schema we'll be in the dark I'm afraid.

See attached screenshot - I'm not sure if that accessvlan is the configured one from 'switchport access vlan' or the dynamically assigned operational vlan.

Sorry I can't be of much help. I did use the 'I wish this page would' feature for a different CLI template problem I had and Cisco did reply to me to say they've logged a feature request so maybe its worth logging one and seeing what happens!

Thanks,

Dave