MDB:Development

Structure

Organizations

Units

Main Org unit (can have parental or child units). OUCs, DNS, etc associated with the unit, not the groupings.

• Define associated DNS domains, OUCs, Remedy queues, etc for each Unit.

Groups

item groups within a unit; completely for organization. Might allow it to have access privileges associated with it, but these would be limited to viewing item data, perhaps editing. No access to "codes".

• Should allow Groups to become Sub-Units. And vice versa.

Tables

• Type, Make, Model, Image -- these can all be fields in one table... automatically sets up a hierarchy, constrains duplication, and allows a single image to be associated with all items of the same make/model.
• /Devices
• /Printer Information

Users

Users need to fill out information about themselves. Titles can be pulled from LDAP, along with most contact information. But skillsets, knowledge, responsibilities, etc -- everything needed to fill in the IT Directory should be captured.

• be sure to send a ping/nag every semester to have them confirm that their information is correct and up-to-date.
• need a cron to remove access for disabled/removed UnityIDs.
• we need to make the profiles interesting -- ACHIEVEMENT AWARDS! -- based on Remedy, QIP, Surplus data... "1000 items surplused", "100, 200 calls answered" etc, but with cool themes: 500 items surplused -- "Trash Collector". Fun it fun and pretty/cute; think video games/City of Heroes.

Source Data

Sybase

We need to pull information from CAMS, Surplus, Facilities, and start autopopulating a load of the data in the system. Go ahead and embrace this as a tool for both the SysAdmins and the CAMS coordinators in each dept.

• Split the active and inactive (retired, surplused) into TWO tables. Should help w/searches, duplicate entries, etc. When retiring/scraping item, it gets physically moved to the inactive table.
• QIP -- cmdline options. Anyway to use QIPREPORT to dump all of the machines (using guest?). Then using a cronjob, import all of the info into a table. Removes the need for the nslookups in IP Table reports, and speeds up the DNS/IP updates to the machines, and would allow reports -- ie, which machines are using the STARS.NCSU.EDU domain.
• Reconciliation reports -- looks at CAMS tags and compare against CAMS DB. Looks at serial numbers / CAMS tags and looks for references in the Surplus DB. Use to keep CAMS or MDB up to date.
• Update Building lists to pull automatically from Facilities building codes -- also then allows you to run reports against CAMS to tell the CAMS coordinators when they need to update info in CAMS (equipment locations).
• Pull org unit lists -- 140401, OUCs, etc -- use to build the official group lists. Users of MDB then either select which OUCs they belong with? Or we setup these as the primary groups, and they can create associated subgroups. Allows us to yank all CAMS info associated with each group into the system by default. Also able to associate all surplus reports against the reported OUC.
• Auto populate all CAMS info into the system. Don't classify the items by default. Have a note at the top when there are unclassified items in the DB. Way to recognize when a new item has been pulled from CAMS automatically. Users then go classify the item, and append useful information.

Facilities

Use floorplans for each of the buildings? (a la http://www.netsimplicity.com/vam/)

QIP

Need to populate information regarding the machines in QIP. Depending on how threaded we can make this, and the time cost, possibly running after each DNS update M-F (8,11,14,17).

qip-getsubnetlst -t network -a 152.1.0.0 -u xxx -p xxxx
qip-getsubnetlst -t network -a 152.7.0.0 -u xxx -p xxxx
qip-getsubnetlst -t network -a 152.14.0.0 -u xxx -p xxxx

Followed by multiple threaded versions of:
qip-report -u xxx -p xxxx -r network -a 152.14.1.0 -f ~/qip-152-14.txt

Uses the "active" networks from the subnet lists. 

Call "QIP" not IP Ranges.

• List of machines registered in QIP but not in MDB and vice-versa. Simple report page.
• Graphical display -- each subnet (one per page) displayed as you would see a defrag report. Color coded appropriately.
• Unit identifies networks (and the app will need to understand the subnet mask provided; quarters vs supernets) it uses in the new version of Codes (Settings?).

Active Directory

Needs to be a source for Windows machines hardware and applications.

WolfTech Collection Agent

Uses WMI to get hardware info. Uses Microsoft Application Analyzer to get complete list of installed applications. Also collects information regarding who's in the local accounts/groups on the machines.

WSUS

Need to pull relevent WSUS info per machine as well.

RHN?

Is it possible to pull the hardware/OS/apps from RHN?

[09:20] <jjneely> Dan wants to suck the hardware information out of RHN for his inventory system
[09:21] <jjneely> djgreen: I'd suggest looking at the XMLRPC APIs for RHN documented (heh) at https://rhn.linux.ncsu.edu/rpc/api/ but I don't believe it exposes the information your looking for
[09:21] <jacorley> heh
[09:22] <jacorley> I'm fairly positive it doesn't expose anything useful
[09:23] <jjneely> All of RHN is backed in Oracle 9iR2 database...to which I still don't have access to the Oracle patch site (although I would probably have better luck getting it now) and no one in ITD is a real DBA which leads to some scariness
[09:23] <jacorley> having a real oracle dba is almost a requirement for anything oracle related
[09:24] <jacorley> jjneely: carlos does a lot of work with oracle, he might be able to lend a hand if need be
[09:24] <jjneely> Also, if we are looking at replacing RHN while I may be able to collect the hardware information everything will be different
[09:25] <jjneely> what I'm hoping the replace RHN idea will buy me is that I can run one instance of the sat with the embedded oracle db rather than external and not have mission criticle data in it.  The new magic would most likely use an ITD mysql database that's actually redundant ;-)
[09:26] <jacorley> redundancy? that's just crazy talk
[09:26] <jjneely> djgreen:  what you think?

