Inventory Database: Requirements Document Data Model Notes

DRAFT

This is structured to correspond to the InventoryRedesignRequirements document to which it relates.

Bugs

• "Attached to barcode" needs to work properly, and may even need to be multi-level. I can think of several uses for it:
  • Network card is "attached to" (actually installed in) PC (if network card inventoried separately)
  • Monitor or external drive "attached to" PC (if permanently; detachable external drive is different)
  • Rack-mount equipment "attached to" rack? (Or is a rack a "location" within a room?)

Feature Changes

• batch creation, editing, deleting.
  • asset disposal process currently involves: manually entering each item, changing room#.
    • given a barcode, want to set: PO, asset, descr, model, serial#.
      • Can we auto-generate disposal forms? i.e. just click "dispose of this item" and it makes the form?
  • batch delete: I'm pretty sure the only reason to delete stuff is because it is ancient information that is no longer useful. This should be done by an automatic process with no manual intervention. This completely avoids any need to consider what warnings, etc. are necessary to avoid accidental deletions.

• 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?
  • viewing a room's list of equipment:
    • remove "unit" field: not necessary (post-split).
      • I wouldn't be so anxious to remove it. What if in the future we are responsible for equipment owned by distinct entities? On the other hand if it's easier to cut it out then it is true that we don't need it at this moment. Or just remove from display.

• field for desk/workstation
  • or append to the room#: format something like "DC2555f-1" or "-a"
    • No, need a location_code field, and the room/location combination is a foreign key into a table with one row per location. Actually, room should be building/room.
  • separate or same field for rack#. (alpha for rack/numeric for desk?)
    • Rack should either be a location or each piece of equipment therein should be "attached to" the rack. Do we currently inventory the racks themselves?
• 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)
  • Move it all to separate fields or tables. Ideally, have no comment field, unless people truly have a need to make miscellaneous one-off comments. On the other hand, don't eliminate the comment field unless we're sure miscellaneous comments are not needed.
  • 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.
  • fix could be: accept any number of field digits... sometimes, three or possibly 4 accounts for an entry.
    • No, another table is needed here. Actually, we should probably have a table of just flexfields that essentially allows assigning aliases so we don't have to deal with 31-digit codes all the time. This would be used by all applications. Then inventory needs a table implementing a many-to-many relation to the flexfield table.

• 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...)
  • Need a "MAC address" table. Each entry knows to which inventory item it belongs (usually a PC, but if a network card were to be inventoried separately it would be on the network card).

• record student's ID who is using the machine (populate other uwdir info?)
  • Don't populate, just link in through a view/query when needed.
• grad DB (Debbie M's excel spreadsheet) has a pile of information that should be integrated.
  • This is the real reason why inventory should move to Postgres. The spreadsheet needs to become part of the graduate student management system, which must not be split between multiple databases.
  • 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.

• "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.
    • 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.
  • No, this is what indexes are for. The monitor is "attached to" the PC. A PC would never be "attached to" anything, unless possibly it was part of a cluster or rack.
• 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 and Dave Gawley

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

Dave:

• 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.

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