Solved

Powershell extraction issues

Posted on 2013-01-31
6
224 Views
Last Modified: 2013-02-20
I have following below PS script which I used to run this in PS program.
The "AllServers.txt" have around 200 server name list, when I run the scripts it saved the file but it takes last server name which I have in the list , why this happens ?


Set-Location "E:\MicroTest\"
$cmd = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
$set = new-object system.data.dataset
foreach ($svr in get-content "AllServers.txt")
{
  $constr = "server=$svr;database=master;Integrated Security=sspi"
  (new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $constr)).Fill($set) | out-null
  $obj = $set.Tables[0] | select-object @{n = 'Server'; e = {$svr}}, Version, SP
}
$obj | export-csv -NoTypeInformation "e:\MicroTest\AllServerVersions.csv"


Below is an example of the saved output.

"Server","Version","SP"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","9.00.4035.00","SP3"
"PrimaTest1122","8.00.2039","SP4"
"PrimaTest1122","8.00.2055","SP4"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.3042.00","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","9.00.4220.00","SP3"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","9.00.4220.00","SP3"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.4285.00","SP3"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","9.00.5000.00","SP4"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
"PrimaTest1122","10.0.4000.0","SP2"
0
Comment
Question by:motioneye
6 Comments
 
LVL 13

Assisted Solution

by:stergium
stergium earned 50 total points
ID: 38842790
Hello.
I first thought would be to change this
  $obj = $set.Tables[0] | select-object @{n = 'Server'; e = {$svr}}, Version, SP
to
  $obj = $set.Tables[0] | select-object @{n = $svr; e = {$svr}}, Version, SP

it should give you in any line the name of the server selected
please feed back
0
 
LVL 39

Assisted Solution

by:footech
footech earned 50 total points
ID: 38843448
I don't use PS w/ SQL, so I'm unfamiliar with a few of the commands in your script, but here's what I see is happening (kind of feeling my way through this).  In each loop of the foreach statement, the $obj variable is defined and it's value set (replacing what was previously set).  After the foreach completes all of its loops, $obj will have the last value that was assigned, which you then pipe to Export-CSV.  You either need to:
1) write to the file during every loop of the foreach, or
2) add the info to a variable and then after the foreach pipe all that accumulated data to Export-CSV.
Option 2 is usually the better way to go as it avoids having to open and close the file again and again (the open being by far the slowest operation).  I'm assuming that the rest of your script works just fine, just not the export to CSV part.  Since $obj is a table, I don't see any way around this except to either have the server name inserted/retrieved as part of the table, or deal with each row one at a time.

Set-Location "E:\MicroTest\"
$cmd = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
$set = new-object system.data.dataset
$obj = @()
foreach ($svr in get-content "AllServers.txt")
{
  $constr = "server=$svr;database=master;Integrated Security=sspi"
  (new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $constr)).Fill($set) | out-null
  $obj +=  $set.Tables[0].rows[-1] | select-object @{n = 'Server'; e = {$svr}}, Version, SP
}
$obj | export-csv -NoTypeInformation "e:\MicroTest\AllServerVersions.csv"

Open in new window

I'm totally guessing about the syntax to access the last row in the table ($set.Tables[0].rows[-1]) so forgive me if it doesn't work, but maybe it will give you other ideas.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 400 total points
ID: 38844092
The basic thoughts of footech are correct. $set will collect all results retrieved accumulated in $set.Tables[0], but contain no info about the server. However, $set.Tables[0].rows[-1] does not work (results in no output).

My suggestion is to enrich the DataSet with the server name, let it collect all info, and output it directly to Export-CSV:
Set-Location 'E:\MicroTest\'
$cmd = "SELECT SERVERPROPERTY('ServerName') as Server, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
$set = new-object system.data.dataset
foreach ($svr in get-content 'AllServers.txt')
{
  $constr = "server=$svr;database=master;Integrated Security=sspi"
  (new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $constr)).Fill($set) | out-null
}
$set.Tables[0] | export-csv -NoTypeInformation 'e:\MicroTest\AllServerVersions.csv'

Open in new window

This should be the fastest and least memory-consuming method available.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 38892260
You should not accept just all posts. Please make sure only helpful comments receive points, and to split the points according to the amount of help provided.

http:#a38842790 doesn't change anything to the good, it makes things worse. No points should be given for that.

http:#a38843448 adds some valid info, and though the code is not really working that way, it is only a small nuance failing. Accepting this comment is correct.

But http:#a38844092 shows the only working code, and since that is not based on posts of other Experts it should receive the major share of points.
0
 

Author Closing Comment

by:motioneye
ID: 38912495
Thanks, all of you have at least helped and give some ideas, of course the best answer will be awarded more, and sorry for the mistakes on awarding points
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Hi all.   The other day I had to change the passwords for a bunch of users on the fly. Because they were so many, I decided to do it in an automated way and I would like to share it with you all.   If you are not doing it directly in a Domain Co…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now