Item is delivered, either partially or in whole

New orders file created with rfe orders/{orderno}. Each file must adhere to the OrderFileFormat.

An order file example follows :-

date:7/11/2012
supplier:HP
ticket: rt56452
1.item:HP 8300CMT i5 3.2GHz, 8GB, 500GB
1.warranty:3yr
1.quantity:2
1.price:330.88
1.sno:CZC2457YM0
1.sno:CZC246Y8JH
1.delivered:19/11/2012
1.budget:747DIV/G40588
1.category:desktop
1.requestor:ascobie
2.item:Dell Ultrasharp 20" 2001FP
2.warranty:3 years
2.quantity:1
2.price:439.00
2.sno:
2.delivered:
2.category:monitor
2.budget:747DIV/G40588
vat:1.20

In the following, the term "orderline" will refer to each section of an orders file identified by the same starting ID. So, in the example above, we have orderline 1 which is for 2 of HP 8300 CMT desktops, and orderline 2 which is for 1 of Dell Ultrasharp monitor.

Note that item description field will be as entered by purchaser. Although it should include both manufacturer and model and may include details such as disk size and memory size, it is of no fixed format and should not be interpreted by any code.

In the example above, two desktops have been delivered. The monitor has yet to be delivered, so has a blank serial number and delivery date.

An order file is sync'd with the inventory tables as part of a periodic sweep, every 5 minutes, through all orders looking for updated files. (The periodic sweep code compares the purchaseorder->last_loaded field with the mtime of the orders file to determine whether a sync is required). The script that performs this sweep is called ordersync.

