5 Replies

  • First, I'd recommend grabbing a tool like dBeaver if you haven't already. It allows you to build queries, browse data, edit data, and it will take care of your CSV importing question.

    Once you add a new database connection, in the navigator on the left side, you can drill down to your database. In dBeaver, you can right click on the db name -> import from there to put the csv file into a table. The built-in wizard usually does a good job for that portion. You can also double click on just about anything to view more details.

    Your tables will essentially look like a spreadsheet of the data in a csv but one column needs to be the primary key. The primary key just needs to be a column that will never have the same data in two rows. They're kind of like IP addresses in that the system always assumes they'll be unique and uses them to make sure that you're grabbing the right row. It can be as simple as a counter that goes up for each entry but can be anything. Names, UUIDs, etc - as long as each entry in that column is unique, it can be used. Do keep in mind that some systems will let you shoot yourself in the foot here and enter non-unique entries which can cause errors.

    Lastly, you're probably looking for joins to manipulate the data. These clauses let you choose a column in separate tables and merge the data based on matches that appear in those columns. You'd have to join each table together and, at least in this case, you'd want to use the unique constraint at the end to only list the programs only once.

    I will say, joining more than 2 tables at once can get complex quickly. If I was just learning in a test environment, I would join two together and save the output in a new table each time. That way you can take it in smaller steps to examine and go back to an earlier attempt if something goes wrong. Hopefully, that makes at least a little sense! Feeling a bit rambly today. Let me know if I went too far off base.

    Edit: forgot a link to unique constraints
    Was this post helpful? thumb_up thumb_down
  • You seem to be trying to recreate a software inventory system for your environment. And you are probably coming at it from the backend instead of the deployment and authorization which makes it like reverse engineering. This has been attempted by hand for decades. SCCM is supposed to do this among other software systems.

    Was this post helpful? thumb_up thumb_down
  • ENCOM2112

    Thanks for that info! I'll look into dbeaver for sure! (Instead of using actual data, I'm going to just try and generate some pseudo-random CSV files, so they will be smaller, but I'm still going to learn dBever because I feel like the ability to import a thousands of rows long CSV file is very useful/powerful)

    Okay so for the primary key (choosing the primary key was one of my main questions) I think that for the computer/user list, it can be the hostname; for the employee/department list, it can be the username;... I'm not sure what to pick for the computer/app csv, because it's multiple apps per computer, but also each app shows up on multiple machines. 

    Joins! I think that's the puzzle piece I was missing! Thanks!

    I'll post back here once I make some progress (probably won't be able to work on this project until tomorrow evening)


    Thanks for the info about "Microsoft System Center Configuration Manager", and yes I am looking at this as kind of backwards, as it seems like the RMM we're using should be able to do this, but, it doesn't.

    The real reason I'm doing this though is not to be able to generate the report that shows apps per department, but to learn how to manipulate datasets in databases (see lat line of my post)

    Thank you both for the advice!!! :-)

    Spice (1) flagReport
    Was this post helpful? thumb_up thumb_down
  • Just so you have a reference, this is the beginnings of a CMDB - Configuration management database

    Was this post helpful? thumb_up thumb_down
  • That's cool! 

    I don't want to build one of those.

    I just want to continue learning how to combine datasets like this (this is just one scenario and I personally learn best from real-world examples)

    Another would be maybe combining snake migration data with fertilizer runoff to determine the next time the rodent population will spike (run-off will kill amphibians and reptiles, and increase plant growth, which increases rodent population, which will cause an increase in snake population until the rodent population decreases, which will increase the next time the fertilizer factory ramps up production?)

    Spice (1) flagReport
    Was this post helpful? thumb_up thumb_down

Read these next...

  • Snap! Reporting phishing in Teams, State of IT, Arc A770 graphics card, Optimus

    Snap! Reporting phishing in Teams, State of IT, Arc A770 graphics card, Optimus

    Spiceworks Originals

    Your daily dose of tech news, in brief. How is it already Monday? Actually, how is it already October 2022? It felt like SpiceWorld was just starting, and already it's over (for this year). But don't worry, the fun continues as this month is Cybers...

  • Can you run a print server on windows 10

    Can you run a print server on windows 10


    I have many clients with no servers.  Looking for the best way to manage printers.If I share the printer then that machine that shares the printer needs to be online to print correct?   Having to always download the print driver and lookup the printers IP...

  • Lost of VDI rights on Windows 10 Enterprise E3

    Lost of VDI rights on Windows 10 Enterprise E3


    I am migration from standalone Windows 10 Enterprise E3 online subscription to Microsoft 365 E3 online subscription. However, I am seeing this in the terms.https://www.microsoft.com/licensing/terms/productoffering/Microsoft365/MOSADoes this mean I am losi...

  • Spark! Pro Series - 3rd October 2022

    Spark! Pro Series - 3rd October 2022

    Spiceworks Originals

    Welcome to Monday. I am currently sat at home in isolation, with my wife and I having succumbed to the dreaded Covid-19 after managing to avoid it for the last two and a half years! Still, it gives me the chance to indulge my fancy in yet another Spark! t...

  • What security best practices would you suggest?

    What security best practices would you suggest?


    It's Cybersecurity Awareness month, which if people took seriously, would likely be a great boon to all of ITkind. :)  The National Cybersecurity Alliance suggests some specific things that people can do to increase cybersecurity: Enabling multi-facto...