How to deploy PI 3.1 CLI Templates to specific Interfaces / Ports

[mstraessle]

I hava a similar question like was already asked but without any useful answer in this Entry:

https://supportforums.cisco.com/discussion/12318371/how-deploy-prime-infrastructure-template-selected-interfacesports

I have to deploy IBNS 2.0 on all access-ports of any switches. The problem is, if I use simple templates, also trunk ports will be deployed with the interface template I want to be active, which is not working.

So I am looking for "how to use the system defined "Trunk Ports" group in the CLI templates, to sucessfully exclude all trunk ports on any switches.

Does anybody have an idea how to solve or use this "Variable" in a CLI Template?

Thanks for any hint.

[Ethan Grinnell]

You'll probly have to use the DB variable type and some creative SQL queries, I don't recall the exact table name, but one of them stores the interface operational mode, look for ones that are access ports. Use the ncsDiag.do page to browse the SQL tables.

I've done some cool things with DB vars. Tbe built in ones aren't super useful though. Tip: To return multiple things concatenate the results with a delimiter. For example, "interface-CDPNeighborName" with - as the delimiter.

If all of your trunks connect to devices that speak CDP and no CDP devices are on access ports, then you can make a list of interfaces with CDP neighbors and exclude those from the all interface list, the remaining interfaces are access ports. Probably not going to apply in most cases, but who knows.

[Ethan Grinnell · Accepted solution]

Now that I am sitting at my computer I'd like to expand on my previous suggestion.

Browse to https://PrimeServerName/webacs/ncsDiag.do and DB Table List when logged in as root to browse the table names. When I am trying to build a new DB variable I spend most of the time trying to figure out the case used for the column names. I'm no SQL expert, this aspect of it is so frustrating!

At any rate, try this:
In /opt/CSCOlumos/conf/ifm/template/inventoryTagsInTemplate/CLITemplateDbVariablesQuery.properties add this line:

AccessInterface=select u.name from VLANSwitchportSettings u where u.operationalVlanMode = '2' and u.owningEntityId = --?%-- order by u.name

Create a new CLI Template that uses your new DB variable. Here's one that worked for me:

#foreach($Interface in $AccessInterface)
    interface $Interface
    description Hi I'm an access interface
#end

One irritation that I have found with VTL is that a single match is returned as a string, but multiple matches are returned as an array/list. So I force everything to an array, that way the foreach loop doesn't break if a single item is returned.

**Edit** Changed method to detect string/array, much better

##Determine if this is a string or an array of strings [Single string does not make VTL create an array, VTL treats multiple strings differently and creates an array of strings] - So use split to force everything to an array
#set($AccessInterface = $AccessInterface.split("#@#", -1))
#foreach($Interface in $AccessInterface)
    interface $Interface
    description Hi I'm an access interface
#end

Other useful queries that I've used:

702WTrunkVlanIDs=select u.nativeVLAN || '-' || u.allowedVLANs.vlanIds || '-' || u.name from VLANTrunkingSettings u, CDPNeighborInfo y where y.neighborSysDescription like '%AIR-CAP702W%' and y.ifName = u.name and y.owningEntityId = u.owningEntityId and u.owningEntityId = --?%-- order by u.name

CDPNeighbors=select u.neighborDeviceId || ',' || u.ifName from CDPNeighborInfo u where u.owningEntityId =

SwitchStack=select u.stackMemberNumber || ','|| u.state || ',' || u.softwareImage from StackSwitch u where u.owningEntityId =

[Ralf Becker]

Another usefull query:

It can be used to deploy templates on port groups.

GroupCiscoApPorts=select v.name from GroupMemberRef r, VLANSwitchportSettings v where v.configuredSwitchport.instanceId = r.memberId and r.group.instanceName='CiscoApPorts' and v.owningEntityId =--?%-- order by v.name

"CiscoApPort" is the name of a port group (see Inventory > Port Groups)