Complete router information :

 

Resource ID Resource Name Product Name IP Address Personality State Cost Acquisition Date Expiry Date Warranty Expiry Date Resource Tag Resource Serial No Bar Code Vendor Area Site Location OS Type OS Version Cpu CPU Revision DRAM Size NVRAM Size Flash Size Config Register Processor BoardID Interface S.No Interface Name IPAddress Speed(Mbps)

 

SELECT ru.RESOURCEID "Resource ID", ru.NAME "Resource Name", pro.COMPONENTNAME "Product Name", ru.IPADDRESS "IP Address", ru.PERSONALITY "PERSONALITY", ressta.DISPLAYSTATE "State", rescost.COST "Cost", longtodate(res.ACQUISITIONDATE) "Acquisition Date",longtodate(res.EXPIRYDATE) "Expiry Date", longtodate(res.WARRANTYEXPIRY) "Warranty Expiry Date", res.ASSETTAG "Resource Tag", res.SERIALNO "Resource Serial No", res.BARCODE "Bar Code", sdoo.NAME "Vendor", rd.REGIONNAME "Area", sdo.NAME "Site", reslo.LOCATION "Location", exru.OSTYPE "OS Type",exru.OSVERSION "OS Version", cru.CPU "CPU", cru.CPUREVISION "CPU Revision",cru.DRAMSIZE "DRAM Size", cru.NVRAMSIZE "NVRAM Size", cru.FLASHSIZE "Flash Size", cru.CONFIGREGISTER "Config Register", cru.PROCESSORBOARDID "Processor BoardID", ruin.IFINDEX "Interface S.No", ruin.IFDESCR "Interface Name", ruin.ADDRESS "IPAddress", ruin.IFSPEED "Speed(Mbps)" FROM router ru LEFT JOIN resources res on ru.resourceid=res.resourceid LEFT JOIN ciscorouter cru on res.resourceid=cru.resourceid LEFT JOIN extendedrouter exru on cru.resourceid=exru.resourceid LEFT JOIN routerinterfaces ruin on exru.resourceid=ruin.resourceid LEFT JOIN resourcecost rescost on res.resourceid=rescost.resourceid LEFT JOIN resourcestate ressta on res.resourcestateid=ressta.resourcestateid LEFT JOIN resourcelocation reslo on res.resourceid=reslo.resourceid LEFT JOIN componentdefinition pro on res.componentid=pro.componentid LEFT JOIN regiondefinition rd on reslo.regionid=rd.regionid LEFT JOIN sitedefinition sd on reslo.siteid=sd.siteid LEFT JOIN sdorganization sdo on sd.siteid=sdo.org_id LEFT JOIN vendordefinition vd on res.vendorid=vd.vendorid LEFT JOIN sdorganization sdoo on vd.vendorid=sdoo.org_id

 

 

For complete Switch information :

 

 

Resource ID Resource Name Product Name State Cost Acquisition Date Expiry Date Warranty Expiry Date Resource Tag Resource Serial No Bar Code Vendor Area Site Location Rootcost Numports IP Address Personality OSType OSVersion Cpu DRAM Size NVRAM Size Flash Size Config Register Processor BoardID Serial Number Motherboard Assembly Number Motherboard Serial Number Motherboard Revision Number Power Supply Part Number Model Revision Number Port Index Port Name Operational State Admin State Speed(Mbps)

 

 

select swi.RESOURCEID "Resource ID", swi.NAME "Resource Name", pro.COMPONENTNAME "Product Name", ressta.DISPLAYSTATE "State", rescost.COST "Cost", longtodate(res.ACQUISITIONDATE) "Acquisition Date",longtodate(res.EXPIRYDATE) "Expiry Date", longtodate(res.WARRANTYEXPIRY) "Warranty Expiry Date", res.ASSETTAG "Resource Tag", res.SERIALNO "Resource Serial No", res.BARCODE "Bar Code", sdoo.NAME "Vendor", rd.REGIONNAME "Area", sdo.NAME "Site", reslo.LOCATION "Location", swi.ROOTCOST "ROOTCOST", swi.NUMPORTS "NUMPORTS", swi.IPADDRESS "IP Address", swi.PERSONALITY "PERSONALITY",exswi.OSTYPE "OSType", exswi.OSVERSION "OSVersion",cswi.CPU "CPU", cswi.DRAMSIZE "DRAM Size", cswi.NVRAMSIZE "NVRAM Size", cswi.FLASHSIZE "Flash Size", cswi.CONFIGREGISTER "Config Register", cswi.PROCESSORBOARDID "Processor BoardID", cswi.SERIALNO "Serial Number",cswi.MBOARDASSEMBLYNO "Motherboard Assembly Number", cswi.MBOARDSERIALNO "Motherboard Serial Number ", cswi.MBOARDREVISIONNO "Motherboard Revision Number", cswi.POWERSUPPLYPARTNO "Power Supply Part Number", cswi.MODELREVISIONNO "Model Revision Number", swip.PORTIFINDEX "Port Index", swip.PORTIFDESCR "Port Name", swip.PORTOPERSTATE "Operational State", swip.PORTOPERSTATE "Admin State",swip.PORTIFSPEED "Speed(Mbps)" from switch swi LEFT JOIN switchport swip on swi.resourceid=swip.resourceid LEFT JOIN ciscoswitch cswi on swip.resourceid=cswi.resourceid LEFT JOIN extendedswitch exswi on cswi.resourceid=exswi.resourceid LEFT JOIN resources res on exswi.resourceid=res.resourceid LEFT JOIN resourcecost rescost on res.resourceid=rescost.resourceid LEFT JOIN resourcestate ressta on res.resourcestateid=ressta.resourcestateid LEFT JOIN resourcelocation reslo on res.resourceid=reslo.resourceid LEFT JOIN componentdefinition pro on res.componentid=pro.componentid LEFT JOIN regiondefinition rd on reslo.regionid=rd.regionid LEFT JOIN sitedefinition sd on reslo.siteid=sd.siteid LEFT JOIN sdorganization sdo on sd.siteid=sdo.org_id LEFT JOIN vendordefinition vd on res.vendorid=vd.vendorid LEFT JOIN sdorganization sdoo on vd.vendorid=sdoo.org_id

 

 

 

regards,

Rachana