Use PowerShell to Collect Server Data and Write to SQL

re: Use PowerShell to Collect Server Data and Write to SQL

wheresjeannine — Wed, 13 Feb 2013 23:56:42 GMT

I want to run my PS script that dumps all details of all WEBs, Lists, Libraries, Folders, Documents, ITems and Attachments from SharePoint and send that output to a SQL Server database table. Q: Can do? Q: Am I dreaming too big? Imquiring minds need to know jeannine.menger@recommind (dot) com

re: Use PowerShell to Collect Server Data and Write to SQL

mbourgon — Wed, 06 Feb 2013 19:47:30 GMT

Answering my own question, sorta - asked on Stackoverflow and Chad Miller (the guy who wrote the above code) answered.

Two ways to do it, provided you ran something like this:

$dt = invoke-sqlcmd2 -serverinstance $_.server -query "exec master.dbo.sp_who" -As 'Datatable'

and you want to add the Servername (yes, sp_who returns the server name, but I wanted an SP everyone had)

Either:

$Col = new-object Data.DataColumn

$Col.ColumnName = "ServerName"

$dt.Columns.Add($col)

$dt | %{$_.ServerName = $server}

Or (abbreviated version):

$dt.Columns.Add("ServerName")

$dt | %{$_.ServerName = $server} #in my main foreach I say $server = $_.server

re: Use PowerShell to Collect Server Data and Write to SQL

mbourgon — Tue, 05 Feb 2013 20:30:38 GMT

(grr - stupid post button)

JV - sorry, how do you do that in powershell? (should have been more clear).

Say I did this:

$dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

I now have a datatable. I want to copy the details of $dt into a new DataTable (so that I can add the ServerName field), then write using write-datatable. But since I'm multithreading it, I need to do this within the PS script; I can't use t-sql or interim tables for that part.

re: Use PowerShell to Collect Server Data and Write to SQL

mbourgon — Tue, 05 Feb 2013 20:30:02 GMT

JV - sorry, how do you do that in powershell? (should have been more clear).

Say I did this:

$dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

re: Use PowerShell to Collect Server Data and Write to SQL

JV — Tue, 05 Feb 2013 16:58:59 GMT

@mbourgon

insert into datatablea2 select 'myserver as ServreName, * from datatable1

re: Use PowerShell to Collect Server Data and Write to SQL

mbourgon — Tue, 05 Feb 2013 16:22:07 GMT

JV - cool! Now I'm getting somewhere. Is there an easy way to say "copy field + datatable1 into datatable2"?

I'm trying to take the results of an SP and add the servername to it, before writing to a table. All help appreciated!

re: Use PowerShell to Collect Server Data and Write to SQL

JV — Mon, 04 Feb 2013 22:44:56 GMT

@mbourgon

You can't a a column to the beginning of a table without dropping and recreating the table.

re: Use PowerShell to Collect Server Data and Write to SQL

mbourgon — Mon, 04 Feb 2013 22:26:45 GMT

Say I run a query using invoke-sqlcmd2, then need to add a field to the datatable (preferably at the beginning of the row) prior to using write-datatable. How do I do this? I've been futzing with add-member without any success. Any help greatly appreciated!

re: Use PowerShell to Collect Server Data and Write to SQL

cmille19 — Sun, 12 Jun 2011 18:01:37 GMT

inanutshell

See discussion under Out-Datatable for how to handle text files:

gallery.technet.microsoft.com/.../4208a159-a52e-4b99-83d4-8048468d29dd

re: Use PowerShell to Collect Server Data and Write to SQL

inanutshell — Mon, 06 Jun 2011 15:34:46 GMT

I'm using get-content on a text file, and then using the select object on those results. Finally I'm piping that to out-datatable, but get nothing but the length column. Since I guess what is getting passed is a string array the actual string value is not grabbed from $object.PsObject.get_properties() in the out-datatable definition. Is this by design? Does out-datatable not work with string arrays?