Since this conversation, Jack has released some previews of a new RHN system he's working on... need to talk to him about exports...

Macintoshes

Need scripts to yank HW/SW data from Mac OSX... many people have mentioned the opensource application SpiceWorks as having a way to do this. Need to investigate if the scripts we need may already be written. If nothing else, should look at the interface as an example of what we might do.

SWTracker

We need to do something to address SWTracker... a similar DB structure (though it could use an update), but far better interfaces. Perhaps a completely new DB/interface, but someway to push data to ITECS? Or perhaps simply allow them to sych/download data -- if a better product, this might only need to be a temp solution.

Oracle

Potential to include access to financial data via WolfReports information? Ease admins ability to reference/synchronize the items in their inventory with specific PO#s used to buy them. Might be limited to recent purchases? Investigate options.

Tabs/Divisions

Use the School of Education (http://www.education.cornell.edu) tabbed template as the primary design. Or perhaps the CATS software package -- much better layout. + Might be able to use the Client/Contacts tabs builtin.

• My Surplus -- have a pulldown of their OUCs, which they can select and get a list of their dispositions (most recent first), then select each to see a full list of items. Allow a search (desc, cams, serial), but only within their OUCs.
• QIP -- see above.
• Search
• My Codes
• Active/Inactive Machines?
• My Software / My Applications
  • Filter that restricts data to machines that have been updated in the last two weeks/month.
  • List of machines that haven't updated in XX days/weeks.
  • Display all apps, or display all apps/version
• SWTracker -- we need to get the table structure from SWTracker; can use it as well. Might as well tie in the SW info.
• ...
• User accounts -- lets get more basic info on the users -- use LDAP to give contact info. Starts IT Directory.
• Units should have contact info, plus it has associated OUCs, DNS, etc.

Reports

• all invalid user accounts
• machines w/o contact information
• machines in QIP not in MDB
• missing information -- ie, items needing speed/memory.
• machines running service X
• Greg R in CNR uses the saved searches, so need to keep this functionality.
• SW error messages
• computers w/o reboot in X days
• hardware changes?
• computers that haven't reported in X days
• BIOS overview (list by make/model) -- can we get most recent bios#s from dell website?

• Get error messages in their logs.
• HD usage overview

Requests

• barcodes/scanner
• pictures
• ability to transfer items from one group to another -- sent/accept setup + autogen paperwork?
• homeuse form tracker
• A parts field. Something we can use to list commonly purchased items for machines, like toner for printers.
• multi-edits at once. should be able to standardize this based on the fields updating.
• change Primary User to Primary Contact
• Personalize the display options (and remember)
• Printer friendly page
• expand the IP request form to something all depts can use.
• inventory option -- similar to CAMS' system
  • need to have this go into effect each year 3 weeks before they start the CAMS Inventory
  • need to use the identified "responsible" faculty to provide each with a list of items to confirm locations of
  • CAMS coordinators will then have an overall report that details what has/hasn't been inventories, changes in locations, and who/date of confirmations.
  • the key is to place the work on the faculty, spreading the burden -- remove the need to have the single CAMS coordinator walking the hallways to find 500 items...
• autofillout of Surplus forms (Surplus information could be personalized by user) + the "HD erased" paperwork.
• bulk import
• bulk add (as in, we just bought 20 of the same model..)
• Xchange service -- people getting rid of good computers; "pre-Surplus" offer to other units...
• warrenties don't always end on the same day as purchase dates (EOL warrenties for Dell, for example). Move from warrenty date = purchase date + Xyears to a straight input date.
• We need to be able to tag certain funding accounts -- some agencies want their equipment back. Once you tag an account, we'll need to have the system refuse to SURPLUS these items... And it should tell you why.