Help Posting script results to database

This topic contains 11 replies, has 3 voices, and was last updated by Profile photo of crashrebootrepeat crashrebootrepeat 1 year, 7 months ago.

  • Author
    Posts
  • #30318
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    I've got this script and I need to figure out how to get the data posted to a database. Can someone help walk me through that?

    `

    $computerSystem = Get-CimInstance CIM_ComputerSystem
    $computerBIOS = Get-CimInstance WIN32_BIOS
    $computerOS = Get-CimInstance CIM_OperatingSystem
    $computerCPU = Get-CimInstance CIM_Processor
    $computerHDD = Get-CimInstance Win32_LogicalDisk -Filter "DeviceID = 'C:'"
    $computerWSAT = Get-CimInstance Win32_WinSat
    $computerReliability = Get-CimInstance Win32_ReliabilityStabilityMetrics | Select-Object -First 1
    $computerDate = Get-Date
    Clear-Host

    Write-Host "System Information for: " $computerSystem.Name -BackgroundColor DarkCyan
    "Date : " + $computerDate
    "System Name: " + $computerSystem.Name
    "Manufacturer: " + $computerSystem.Manufacturer
    "Model: " + $computerSystem.Model
    "Serial Number: " + $computerBIOS.SerialNumber
    "BIOS Manufacturer: " + $computerBIOS.Manufacturer
    "BIOS Version: " + $computerBIOS.Version
    "SM BIOS Version: " + $computerBIOS.SMBIOSBIOSVersion
    "CPU: " + $computerCPU.Name
    "HDD Capacity: " + "{0:N2}" -f ($computerHDD.Size/1GB) + "GB"
    "HDD Space: " + "{0:P2}" -f ($computerHDD.FreeSpace/$computerHDD.Size) + " Free (" + "{0:N2}" -f ($computerHDD.FreeSpace/1GB) + "GB)"
    "Total RAM: " + "{0:N2}" -f ($computerSystem.TotalPhysicalMemory/1GB) + "GB"
    "Operating System: " + $computerOS.caption + ", Service Pack: " + $computerOS.ServicePackMajorVersion
    "OS Install Date: " + $computerOS.InstallDate
    "Last Reboot: " + $computerOS.LastBootUpTime
    "WinSat CPU Score: " + $computerWSAT.CPUScore
    "WinSat Disk Score: " + $computerWSAT.DiskScore
    "WinSat Graphics Score: " + $computerWSAT.GraphicsScore
    "WinSat Memory Score: " + $computerWSAT.MemoryScore
    "Computer Reliability Score: " + $computerReliability.SystemStabilityIndex
    `

  • #30323
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    It's certainly possible to assist you, but there is a lot of questions:

    What database? SQL?
    Will the script run locally on the computers?
    How do you intend to execute this script on remote systems?
    Does the WAN\LAN architecture need to be considered (e.g. multiple sites with a central datacenter)?
    Is there any systems management software deployed (e.g. SCCM\LanDesk\Altiris\KACE)?

    Typically, I don't write from the client directly to the database because I have less control of the ODBC drivers and more importantly want to get the data somewhere first. To accomplish this I do a queue based approach were the script runs, generates a output file (e.g. XML) which is copied to a network share. On the server with the network share I have a scheduled task running another script that loops through the files and does database updates.

    First things first, you really need to create your result as a PSObject for the data you want:

    $computerSystem = Get-CimInstance CIM_ComputerSystem
    $computerBIOS = Get-CimInstance WIN32_BIOS
    
    $props = @{
        "Date" = (Get-Date);
        "System Name" = $computerSystem.Name;
        "Manufacturer" = $computerSystem.Manufacturer;
        "Model" = $computerSystem.Model;
        "Serial Number" = $computerBIOS.SerialNumber;
        "BIOS Manufacturer" = $computerBIOS.Manufacturer;
        "BIOS Version" = $computerBIOS.Version;
    }
    
    New-Object -TypeName PSObject -Property $props
    

    Output:

    System Name       : LENOVOY50-ROB
    Serial Number     : 3514t6y53345172
    Model             : 2t4242
    BIOS Version      : LENOVO - 1
    Date              : 10/1/2015 1:06:26 PM
    BIOS Manufacturer : LENOVO
    Manufacturer      : LENOVO
    
  • #30333
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    OK Rob thanks for your help I have converted this to a PSObject.

    The database will be MSSQL
    It will run daily as a scheduled task on 3500(ish) systems
    We do have multiple sites but the database will be in Boston, MA USA
    We do have SCCM

    I am not opposed to doing it your suggested way of having it output to XML and copy and then import.

    $computerSystem = Get-CimInstance CIM_ComputerSystem
    $computerBIOS = Get-CimInstance WIN32_BIOS
    $computerCPU = Get-CimInstance CIM_Processor
    $computerOS = Get-CimInstance CIM_OperatingSystem
    $computerHDD = Get-CimInstance Win32_LogicalDisk -Filter "DeviceID = 'C:'"
    $computerWSAT = Get-CimInstance Win32_WinSat
    $computerReliability = Get-CimInstance Win32_ReliabilityStabilityMetrics | Select-Object -First 1

    $props = @{
    "Date" = (Get-Date);
    "System Name" = $computerSystem.Name;
    "Manufacturer" = $computerSystem.Manufacturer;
    "Model" = $computerSystem.Model;
    "Serial Number" = $computerBIOS.SerialNumber;
    "BIOS Manufacturer" = $computerBIOS.Manufacturer;
    "BIOS Version " = $computerBIOS.Version ;
    "SM BIOS Version" = $computerBIOS.SMBIOSBIOSVersion;
    "CPU" = $computerCPU.Name;
    "HDD Capacity " = "{0:N2}" -f ($computerHDD.Size/1GB) + "GB";
    "HDD Space " = "{0:P2}" -f ($computerHDD.FreeSpace/$computerHDD.Size) + " Free (" + "{0:N2}" -f ($computerHDD.FreeSpace/1GB) + "GB)";
    "Total RAM " = "{0:N2}" -f ($computerSystem.TotalPhysicalMemory/1GB) + "GB";
    "Operating System " = $computerOS.caption + ", Service Pack: " + $computerOS.ServicePackMajorVersion;
    "OS Install Date " = $computerOS.InstallDate;
    "Last Reboot " = $computerOS.LastBootUpTime;
    "WinSat CPU Score" = $computerWSAT.CPUScore;
    "WinSat Disk Score " = $computerWSAT.DiskScore;
    "WinSat Graphics Score " = $computerWSAT.GraphicsScore;
    "WinSat Memory Score " = $computerWSAT.MemoryScore;
    "Computer Reliability Score " = $computerReliability.SystemStabilityIndex;
    }

    New-Object -TypeName PSObject -Property $props

  • #30345
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    If you have SCCM, assuming it's inventorying, most (if not all) of this information already exists in the SCCM database. If you wanted WinSat scores specifically, you can work with the SCCM administrator and update the MOF file and add the WMI class to the inventory and let SCCM do this work for you. Even if you wanted a separate database for some reason, you could still get the data from the SCCM database rather than doing a client-side pull of information.

  • #30349
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    Thanks, if you could still help me figure out how to get this into a database it would be helpful the event I need to do something similar in the future.

  • #30360
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    @crashrebootrepeat
    You should check out the "Ditch Excel: Historical and Trend Reports with PowerShell and SQL Server" free ebook in the resources section.

  • #30370
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    I really appreciate the book being pointed out to me but I really need is a sample script. I need to see it and that's how I will learn. So if someone could help me either with mine or showing me something similar you have written then I be able to understand it.

  • #30371
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    The resource shared is a simple script. I don't think you can get any simpler. If you don't feel like reading, you can watch this youtube video by the author where he shows the usage of the script and how easy it is.

    Edit: I guess it's really a more complex script made into a tool that you can use in your script to make it simple.

  • #30375
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    When I write a complex script, I do it as a proof of concept, especially if I don't know how to do it. In your scenario, test each piece individually rather than coming up with the entire solution. Everyone's environment is different and it's hard from an outsider point of view to tell you what's best for your environment without some kind of discovery. Here is how I would start:

    1. Can I get the information I need from the client(s)? Test the WMI calls on all OS's to make sure that you get the information consistently. It appears you are part of the way there currently.
    2. On a DEV SQL server, create a database table with the data types you plan on using. For instance, dates have to be in certain formats the SQL will see as a datetime. Write the SQL command you will use to INSERT\UPDATE and pass mock parameters. At this point, you should have a single table with maybe 4-5 columns.
    3. There are multiple ways to connect to SQL with .NET, ADODB, ODBC, etc. that have advantages\disadvantages. To keep things simple, if you have SQL Management Studio or SQL installed on the server, there is a simple cmdlet Invoke-SQLCmd that is part of the SQLPS module. On the server that is going to make the database updates, open Powershell ISE and test writing data to the server using whichever Powershell method you wish. There are hundereds of examples on how to do this, as simple "Powershell Insert SQL" will provide you numerous examples of writing to SQL.
    4. Now I know that I can get data from the client and write to SQL with the data types I need. Create a share with appropriate permissions for the account that is gathering data to create a file. Simply take your PSObject you create (i.e. $results = New-Object...) and export the file to the share (e.g. $results | Export-CliXML \\server\share$\clientname.xml ) with the credentials that is doing the query on all machines.
    5. Now all of the parts are there. Create your database structure to hold all of the fields you require. Rewrite your SQL commands with the new columns\tables. Attempt to import the XML and send it to SQL.
  • #30400
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    @Curtis Smith thanks so much for posting the link to video, being able to actually see how it was supposed to work really helped out. I was able to modify this to how I needed it.

    Thanks to Don Jones for the module. Its a life saver.

  • #30401
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Yes, solid stuff. Glad it worked for you.

  • #30440
    Profile photo of crashrebootrepeat
    crashrebootrepeat
    Participant

    I do have one other question, how can I make this stop prompting I just want it to run

    I have tried

    $ConfirmPreference = 'SilentlyContinue'
    $WarningPreference = "SilentlyContinue"
    $VerbosePreference = 'SilentlyContinue'
    $DebugPreference = 'SilentlyContinue'
    get-sysinfo -computername localhost | Save-ReportData -ConnectionString "Server = server; Database = DT_Info; Integrated Security = True;"
    exit

    and it still comes up and says

    PS C:\Scripts> .\sysinfo.ps1
    VERBOSE: Table name is SysInfo
    VERBOSE: SELECT COUNT(*) AS num FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND LOWER(TABLE_NAME) =
    'sysinfo'
    DEBUG: Tested for table

    Confirm
    Continue with this operation?
    [Y] Yes [A] Yes to All [H] Halt Command [S] Suspend [?] Help (default is "Y"):

    I even tried commenting out everywhere there was a debug but it's still coming up.

You must be logged in to reply to this topic.