Inventory Database: Requirements Document

This document incorporates notes from ST 62833 (inventory database on cscf in php) and ST 47633 (wishlist, closed) as well as discussions between DanielAllen and other CSCF stakeholders.

IsaacMorland has added some InventoryRedesignRequirementsDataModelNotes.

Overall design

• web interface written in PHP following code-base of Math's re-designed inventory system
• minimal tab-clicking to find relevant information
• rapid prototyping / re-development based on user requests

Maintain integration

To facilitate reporting information about Maintain (and allow editing), add a "DNS" tab which reports the current information in Maintain and any discrepancies or errors.

This tab is not meant to replace Maintain's interface entirely, because not all information can be added/changed from .csv input.

• no capability to add/edit MX/Cname info from .csv
• There should be a link from our interface to the host within maintain for editing "advanced" info.
• Later: integrate batch-editing inventory with maintain- will need to handle a variety of corner cases.

Overall logic for inventory/maintain integration:

DNS Work Flow

• user creates or edits an inventory item.
• choose IP for user? additional button next to IP field: "Choose an IP in this range".
  • on selection, it will query maintain's database and choose the next available address.
• if the item's data includes IP & hostname: auto-check those plus MAC in maintain.
  • select name, ip, ha from host where name = "scsmac21.cs" or ip = inet_aton("129.97.15.167") or ha = "001ec213b54d"
    • if count = 0, doing an add.
      • if add: return user to inventory page, open DNS tab, default values.
      • these values have already been entered in maintain.
    • if count = 1, doing an update.
      • if update: DNS tab should highlight any differences from previous values, and offer keep/change for each.
    • if count > 1, we may have a problem.
      • [It is possible for a hostname to be associated with multiple IP addresses (or the inverse) in Maintain, and only hostmaster (IST staff) can update these records.]
      • error out (opening the DNS tab, showing a list of the matching entries, with highlights. each with a link to the appropriate entry in Maintain.
        • --> one or more will need to be changed manually in Maintain, because the alternative is replicating much Maintain functionality within inventory, which I want to avoid.

DNS Functional Requirements

• submitting CSV to Maintain:
  • Authenticate as generic CS inventory user
  • CSCF workflow for generating new hosts in Maintain: HostNameConventions
    • CSV creation in Maintain:
      • two required fields: hostname, IP and one semi-required: hardware address (if supplied, must be unique in db)
      • many sensible defaults on optional fields: zone (user's default zone), DHCP workgroup (default for zone), TTL (86400 / 1 day)
      • defaults to blank: Operating System, Hardware Type, Location, Serial Number, TXT Data, Owning Unit, Contact Userid, Admin Userid, Purchase Date
    • if they've specified a hostname but the IP address is already assigned, could we automatically switch to creating a cname?
      • Not using csv-import or edit- would need to be manual.
      • that's an error for the user to resolve manually.
    • Data that doesn't come from inventory system directly: TTL, workgroup (conveniently, both have good defaults).
    • Data that is recommended from HostNameConventions:
      • Operating System: see about validation from /software/accounts-master/data/equipment/.Config/charges
      • TXT Data: USER: from "Authoritative User"
      • TXT Data: ORGUNIT: fill in Math,CS if not in CS-owned Vlans?
      • Owning Unit: blank? or based on sponsorship code?
      • contact ID: can be blank, I think?
      • admin ID: fairly lenghtly policy. default to admin for subnet?
      • comment: "changed by $user via inventory."
        • include other fields that changed in this batch? and/or inventory comment field? (probably not, if it's meant to act as a RCS-type message)
      • hardware type: "Vendor" / "Model"
  • Possibly making IP address choices for the user:
    • choosing an IP range or address: could we intelligently pick, if user puts in partial address? (eg., "129.97.84" vs "129.97.84.23") Doesn't work for "top of range" or "bottom of range".
      • "select (max(ip)+1) from host where (magic)" # (magic = identify the ip-range in question: maybe something like $range = ip_to_int("129.97.84.1"); magic = ((ip > $range) and (ip < $range + 255))

Appendices: Notes from users

Notes from Paul Thompson on the old E+I interface

As recorded by Daniel. Italics were Paul's written responses clarifying Daniel's notes.

Bugs

• inventory has a broken field "attached to barcode" - which never worked properly. new version: doesn't have the entry-field
• if you save a new item in inventory and click the barcode in the resulting page, crashes session (null pointer?). need to go back and search for the barcode. new version:
• there's a checkbox for is the item active. The search field doesn't obey. (always includes inactive items in search results).

Feature Changes

• emulate Maintain's autocomplete features. (similar to firefox field-autocomplete).
  • Not so much similar to firefoxs, but more so the drop down menus Maintain uses.
  • vendor, RM#, purchase order, account flex-field (AFF) (also see below for hardware tab)
• batch creation, editing, deleting. new version: mass-add/edit
  • asset disposal process currently involves: manually entering each item, changing room#.
    • given a barcode, want to set: PO, asset, descr, model, serial#.
      • Correct - but we want to extract the data from those fields / barcode supplied and exported into a say a tab de-limited file.
  • batch create: only difference between machines is barcode/serial#. 10 machines shouldn't require reentering same info on 10 forms.
    • The process I'll describe below, as changes and deletion can be handled in a similar way.
  • batch edit: to change room # for a batch.
  • batch delete: do we need inactive data from 5+ years ago? (surplused, ancient).
    • What I'd like is a process in whcih the user is asked: do you wish to create, edit or delete records? User makes their selection; they are asked (depending on the choice) how many records will be created (and the range of the barcode numbers), changed (barcodes or range thereof), or deleted (barcodes or range thereof). There should be some very large labelling for deletion as well as a warning about how dangerous this can be and should only be sure that what they are doing is correct..
    • The next step should be which fields will use the same info and which will have unique information (in the case of creation - for editing and deletion, the stage should only be which fields are changing) - at which point the repeating info should be entered. The unique info should be entered afterwards (where its done at the same step as the repeating info is up to you). Finally the user should be allowed to select the apply button and see feedback on the screen when each record has been created.
• global search in many fields (such as "crt"). new version:

• 'choose a room to enter': change the title to something less confusing. new version:
  • brief view of room's contents should include: barcode, equipment type, description, last found.
    • edit button, and a checkmark connected to an "update Date Last Found" field for quick batch-changes.

• 3 fields were needed when we were with math; no longer needed. they auto-fill with a defalt null value
  • When I select 'Choose a Room to enter' on the main menu there are three fields used to force default values into the Sponsorship, Equipment, and support classes. When sweeping a room, these should no longer be required as the error checking in creating a barcode forces a user to enter a valid label. new version: doesn't require changing from the default.
    • Jennifer and you may need to sit down and discuss a list that can be used, whether its using codes or 'plain english' labels that cross reference the codes.

• changing hostname in inventory: maybe pre-populate IP and mac from maintain?
  • changing subnet: one button? (ask him about the importance of this) (*)
    • I wanted a Change button, in case the IP subnet changes for a PC, or laptop on the wired network. e.g. - a grad student PC is moved from one room to another and has been re-purposed, thus is placed on a new subnet, but the hostname stays. It'd be quick to change the IP. I was thinking of only changing the subnet portion, but there may be times in which that would not be sufficient. Also, while on the topic of autocomplete, will you still produce a branching structure for hostnames, virtual hostnames, etc, per MAC address?

• on Paul's "inventory sweep":
  • red "NN" isn't preserved after logout. If you declare an item in a room, it should stay seen for... a term? new version: not used
  • viewing a room's list of equipment:
    • add a "not here" box so we can flag an item not-found and keep that information.
    • show "comments" or "purpose" when you mouse over the equipment name.
      • The mouse over should be there for global search results too.
    • show user field
    • remove "unit" field: not necessary (post-split).
    • show equipment type (printer, etc)

• field for desk/workstation
  • or append to the room#: format something like "DC2555f-1" or "-a"
  • separate or same field for rack#. (alpha for rack/numeric for desk?)
• for grad /teaching lab systems, a list of users who have authorized access to login to that particular system updated from accounts/ AD / etc. perhaps if a user is added to list it reverse updates?

• hardware fields and description field overlaps data.
  • Paul's description includes HD size. hardware fields don't include.

• comment field: currently has some info that could go in separate fields. (ST, off-campus, multiple MACs, personal equipment)
  • ST field:
    • wants inventory to to see RTs related to the barcode [in ST you could add 'inventory' to the list of email recipients; and the email would be parsed for ST# ?]
      • There is a user 'cs-inventory' if I recall, but there is a 'eqiup barcode' field. It's not often used.
    • wants ST to have link to the inventory db for that item; if possible, automatically provide the room-number and subscription codes within ST.
      • and vice versa.
  • personal equipment: paul notes in description
  • "away"/ "off-campus" flag for if the item is away from the university.
    • field with approx. return date?
      • run a report for "what equipment is due back in 7 days?" on a cron-job, email to jennifer and cs-inventory.
      • possibly email reminder to the user, if we had their address.
  • comments field should be variable # of lines; much info scrolls off bottom if you add lots of CR's in the comment. (paul uses a compact format with ';' to get around that)
• customize the contents of dropdown tables for qualified users (aux_equipment_type, aux_sponsors, and so on:)
  • equipement-type (such as: part of a cluster)
  • sponsorship listings (to clear english descriptions rather than shorthand code).
  • incorporate Research subscription codes into the Sponsorship code? i.e. "a+b=c" if a (subscription code a.k.a. prof, group, or project) + b (desktop/dedicated/other) = c (what Research group charges)

• report for host/ip discrepency with Maintain -> daily email.
  • integrate with maintain will prevent most of these. (but all?)
  • push our serial # to maintain's, as an additional check?

• Dan H prefers changing data in Maintain; what happens if we're emphasizing inventory's interface?
  • CSCF should then have a meeting in the future to ensure that everyone moves over to Inventory - if there are features Dan must use, perhaps he can then add on later?

• generate new hostnames in E+I (eg., for personal equipment?)

• freshness date hosts. (also eg., personal equipment; if it's not seen for a term, perhaps it can be deleted)
  • report stale hostnames. (fill from Ona timestamp- when was it last seen really?)
    • Perhaps an initial ping check recorded for all hosts would be a good starting point. Then use ONA to update.
  • paul does a lot of manual hostname cleanup.
  • each term: old equipment not seen in x years -> send alert to maintainer email
  • note: but copying data from ona: too much redundancy??

• accounting flexfield is too inflexible. eg; can't have more than two accounts. new version: any number of accounts
  • fix could be: accept any number of field digits... sometimes, three or possibly 4 accounts for an entry.

• mandatory data includes hostname. can't accept cname.
  • should accept multiple hostnames for a given IP.
  • maybe: list true name and aliases.

• should accept multiple mac addresses for same record. ( worrying about multiple hostnames for a mac address: would we want some sort of data tree / text field with MAC / hostname / IP address...)

• record student's ID who is using the machine (populate other uwdir info?)
• grad DB (Debbie M's excel spreadsheet) has a pile of information that should be integrated.
  • Debbie's replacement might be more sympathetic to using a webform instead of editing an xls spreadsheet.
  • we could populate student-info for inventory, from that spreadsheet.

• "authoritative user" field is vague. it's related to subscription code.

• it would be nice to shorten the sponsor-code list, because we don't need to specify exactly which TA is sponsor. could just put that in a "real user" field.
  • or "acquiring person" for equipment purchased under projects (such as CFI). CFI could be sponsor Code, but if a prof acquired the equipment via another prof's account this should be noted.
• main page: should explain that you get a popup window with explanations of each field by clicking on the field.

• undo after save?

• "support" tab: "group field"- is that ever used? why does he need to fill it in?
  • Support tab is from the old MFCF days. I doubt any records are using it now.

• "hardware" tab: SNMP- is it updated?
  • auto-complete fields for DNS/IP/Contact/OS (as per above)
  • can we use SNMP data to grab equipment info? (eg., sun machines...)
  • authoritative user: add similar field for "real user"
  • last ping date: is it populated automatically? why not? get from ONA instead?
    • Jason informed me that ONA is not always reliable for last ping date. :/
  • would be useful to be able to link from record to an ONA lookup for that mac address.
  • also would be useful to double-check a barcoded device is at its proper host/IP/MAC.
• main tab:
  • for printers, add field for queue name, that's often something we're looking for.
  • what is "quota file" used for? remove. new version:
    • or "quota name" in case the printer is linked to a preexisting quota?

• attached-to-barcode field: should these be reciprocal? ie., pc has special monitor attached, reflected in the PC record, the monitor's record should have the PC barcode automatically.
• terminals: automated (per term?) check of what each networked and online unit has for firmware rev. and configuration and fill its particular record based on hostname (assuming thats accurately listed)?
• More reporting by the database in terms of sponsorship data aka someone selects 'unknown' and accounts@cs is notified once a night to update the record
• More methods in which to search (not equal to, does not include, etc)

Notes from DanHergott

• would rather pull information from maintain, if possible. He never learned inventory because it was clunky.

Notes from DaveGawley

• ona can link associations between MAC address and hardware port. dan can/should have a mapping of port to physical location. that way we could use ona to look up the MAC, find the port, and auto-fill the location last seen.
• Inventory allows multiple hostnames, IPs, and MAC addresses. Can we see this data in table format?
• Can we offer data useful for automatically constructing a table of which services are provided in each xhier-region by which hosts?
  • Add database information about "groups"/"regions" for a host.
  • Add database information about high-level services provided by a host (web/mail/database...)

From IsaacMorland on the new .php inventory interface (as are following comments)

I'd like this to use CAS. From the E+I point of view that just means that rather than handling its own logins it should use $REMOTE_USER to determine the userid of the current user. You can get it working on www.cs using the same authentication setup as other areas of the server, and later when we roll out CAS for general use all we have to do is adjust the web server configuration.

From LawrenceFolland

• inventory -> maintain will be useful.
• should be able to construct a URL to enter Inventory at a certain barcode, room, etc.
• would like to be able to track the software licensed to a particular computer
• noted from a recent issue: machines have different mechanisms for becoming root. Can we record the process for becoming root (NOT private information)?
• information on how to get access to the terminal (via terminal server? via port 'n' on a KVM in the hardware lab?)

From MikePatterson

• A reset button on the short query form would be nice too, that's something I've longed for.
• CAS authorization would be nice.
• require unique mac addresses?
• duplicate an entry: deals with the problem of invalid duplicate elements by reporting "you have an error in these elements" allowing you to fix them.

From StephenNickerson

• The search-results page row colours could use tweaking.

From BillInce

• Better data-handling on login so we don't lose the data from a previous session (either via back button, or being forced to reauthenticate).
• Better data-handling will be required if we're going to allow unauthenticated users to follow a link from outside that enters Inventory at a certain barcode, room, etc.

From DawnKeenan

• It would be nice to have a documented way to indicate a machine has been taken home by someone.
• visual indicator of search-results sort field
• "meaningful" page title/header ("where am I?")
• greenbar on "enter a room" search-results
• batch-CSV download (from search-results page?)
• date-based queries should have before and after

From JenniferKeir

• E+I used to validate operating system names; inventory@ receives emails when invalid operating sytsems are listed. We should validate these. The list comes from cscf.cs:/software/accounts-master/data/equipment/.Config/charges
• CVS output for multiple hosts: it would be useful if the search-results page could link to "download CSV for these hosts."

-- DanielAllen - 14 May 2008