The Database Manager



  • Well, yipeah-ai-yoo for me. I just (last week) got a job. The position was for a "Database Manager". Curious, I called to ask what that meand exactly. The response was something like this.

    "Well sir, our organisation has a database with all of its laptops, computers, servers and hardware in general. We need someone to manage it and clean it up. We estimate the project can be done in about 6 weeks."

    Interested, I went for an interview. Turns out they used an web-based application I'll call "MAXbureau" and they didn't only use it to manager hardware, but pretty much anything that is managable (employees, support contracts). The problem was that until a year ago users had to enter had to enter their hardware information themselfs. Since the organisation has about 15000 employees this resulted in about 15000 different ways the information was formatted on the 20000 computers.

    It ranged from pretty innocent things like a brand "Compaq" and type "Compaq D510 SFF" to pretty inconvenient things like missing MAC-adresses or serial numbers. Or the, in my opinion avoidable, error of entering the MAC with stripes (-) instead of double points (:).

    The most sirious thing however, is the Divison and Directory ID (DDD). Due to reorganisations and the users there are about 700 divisions in the system while there really are 23. Imagine things like "Child0_", "Child1_", "_Child", "CHILD", "Childeren", "CHILDEREN" and the mysterious "300CH" for the "Child" department.

    After creating a list of things that had to change, I asked for database access and the SQL structure, so I could run queries to replace those things. The answer was plain and simple: "You can't get it if you aren't here for at least 3 months. Besides it wouldn't get you anything, we tried that but it messed up the whole system". A little bit out of the field, I asked: "How do you expect me to correct the database then". "You'll have to use MAXbureau".

    After learning MAXbureau and going trough the manual, I returned to the DBA. "I've looked into MAXbureau, but there doesn't seem to be any find and replace function or something in the likes". "Ofcourse, I could've told you that, you'll have to go trough them manually".

    Even more baffled than before, I returned to my desk and tried it out. Opening the editor and closing takes 8,9 seconds. If I edit one field 6,4 second add to that (thanks to the fact that after the editor has loaded you have to wait 3 seconds, because after three seconds a javascript runs that puts the focus on the first field). Two fields take 10,1 seconds, three 14,0 seconds. A mac has to be looked up and is very hard to acces, so one adds a stunning [b]85 seconds[/b].

    After using the software's report function I got this:

    9000 records need one field updated. 4000 two fields and 750 three field. That includes the 12000 MAC's that need to be updated. In case you didn't get your calculator yet: that's about 1.250.000 seconds or 350 hours of clicking, clicking, clicking again and finally clicking.

    Currently I've contacted the manifacturer to request either a method of find&replace (either within the application or on SQL) or either the database-schema, so I can figure out how the fields should and can be updated. You can't make me believe it isn't possible. If a stupid web-based program can do it so can I, certainly if I've got 350 payed-hours to figure it out.

    [b][i]To be continued....[/i][/b]



  • Oh, and "MAXbureau" is a whole epic WTF by itself. If you want to guess what the softwares name really is: its really written like XXXxxxxxx. The XXX is a somewhat-synonim for "MAX" and the xxxxxx part is a somewhat-synonim for "bureau", but more in the British/Dutch definition of it.



  • Ok, so congratulations and welcome to the real world.

     I can almost guess where this story will be going... You create a tool that does the same automatically in just a bit of the original 350 hrs. You test it and it works like a charm. You show it to your boss. Boss happy. Bigger boss finds out and demands you do it by hand, because it's too risky to use your tool. You almost get kicked for your tempering with their "mission-critical enterprise solution". Ah the joy...

     

    EDIT: Yeah, I sound bitter, I know. But changing an organisation that is used to doing fucking up stuff by hand can be frustrating...



  • TOPdesk!! 

    I too live in the Netherlands and actually went to an interview for a job at TOPdesk, they were just about to create their "own version" of java or so they explained it to me.

    I didn't get called back after the second interview which basicly consisted of 2 developers asking "given the spec X how would you write software for it", I guess I didn't do it enterpricy enough. 



  • TOPdesk?


  • Discourse touched me in a no-no place

    @dtech said:

    The position was for a "Database Manager".

    [...] I asked for database access and the SQL structure, [...]"You can't get it if you aren't here for at least 3 months.

    And you haven't left yet?  Or at least pointed them in the direction of your job description and what "database manager" means (assuming it translates as I hope it would?)

     

    I have a feeling this is going to be a tad gory.... 



  • @bobday said:

    ...they were just about to create their "own version" of java or so they explained it to me.

     

    Holy, f*-ing, s*! If I heard that, I would literally jump up and run out of the interview. Then again, I used to work at the company that created a new language, Neon (object oriented FORTH - seriously), just to write the Mac version of Typing Tutor. So I've heard some horror stories.


  • @bobday said:

    I too live in the Netherlands and actually went to an interview for a job at TOPdesk, they were just about to create their "own version" of java or so they explained it to me.
    What's the big deal? Microsoft did it...



  •  This is simply an issue of miscommunication. They call it "Database Manager" but what they really mean is "trained monkey".



  • @DOA said:

     This is simply an issue of miscommunication. They call it "Database Manager" but what they really mean is "trained monkey".

     

     I've been thinking about replacing me with 3 mail-order Indian employees at 1/4th of my salary. You think anyone will notice?



  • @dtech said:

    (...)
    "Well sir, our organisation has a database with all of its laptops, computers, servers and hardware in general. We need someone to manage it and clean it up. We estimate the project can be done in about 6 weeks."
    (...)
    9000 records need one field updated. 4000 two fields and 750 three field. That includes the 12000 MAC's that need to be updated. In case you didn't get your calculator yet: that's about 1.250.000 seconds or 350 hours of clicking, clicking, clicking again and finally clicking.
    (...)
     

    Well? What are you complaining about? You can do it easily within those 6 weeks, if you work 12 hours per day.


  • [b]Part 2[/b]

    Just got called by their service desk. They said the main problem (the different DDD's) should be solvable. It would require about 20 queries on 8 tables and one day of advice (of which I don't know the price yet.

    *sigh* What happended to "UPDATE hardware SET DDD='CHILD' WHERE DDD='_Child0'"

     [b][i]To be continued...[/i][/b] (and I sure hope it doesn't end with me not posting anymore because my fingers are cramped after 8 hours a day clicking)



  • @dtech said:

    ... I asked for database access and the SQL structure... The answer was plain and simple: "You can't get it if you aren't here for at least 3 months.

     

    I know that lines have to be drawn somewhere, but I just love the (lack of) logic behind rules like that.  "Once a person works for us for 3 months, they magically come to love the company so much that they would never do anything to sabotage our data."



  • @dtech said:

    Part 2

    Just got called by their service desk. They said the main problem (the different DDD's) should be solvable. It would require about 20 queries on 8 tables and one day of advice (of which I don't know the price yet.

    *sigh* What happended to "UPDATE hardware SET DDD='CHILD' WHERE DDD='_Child0'"

     To be continued... (and I sure hope it doesn't end with me not posting anymore because my fingers are cramped after 8 hours a day clicking)

    DROP DATABASE

    COMMIT

    -- Now is the time when you run for the hills!!!!



  • @ Adbove to posts

    There are backups at 1 PM (when everyone is lunching) and at 3 AM, so both actions would nolt lead to anything, expect a few hours of lost work.



  • @dtech said:

    After creating a list of things that had to change, I asked for database access and the SQL structure, so I could run queries to replace those things. The answer was plain and simple: "You can't get it if you aren't here for at least 3 months. Besides it wouldn't get you anything, we tried that but it messed up the whole system". A little bit out of the field, I asked: "How do you expect me to correct the database then". "You'll have to use MAXbureau".

     

    I was imagining myself in this scenario, and right here is where I imagined myself looking for my hat, my coat, and the door. 



  • @Kefer said:

    Ok, so congratulations and welcome to the real world.

     I can almost guess where this story will be going... You create a tool that does the same automatically in just a bit of the original 350 hrs. You test it and it works like a charm. You show it to your boss. Boss happy. Bigger boss finds out and demands you do it by hand, because it's too risky to use your tool. You almost get kicked for your tempering with their "mission-critical enterprise solution". Ah the joy...

     

    EDIT: Yeah, I sound bitter, I know. But changing an organisation that is used to doing fucking up stuff by hand can be frustrating...

    Or: you inform them how long the process will take manually. If they don't care, then you create said tool and let it do a day's worth of work in the background while you play games or something all day, without them knowing it exists.


  •  [b]Part 3[/b]

    Well, seems like my own options is to manually edit trough MAXbureau. If I believe my superiors and its helpdesk at least.

    Well, thats not what I'm going to do. Not in 1000 years. Welcome to: Greasemonkey.

    I've spent the whole morning figuring out how the edit form worked. Greasemonkey autocompletes pretty much everything important now. If I can think of a way to make 20.000 ID to MAC records available to javascript I'm done. (I've got them in a few different data sources. I think I'm going to write a little PHP script that reads them out of a .csv file and that greasemonkey can access with AJAX, unless someone has a better idea)

    Now the only remaining thing is to automatically open the records (and close them, but thats fairly easy). On my quest to discover that I've discovered another beautifull representative line:

    [code]Columns.altEditUrls = ['javascript: GridActions.openSelectedEditable();', 'javascript: GridActions.openSelectedEditable();',
    , 'javascript: GridActions.openSelectedEditable();',
    [i][96x omitted][/i],'javascript: GridActions.openSelectedEditable();'];[/code]

     No kidding. You can find it in "scripts/grids/columns.js" if you don't believe me.

    [b][i]To be continued[/i][/b] 

     



  • [b]Part 3.5[/b]

    My boss asked how it was going and I risked showing him what I was doing. His comments were "neat, neat. So if you can reach that it automatically opens records you can do it fully automatic. Go on with this. I see we got the right man on the job."

    Luckily my boss his boss is the head of the organisation. He is much to busy richening himself and yelling at the board of directors, so Kefer's scenario probally wont become reality. Well, but the rest of the profecy did.... I'm getting scared.

    [b][i]Still going to be continued[/i][/b] 



  • @dtech said:

    <SNIP>

    Well, but the rest of the profecy did.... I'm getting scared.

     Well, every rule has its exceptions.

     

     

     

    They say.



  • This story would have been funnier if you had left after realizing that they had effectively hired you as a data entry clerk.  Yes, you've showed great resourcefulness, but also great spinelessness.

    You've been at the company for a week by your own admission and you're already complaining about the CEO.  What are you trying to prove? 

    <hints id="hah_hints"></hints>


  • @Aaron said:

    but also great spinelessness.
     

    Ahhh your first introduction to the Dutch...



  • @MasterPlanSoftware said:

    @Aaron said:

    but also great spinelessness.
     

    Ahhh your first introduction to the Dutch...

    Do I need to be offended now? Ah whatever.



  • @MasterPlanSoftware said:

    Ahhh your first introduction to the Dutch...
    The troll's awake! Stay away from the bridge!



  •  Haha...



  • @DOA said:

    @MasterPlanSoftware said:

    Ahhh your first introduction to the Dutch...
    The troll's awake! Stay away from the bridge!

     

    Whoops. I am sorry to offend you and your (apparently) humorless country.



  •  The CEO comment was just meant to be humorrous. A brief summary of the things I've heard from coworkers.

    I didn't leave because 1: I thought/think I could/can manage something like this and 2: I like the money (there aren't much ICT-jobs for non or freshly gratuaded CS students here)



  • @Aaron said:

    This story would have been funnier if you had left after realizing that they had effectively hired you as a data entry clerk.

    Some of us don't have the luxury of quitting a job the minute we suspect it's going to be a pain in the rear.



  • @MasterPlanSoftware said:

    Whoops. I am sorry to offend you and your (apparently) humorless country.
    I'm not Dutch.

    However you're still a troll :)



  • Gradations of truth

    Just found another pearl.

    [code]var cusBool = new Array(1,2,3,4,5,6,7,8,9,10);[/code] 



  • @DOA said:

    @MasterPlanSoftware said:

    <SNIP> your (apparently) humorless country.
    I'm not Dutch.

    <SNIP>

    Figures...



  • @dtech said:

    Just found another pearl.

    <font size="2" face="Lucida Console">var cusBool = new Array(1,2,3,4,5,6,7,8,9,10);</font> 

    Please tell me that makes more sense than it looks like it makes...



  • @DOA said:

    I'm not Dutch.
     

    You still lack any humor.

    @DOA said:

    However you're still a troll :)

    I know you wish that were true, but you can say the same lie a thousand times and it never becomes any more true.



  • @MasterPlanSoftware said:

    you can say the same lie a thousand times and it never becomes any more true.
    So I should stop telling people about my 124-foot penis made of gold, then?



  • @bstorer said:

    So I should stop telling people about my 124-foot penis made of gold, then?

    Only if you like to see morbius QQ.



  • @bstorer said:

    @dtech said:

    Just found another pearl.

    <font face="Lucida Console" size="2">var cusBool = new Array(1,2,3,4,5,6,7,8,9,10);</font> 

    Please tell me that makes more sense than it looks like it makes...
     

    I'm not really sure yet. I've seen it used about 4 times now, all without apparent use or comments. I haven't been able to do a search trough files search yet.

     



  • @dtech said:

    I've been thinking about replacing me with 3 mail-order Indian employees at 1/4th of my salary. You think anyone will notice?
     

    Your bosses, when they notice a trickle of productivity coming from you where there was none before?



  • Of course its possible:

     

    1. Write a perl script to pull down the db out of the UI and add it to a local one

    2. Reformat the db schema as good as you can

    3. Write a perl script to mirror the local db to the remote.

     

    I wish I could use my scarcasm tag, but this stuff is not that hard. They even gave you a web UI. 



  • Your 2 (and thus 3) options make no sense. It's not like I have a PHPMyAdmin-alike web-interface, I have a web-interface which is gluid together with 7 different iframes and 300 KB of javascript. I cannot modify the database shema, only the values in it. Trough the web interface.

    And your 1 option is almost exactly what I'm doing, see an adbove post of mine. 



  • @dtech said:

    Your 2 (and thus 3) options make no sense.

     

     Yep. I must be crazy :-)

    @dtech said:

    It's not like I have a PHPMyAdmin-alike web-interface, I have a web-interface which is gluid together with 7 different iframes and 300 KB of javascript.

     

    So? If it has a web interface, it can be automated. You can automate it at the IE level with something like Win32::IE::Mechanize, Or use a web proxy to determine the required HTTP inputs to control the data. Heck, HTTP::Recorder will even store the procedures as you click around, so you can use the clickity-click method for the first, and use the HTTP::Recorder output to do the rest.

    @dtech said:

    I cannot modify the database shema, only the values in it. Trough the web interface.

     

    Your task involves fixing relationships and sanitizing the data as opposed to data entry, hence my use of the word "schema".

    @dtech said:

    And your 1 option is almost exactly what I'm doing, see an adbove post of mine. 

     

    You're starting to head down the right path. I did only read the OP before posting.

    I agree that not giving you direct access to the database is a WTF... but your reaction to this is TRWTF.

    Bottom line, your task is MAX 30 hours for a competent and proficient engineer. 



  • @trwww said:

    @dtech said:

    Your 2 (and thus 3) options make no sense.

     

     Yep. I must be crazy :-)

    I did not say you're crazy. I said the option made no sense.

    @trwww said:

    @dtech said:

    It's not like I have a PHPMyAdmin-alike web-interface, I have a web-interface which is gluid together with 7 different iframes and 300 KB of javascript.

     

    So? If it has a web interface, it can be automated. You can automate it at the IE level with something like Win32::IE::Mechanize, Or use a web proxy to determine the required HTTP inputs to control the data. Heck, HTTP::Recorder will even store the procedures as you click around, so you can use the clickity-click method for the first, and use the HTTP::Recorder output to do the rest.

    Automating. That is exactly what I'm doing. As I've posted I am creating a greasemonkey to automate the job.

    @trwww said:


    @dtech said:

    And your 1 option is almost exactly what I'm doing, see an adbove post of mine. 

     

    You're starting to head down the right path. I did only read the OP before posting.

    I agree that not giving you direct access to the database is a WTF... but your reaction to this is TRWTF.

    Bottom line, your task is MAX 30 hours for a competent and proficient engineer. 

     

    At the moment I've spent 2 days (15 hours) on it. I've now reached that the modification are done automatically on the form, notes are being appended to the record and then the record is saved. So I've still got 15 hours to automate the opening of a record. That one's gonna be a close call with MAXbureau's messed up interface. 



  • @dtech said:

    At the moment I've spent 2 days (15 hours) on it. I've now reached that the modification are done automatically on the form, notes are being appended to the record and then the record is saved. So I've still got 15 hours to automate the opening of a record. That one's gonna be a close call with MAXbureau's messed up interface.

    It took me 2 to 3 hours to write GM scripts and a screen-scraper in PHP to pull data out of Community Server.  I even did the HTTP requests raw just using sockets.  I know it's gross, but it shouldn't be too hard to write a script to interface with a web UI.  I've done this many times before professionally and as long as you know HTTP and how web apps work it should be pretty easy. 



  • @morbiuswilters said:

    It took me 2 to 3 hours to write GM scripts and a screen-scraper in PHP to pull data out of Community Server.  I even did the HTTP requests raw just using sockets.  I know it's gross, but it shouldn't be too hard to write a script to interface with a web UI.  I've done this many times before professionally and as long as you know HTTP and how web apps work it should be pretty easy. 

     

     I'll post the script and the DOM when I'm ready. Then you and me can judge if this words are true.



  •  @morbiuswilters said:

    It took me 2 to 3 hours to write GM scripts and a screen-scraper in PHP to pull data out of Community Server.  I even did the HTTP requests raw just using sockets.  I know it's gross, but it shouldn't be too hard to write a script to interface with a web UI.  I've done this many times before professionally and as long as you know HTTP and how web apps work it should be pretty easy. 

    I've almost got the automatic-opening edit working. The resulting script is suprisingly easy (as espected you only needed an fairly basic but also a mysterious 6 unique keys) but you couldn't have gessued what I had to do to construct that URL. I'll give you a hint: the application itself goes trough a number of functions ranging from 48 to 93, it depends a bit on the branches taken. I've also found quite a few bugs in them, most alongside the lines of this:

    [code]function foo(url, var, var){
    if(something){ bar(changedurl, changedvar, var); }
    }
    function bar(url, var, var){
    if(something){ foo(changedurl, var, changedvar); }
    }
    [/code] 

    That also explains the few times Firefox froze for a few seconds followed by: "This script blablabla. [Stop Script] [Continue with Script]" 



  • @dtech said:

    The resulting script is suprisingly easy (as espected you only needed an fairly basic but also a mysterious 6 unique keys) but you couldn't have gessued what I had to do to construct that URL.
     

    Instead of reverse engineering (well, trying to understand) the JavaScript, my first approach would've been to capture a network dump with tcpdump/wireshark. It's usually much easier to just look at what's being transmitted and then modify the fields to your needs. As a bonus, if the network isn't fully switched you may catch some SQL traffic on the side, capturing plain text logins and what not. Then you could just work with the DB directly :-) Or get fired for "hacking the network". Whichever applies :)

     This is a really fascinating development to follow in any case. Thanks for keeping us updated and the thread interesting :)



  •  Part 4

    Well, I'm finnaly done with the script. Its correcting the records at about 30 records/minute so it should be done tomorrow morning.

    TD was still full of WTF's, but there's one I can remember that particulary stood out. It does exactly what you think it does.

    [code]window.attachEvent("onscroll", window.parent.Grid.scrollHeaders);[/code]

    That's right. Scrolling is done [b]with javascript[/b]. If you "scroll" you don't actually scroll, the rows of a table are just being replaced.


Log in to reply