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:
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.
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.
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.
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:
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.
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.
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.
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.
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.
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) =
DEBUG: Tested for table
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.