none
Extract data from server using Powershell and export to sql DB. RRS feed

  • Question

  • We currently have an old VB script that sends data to our sql DB.  Unfortunately the vb script is locked and we can't add any new fields to the script so I wanted to use powershell to get some needed data from the server and then export to a SQL DB that we can later query to run reports.

    Needed data:

    Server name / OS / Make / Model / Service Tag (if not virtual) along with other data.

    Has anyone setup something like this?  Any help would be greatly appreciated.

    Thanks,


    Jr. Admin

    Wednesday, November 19, 2014 1:28 AM

Answers

All replies

  • Hi snteran,

    you can use the Get-WmiObject cmdlet to query Wmi, which will probably give you the information you need.

    Classes of interest:

    win32_baseboard
    win32_bios
    win32_operatingsystem

    That'll get you the information you need. Then you need to feed that info to your database, which means you need to run an "Insert" SQL command. To run an SQL command you may want to search for "Invoke-SQLCmd"

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Wednesday, November 19, 2014 7:52 AM
  • Are you wanting to pass directly into SQL or send all data to say Excel and then import the data from the CSV?

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, November 19, 2014 3:07 PM
  • Assuming you need script to inventory your servers and save in SQL database

    Refer this link

    https://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/


    Regards Chen V [MCTS SharePoint 2010]

    Wednesday, November 19, 2014 3:12 PM
  • Assuming you need script to inventory your servers and save in SQL database

    Refer this link

    https://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/


    Regards Chen V [MCTS SharePoint 2010]


    Very nice little tutorial on using a DB with PowerShell.

    ¯\_(ツ)_/¯

    Thursday, December 4, 2014 2:27 AM
    Moderator
  • Thank you for the link...I'll review and make sure to post any findings that help build our DB.  I think once I get the connection aspect figured out, next is to think out the database scheme.  Especially for items that will have multiple entries.  i.e. - Disk drives, I think i will have to create a table called Disk_drives, have the Server_ID, plus all of it's different drives.  Then find a way to see if the drive is local or attached to a SAN device.  Also IP's, all of our servers have a minimum of 2 IP's so I'll need to break those out.  One step at a time, but hopefully at the end of the day, we can have a nice inventory site that will allow for a lot of reports to be generated. Most companies have a budget for tools, unfortunately I don't work for one of those companies.

    Thanks again,


    Jr. Admin

    Saturday, December 13, 2014 11:20 PM