"AssetID" field in Entity_Physical table???

smasher953

Anyone know if there is a use (currently or planned) for the "AssetID" field located within the Entity_Physical table in the MS SQL database?  I have a need to parse out the SNMP location/chassisID field and place it into the AssetID field.  It doesn't appear to be populated with any values for any of my devices currently. 

I'd like to parse out our internal inventory tag # (which we populate on each device using the "snmp-server chassis-id 23014444,23015555" field) and stick that value into the AssetID field. 

I specifically need to do this because we have a large # of Cisco 3750 stacks and have a need to associate our internal inventory tag # with the specific serial # of the switch in the stack.  I have just about everything working so far with some custom sql code and some temp tables (not actually updating the true AssetID field in Entity_Physical yet). 

 

--greg

smasher953

So I ended up updating the AssetID field in the Entity_Physical table with my local inventory tag.  I created a stored procedure that I can call from any SQL query and this should update it. 

FYI for others out there we put a local inventory tag into the chassis-id SNMP field on all of our Cisco switches in the following format (23014444,23015555,2301666,etc).  The first inventory tag # is the master switch in the 3750 stack, 2nd is switch 2 in the stack, etc.  If the switch fails to the slave at any point the local inventory tag will be out of sync.....but since we provision switches in the stack it should auto-correct itself. 

This procedure will parse those custom chassis-id fields and insert them to the correct switch # and serial # in the Entity_Physical table.  Call it from your SQL query by doing "exec usp_Custom_INVENTORY_TAG_SPLIT". 

Any by no means am I a SQL person....so the code below is probably not the best way to be doing it, but it works for me....If anyone wants to improve please do so and share! 



-- Procedure: dbo.usp_Custom_INVENTORY_TAG_SPLIT

/* DROP PROCEDURE dbo.usp_Custom_INVENTORY_TAG_SPLIT
GO */

CREATE PROCEDURE dbo.usp_Custom_INVENTORY_TAG_SPLIT
AS
select
  [NODES].[NodeCaption],
  [NODES].[MachineType],
  [NODES].[SysLocation],
  [NODES].[SysName],
  [NODES].[SysContact],
  [NODES].[AgentIP],
  [CISCO_CHASSIS].[ChassisID],
  [CISCO_CHASSIS].[ChassisSerialNumber],
  ENTITY_PHYSICAL.*
into #tempSplitTable3
from (nodes
  left join cisco_chassis on
    nodes.nodeid = cisco_chassis.nodeid
  right join entity_physical on
    entity_physical.nodeid = nodes.nodeid)
where [ENTITY_PHYSICAL].[Class]=3

CREATE TABLE #Tally (ID int identity(1,1))
INSERT #Tally DEFAULT VALUES
WHILE SCOPE_IDENTITY() < 8000 INSERT #Tally DEFAULT VALUES

SELECT *,
NullIf(SubString(',' + ChassisID + ',' , ID , CharIndex(',' , ',' + ChassisID + ',' , ID) - ID) , '') AS ChassisID_SPLIT
into #tempSplitTable4 FROM #tempSplitTable3, #Tally
WHERE (ID <= Len(',' + ChassisID + ',') AND SubString(',' + ChassisID + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + ChassisID + ',' , ID) - ID > 0)

select *  into #tempSplitTable5 from #tempSplitTable4 where ((ISNUMERIC(Name)=1) AND (substring(ChassisID,CAST(NAME AS INT)*9-8,8)=ChassisID_SPLIT)) OR (ChassisID=ChassisID_SPLIT)
order by SysName

UPDATE #tempSplitTable3
  SET #tempSplitTable3.AssetID=#tempSplitTable5.ChassisID_SPLIT
FROM #tempSplitTable5
WHERE ((ISNUMERIC(#tempSplitTable3.Name)=1) AND (substring(#tempSplitTable3.ChassisID,CAST(#tempSplitTable3.NAME AS INT)*9-8,8)=#tempSplitTable5.ChassisID_SPLIT)) OR (#tempSplitTable3.ChassisID=#tempSplitTable5.ChassisID_SPLIT)

UPDATE Entity_Physical
  SET Entity_Physical.AssetID=#tempSplitTable3.AssetID
FROM #tempSplitTable3
WHERE Entity_Physical.Serial=#tempSplitTable3.Serial

drop table #tempSplitTable5
drop table #tempSplitTable4
drop table #tempSplitTable3
drop table #Tally
GO