The ordersync script will :-

  • create a row in the 'purchaseorder' table, recording PO, supplier, order date, and VAT. (Note that this means that the VAT value applies to all items in a purchase order)
  • For each orderline with a valid "delivered" field
    • For each blank serial number (blank will be assumed for any missing sno lines)
      • a row in the 'item' table is created, recording description, cost, category, budget, warranty, delivered date, orderline. managed_type='unknown', owner='informatics', allocated_type='unallocated'
    • For each serial number in the orderline
      • a row will be created as above, also recording serial
      • if the item is a system (identified by 'category' field being one of server | desktop | laptop | tablet | storagearray | networkswitch | wap
        • the 'is_a_system' field in the 'item' row is set to true

Note that some items may not have a serial number (eg a mouse), but we still want to record their purchase details so an 'item' table row is still created for that item.

Further items are delivered

The serial number and delivery date of the delivered items are manually added to the orders file using rfe orders/{orderno}, eg :-

2.delivered:02/12/2012
2.sno:HU-0C0881-46633-62M-03ML

A resync (either sync or async as before) will occur.

The ordersync script will :-

  • Remove any 'item' table rows associated with this order that have blank serial numbers. (Note that this means that you can't store state (eg allocation) for an item with a blank serial number.)
  • Perform the operations described in the previous section.

Note that whilst serial numbers need not be unique within the inventory, they must be unique within an individual purchase order.

Order is modified

'date', 'supplier', 'ticket', 'vat' changed
the relevant field in the appropriate 'purchase_order' row will be updated
'item', 'warranty', 'price', 'delivered', 'budget', 'category', 'requestor' changed
the relevant field for each 'item' row associated with the orderline is updated.
'sno' added
If the number of non blank serial numbers for the relevant orderline doesn't exceed the 'quantity' field for the orderline, a row in the 'item' table is created (as for 'Order is delivered'). The number of items for the orderline with blank serial numbers will be reduced.
'sno' modified
A new row in the 'item' table is created (as for 'Order is delivered'). This leaves an orphan row in the 'item' table for the serial number that is no longer listed in the orders file. This row may well have valid data (eg allocated_to, space) that was recorded against this serial number before it was discovered the serial number was incorrectly recorded. If there is any such data, or a mac address has been associated with this serial number, the row is marked an orphan ('orphaned' field set to true). See TODO about orphaned items.

Kit is sold on to a research grant

Splitting an item in two - eg so an item of 10 PCs can be split into two items of 5 PCs each is supported. This is the correct way to mark that a school has re-sold a desktop, bought on a central school budget, to a research grant.

TODO: This requires further thought and expansion. Does it still happen??

05/07/19 GOT TO HERE

Mac addresses

It is important to capture the mac addresses of all computing systems such as desktops, servers, laptops, tablets etc - as we use the mac addresses to track location of such kit, in addition to feeding DHCP.

The mac addresses of desktops bought as part of the Select PC agreement will be loaded into the inventory automatically (see below). The mac addresses of all other kit will need to be loaded manually. Multiple mac addresses can be specified per machine.

You can associate a macaddr with a system using the "ii addmacaddr" command. ( Usage: ii addmacaddr [--hostname hostname] [--serial serialno] [--iid id] macaddr1 [macaddr2 ...] )

ii addmacaddr --serial 13J06TP 4e:82:BC:98:03:3B

Mac addresses will no longer be specified in the profiles of LCFG desktops.

Select PC mac addresses

As part of the Select PC agreement, the suppliers produce a periodic spreadsheet (daily?) that lists all PCs ordered by the University under the agreement - providing serial number, cost, purchase order and, crucially, mac address.

This spreadsheet, and historic spreadsheets, for each Select PC supplier are merged into one CSV file which is periodically loaded into the 'supplierreport' table. On reload of the 'supplierreport' table, a script will create any necessary rows in the 'macaddr' table for PCs that we have order details - linking against serial number.

This is always going to be a fragile solution as we have no control over the provision of the upstream spreadsheet. In any case, it doesn't work for non SelectPC kit. We should provide an alternative route for grabbing MAC addresses. One easy solution is to read MAC addresses from the BIOS, but that requires manual entry of the MAC address. A better solution would be to PXE boot devices into a system (based on the LCFG installroot). That could use a modified version of the clientreport script to populate the inventory system automatically with MAC addresses that it harvests. A step further would be to allow the user to set the hostname,allocated_to and managed_type fields so that they wouldn't have to run 'invedit' later.

Hostnames

  • Hostnames are unique across active systems in the inventory.
  • Hostnames may be reused.
  • Self managed dynamic address machines can have a hostname (to make it easier for support staff to refer to specific machines)
  • The inventory is the definitive location for hostname information - not the DNS as there may be machines with hostnames that aren't in DNS
    • But... what about cnames? These don't need to be in inventory (as they're not physical), but do need to be in DNS
    • Ditto VMs don't need to be inventory, but need to be in DNS
    • But self-managed dynamic machines need hostnames but won't be in DNS
    • => neither inventory nor DNS can be definitive - could provide a tool to hunt in both places?
  • Short hostnames will be postfixed with '.inf.ed.ac.uk', otherwise FQDN hostnames should be used

There will be an 'ii' command to check whether a particular hostname is currently in use.

Logbook

Each purchased system has a logbook, with entries of the form 'date', 'changetype', 'newvalue'. Possible changetypes are creation, location, allocated, managed_type, hostname, comment, disposed, fault

You can display the logbook :-

shell$ ii logbook --hostname benbecula
2010-10-29   creation  
2010-10-31   location   if-2.09
2010-10-31   allocated ascobie
2010-10-31   hostname benbecula.inf.ed.ac.uk
2010-10-31   managed_type dice
2010-10-31   comment  provided under academic/co scheme
2012-11-04   location   if-2.23
2012-11-05   allocated unallocated
2013-03-04   fault        motherboard failed
2013-06-04   disposed 

You can add a comment to a system's logbook :-

eg ii set --serial C3U88FjU --allocated mahesh --comment "Temporarily lent to Mahesh for some network experiments"

You can also add a fault entry to the logbook :-

ii set --hostname benbecula --fault "motherboard failed"

LCFG profiles

  • No change to DICE desktops, except that mac-addresses no longer specified in profile
  • No change to self-managed static - still need a profile to configure DHCP - except that mac addresses no longer specified in profile
    • (In long term, ideally wouldn't need profile)
  • No profile required or allowed for self-managed dynamic.
  • Profile names should no longer be used as the definitive namespace for hostnames

Bare machine to DICE

Eg

  • ii set --serial XY123CF --newhostname bottle.inf.ed.ac.uk --allocated carol --type dice
  • rfe -n -t lcfg/blib lcfg/bottle
    • profiles as before, but no macaddr in profile

Allocation

Allocation to a user

Eg. ii set --serial CZC2457YM0 --allocated ascobie

--allocated {user} implies --allocated_type person

A user must be a valid UUN (so can link to a user table derived from the School DB)

item->allocated_type = 'person'
item->allocated = 'ascobie'
create logbook entry

Allocation to a function (one of server | podium | lab | public | unallocated | junk | other )

eg ii set --hostname squirrel --allocatetype podium

item->allocated_type = 'podium'
item->allocated = NULL (via trigger?)
create logbook entry

Allocation to 'junk' is sticky. Once set, can't be otherwise allocated without privileges. No DHCP header will be created for 'junk'd machines. (See Kit is Junked)

TODO: Chris suggested that we rename one of --allocated and --allocated_type to make command completion easier

TODO: how record allocation of pool laptops?

Setting managed type

The management type of each system is recorded in the "managed_type" field. A system can be one of mdp | dice | selfdynamic | selfstatic | managed | other.

TODO - consider shortening selfdynamic to dynamic and selfstatic to static

eg ii set --hostname squirrel --managed dice

item->managed_type = 'dice'
create logbook entry

TODO - add in other transitions (eg DICE -> selfmanaged)

Name or rename a system

eg ii set --serial CZC234987HJ3 --newhostname harris

item->hostname = 'harris'
create logbook entry

eg ii set --hostname harris --newhostname benbecula

Manually set location

Also - revert to auto location?

(CSOs manually set to 2.09 when putting for temporary storage. Then rely on machines auto correcting their location when commissioned. Need easy way to cancel manual set location).

(Auto location will not work for wireless kit as we have no access to WAP information - TODO: Ask IS whether it would be possible to have an API that returned last known location + UUN for a given MAC address)

Mobile kit with no wired connection will have location 'unknown' - we will rely on 'allocation' to track.

Adding parts to a system

Items can be associated with a system. This is particularly useful for grouping together the component parts of network routers, storage arrays etc.

eg the following associates the item with serial SV02810080 with the system with serial 13K046T

ii additem --serial 13K046T --extra_serial SV02810080

Note that only non system items can be associated with a system.

(Note that --serial is available for many 'ii' commands to select a system to lookup or modify)

TODO - Removing a part from a system

Kit is junked

TODO - flesh out

Delete any profile and DNS now.

A item is marked as being junk by setting the allocation_type to 'junk'

eg ii set --hostname benbecula --allocatetype junk --setlocation IF-B.03

item->allocated_type = 'junk'
item->allocated = NULL (via trigger? )
item->managed_type = NULL  (via trigger?) 
item->hostname = NULL (via trigger?)   ?? - we may want to keep hostname until disposed of ??
create logbook entry

Insist that location is manually specified when setting to junk.

'junk' status is sticky - can't modify anything other than location and can't un-junk (and hence can't bring back into service)

(Perhaps have a "ii junk" command?)

Kit is disposed of

Delete any profile and DNS now. (could also be done at junk stage, but doing now is a final catchall)

TODO - flesh out

Aliases

Sometimes, a reseller will provide a serial number in addition to the serial number provided by the manufacturer. Unfortunately some resellers expect you to refer to their serial number when making support calls. You can add an alias to any item to record this additional serial number. An item can have any number of aliases.

TODO - Are aliases unique (possibly not?). How add, how list, how delete? What operations accept alias (propose only query)?

Querying

  • by serial
  • by hostname
  • by alias
  • by allocated
  • by allocated_type
  • by location
  • by make,model ?
  • by managed_type
  • by other constraints?

Would be good to make restrictions additive with regexp matching - eg ii query --location 'at\*' --managed_type='selfstatic'

Note - don't plan to create a command line tool that is capable of complicated queries. A web interface and/or sql interface and/or API interface would be simpler (leveraging existing opensource tools).

TODO: consider whether case is ever significant.

Specifying kit to the 'ii' command

By hostname (--hostname) , by serial (--serial), by alias (--alias ?) or by item table row id (--iid)

May be machines with same serial number, so as a last resort one can specify the exact item table row using --id {no}.

Periodic maintenance

  • Deal with orphaned rows?

TODO

Various automatic processes

Location info from switches (PULL TO)

A script, switchtoinv, will be run once per hour to update the location of kit for which the macaddr is recorded.This should include all network connected kit, not just desktops/laptops. This script will download the MAChistory file from each site netmon server, merge the results and select the most recently reported location for each macaddr. If this is the first time the kit has been seen by the switches, or the kit has moved to a new location, the new location is recorded (item->space) and the time the kit was seen by the relevant switch is recorded in item->last_seen. A logbook entry is created to record the new location. If the kit hasn't changed location, the time the kit was last seen is recorded in item->last_seen.

As explained above, this will not cover kit which is only ever connected by wireless. (Could check with IS whether they could expose WAP info)

Client reports (LCFG only) (PUSH TO)

LCFG clients periodically (twice a day) report their hostname, make, model, serialno, memory, kernel, monitor model, monitor serialno, macaddr, operating system and a few status flags (eg whether a VM or not). They currently do this by populating the "report" table directly, keyed on the hostname. A script "syncreports" periodically runs on the inventory server which processes the reports in the "report" table and updates information in the item table. This two step operation improves security as the clients don't need to have write access to the item table. Need to think about what technology the clients used to populate their row - currently using DBI::PG, might be better with an API - in any case it needs better authentication than currently used (shared password).

The syncreports script works through the reports matching on the item table by serial number (TODO: what if there are two machines with same serial number, but different manufacturer?). The item->hostname, item->make, item->model,, item->managed_type fields are updated for the matching serial number, but only if managed_type is NULL. The item->os field is always updated. (If there is more than one item table row for the reported serial number, the script throws a warning and doesn't continue processing that report row.) NB: might not want to pull in hostname from the report, though worth comparing and throw a warning if not same.

We have to be careful that the script which syncs the 'clientreport' table with the 'item' table doesn't overwrite manually set information with data from an old report. For example, a DICE managed machine called 'skye' becomes a self-managed machine called 'eigg'. When converting to self-managed, the item->hostname field will be manually set to 'eigg' - if we're not careful, the next time the clientreport sync script is run, it'll overwrite the item->hostname with whatever is in the last report script. A number of solutions :-

  • delete clientreport entries when item->type transitions away from 'dice' - probably unsafe, but good housekeeping?
  • don't sync clientreport entries for items where item->type <> 'dice'
  • add an item->lastreportsync column and only update fields if the clientreport is newer than this date

In the old inventory, it wasn't essential to set any inventory fields manually - the inventory would populate things like 'hostname' from clientreports. However, given that we're expecting to master DHCP information (at least) in the new inventory, it won't be possible to trigger this from a clientreport. Therefore, we won't need to sync item->hostname from clientreport => and therefore we can link using hostname and serial (which overcomes the two devices with same serial number problem).

Might be a good idea to only sync reports that are less than a day old?

Feed into LCFG profiles (PULL FROM)

For each machine with managed_type = 'dice', create a header with the resources. TODO: Shouldn't something similar happen for 'selfstatic' machines? (We should probably do this for any piece of kit which has a macaddress with the generate_dhcp flag set.)

TODO - Expand on the following - which fields come from where

!dhclient.mac         mSET(xx.xx.xx.xx.xx.xx)    # to associated macaddr row with dhcp_generated=true
!sysinfo.node        mSET(gala)
!sysinfo.domain      mSET(inf.ed.ac.uk)
!sysinfo.sno         mSET(CZC2457YM0)
!sysinfo.location    mSET(IF-5.14)
!sysinfo.manager     mSETQ("")
!sysinfo.model       mSETQ("HP Compaq Elite 8300 CMT")
!sysinfo.owner       mSETQ("")
!sysinfo.allocated   mSET(ascobie)
!sysinfo.comment     mSETQ("")

In the old 2008 system, these headers were pulled down periodically (every 30m) from the inventory to the LCFG master. Now that the macaddr for a desktop machine comes via this route (mac -> header -> profile), we need to make sure that the info is available pretty soon after it's available in the inventory. Otherwise people have to wait for the periodical pull down before installing a new machine. We could just increase the frequency of the rmirror, but that's perhaps rather crude. Increasing the frequency to every 5m would create negligible load on either end (only takes 1s to run) and is probably quick enough for user requirements. Alternatively we could push the information from the inventory, but how does it know to do that - the trigger may just be someone setting managed_type to 'dice' for a desktop.

Select PC load (PULL TO)

The Select PC suppliers produce an excel spreadsheet (every Monday) listing details on kit that has been purchased by the University. These spreadsheets are stored on a share on an IS server, along with historical copies of the spreadsheets. The share is EASE authenticated and does require a suitably authorised EASE account to access.

In the prototype, the scripts gatherreport, run manually, pulls down these reports and combines them, using the script parsereport to extract the required information out of the excel spreadsheets. This produces a csv file listing all SelectPC equipment purchased - with a macaddr, serial-no, model-name, purchase order for each item. This file is then loaded into the 'supplierreport' table. A script 'loadmacreports' works its way through the 'supplierreport' table. If a piece of kit was purchased by the school (a row in the 'item' table with same serial number and purchase order) an entry in the macaddr table is created and associated with that 'item' table row.

The delivered version will need to be run automatically. IS has told us that it ought to be possible to access the share using a functional account, so this should be achievable.

\\df1.is.ed.ac.uk\UnManaged\SelectPC\Dell MacAddress \NewformDell.xls

Theon conduits - people, space, site (PULL TO?)

TODO Need to discuss conduits with Tim or Graham.

API

The prototype 'ii' command currently accesses the Inventory tables directly (albeit via DBIx::Class). There are two problems with this :-

  • it is in theory possible for a remote client to access the inventory tables directly and work round the rules that the 'ii' commands would normally impose. Some of this could be solved by using triggers in the database, though not sure if all could be done that way.
  • it is not easy for other systems/scripts to query the inventory as they need knowledge of the schema

It would be better if a remote API were presented (particularly for queries). An API for updates would also be useful so that we can maintain consistent behaviour across multiple edit interfaces (commandline,web,..)

Misc things

  • re schema - might be better to use tables to record possible values for 'type' fields, rather than having Enums which would necessitate schema changes for additional types.
  • MAC addresses - will be displayed in lowercase with colon separators, but accepted in lowercase/uppercase with colon or dash separators. Stored using the postgresql MACADDR type

Virtual machines

  • plan was to NOT add virtual machines to the inventory, but just leave them in the clientreport table to query
    • but this makes it difficult to make a query like - give me all servers
    • problematic if we're mastering DHCP/LCFG info from inventory
  • 10/05/16 - probably better to add virtual machines to the inventory, with an 'item' row. purchaseorder_id = NULL could represent 'this is a vm', or we could add a 'virtual' boolean column. The row could be created using 'ii', or by 'kvmtool' or triggered by a new clientreport entry.

Various Reports

  • working thrrough SelectPC reports - identify serial numbers which are missing/incorrect in our orders files (can match on order number)

Thoughts - 27/04/15

  • Should managed type be set to 'unknown' by default?
  • ? Split out DHCP/non-DHCP from managed type - eg so have type ='self' not 'selfstatic' and 'selfdynamic'. Makes it easier to add support for eg managed dynamic and mdp dynamic
  • What do about faulty kit where the serial number of the replacement item is different from the original item ?
    • ? Change the serial number in the original order and record the old serial number in the item's logbook (assuming that any item can have a logbook!). This will create an orphaned item with all the information that we might want to retain, like allocation, location etc - so we should probably create a "item replaced by" tool that does all the work behind the scenes - though that's not easy to achieve for the original order file
  • modify clientreport to report on various attached hardware (eg graphics cards, disks etc) - could then automatically link these to the machine they're part of. - can then easily, for example, list all DICE machines with particular nvidia graphics cards. Support multiple monitors
  • Should have a 'ticket' field in orders database, and orders table, to record the associated RT ticket (Now added)
  • with clientreport - what happens if the serial number of the machine can't be found in the inventory? It's probably a duff serial number?

-- AlastairScobie - 15 Nov 2013

Topic revision: r35 - 05 Jul 2019 - 15:51:53 - AlastairScobie
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies