March 13, 2018 at 5:36 pm
Hi All
I'm very new to Powershell and I need some help with the next step in this 'thing' I have taken on.
Originating Server is Windows Server 2012 R2 and SQL Server 2016. I'm building a Compliance database and need to get server/database type information from a number of servers - there are about 200+ in the organisation.
I'm exploring the use of powershell to do this as I didn't want to overload the target servers too much and PowerShell is quicker.
Target servers are various windows/SQL versions - in terms of SQL they will range from SQL Server 2005 to SQL2016
I have a script already - hacked from various pieces of code and at the moment it does
1) a connection to a target SQL Server
2) selects the server properties
3) outputs the data to the screen
I now need to be able to insert this data into a table on the originating server (centeral dba management server)
Eventually I will code this powershell script to loop through a list of sql servers kept in a text file and do the server properties extract for each server and insert into the table
I can then write an SSRS report to output this data.
Can someone please help me with inserting the data into the SQL table - I've added the table definition and the ps script.
Would invoke-sqlcmd be the way to go? Some target server may not have this commandlet as they are older OS and SQL but the Server (originating) where the command will execute from does.
any help would be great - thanks
table definition:
CREATE TABLE [dbo].[Servers](
[HostName] [varchar](50) NOT NULL,
[SQLServerName] [varchar](50) NOT NULL,
[SQLServerInstanceName] [varchar](50) NULL,
[Instance] varchar](50) NULL,
[MajorVersion] [varchar](50) NOT NULL,
[VersionString] [varchar](50) NOT NULL,
[ProductLevel] [varchar](50) NULL,
[Language] [varchar](50) NOT NULL,
[Platform] [varchar](50) NOT NULL,
[Edition] [varchar](50) NULL,
[Processors] [char](3) NULL,
[OSVersion] [varchar](50) NULL,
[PhysicalMemory] [varchar](20) NULL,
[RootDirectory] [nvarchar](512) NULL,
[IsClustered] [bit] NULL,
[ISAlwayOn_enabled] [bit] NULL,
[SQLServerCollation] [varchar](50) NULL,
CONSTRAINT [PK_HostName] PRIMARY KEY CLUSTERED
([HostName] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ps script:
$ServerInstance = "NZ8038SP0022"
$Database = "Master"
$ConnectionTimeout = 30
$Query = "create table #server(ID int, Name sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver
declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT
SELECTCONVERT(varchar(20), SERVERPROPERTY('MachineName')) HostName,
CONVERT(varchar(20), SERVERPROPERTY('servername')) SQLServerName,
CONVERT(varchar(20), SERVERPROPERTY('InstanceName')) SQLServerInstanceName,
CONVERT(varchar(20), SERVERPROPERTY('Instance')) Instance,
CASE
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '8.00.%' THEN 'Microsoft SQL Server 2000'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '9.00.%' THEN 'Microsoft SQL Server 2005'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '10.00.%' THEN 'Microsoft SQL Server 2008'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '10.50.%' THEN 'Microsoft SQL Server 2008 R2'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '11.0%' THEN 'Microsoft SQL Server 2012'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '12.0%' THEN 'Microsoft SQL Server 2014'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '13.0%' THEN 'Microsoft SQL Server 2016'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '14.0%' THEN 'Microsoft SQL Server 2017'
END as 'MajorVersion',
convert(varchar(50),SERVERPROPERTY(N'ProductVersion')) AS [VersionString],
convert(varchar(50),SERVERPROPERTY(N'ProductLevel')) AS [ProductLevel],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
convert(varchar(50), SERVERPROPERTY(N'Edition')) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
@RegRootDir AS [RootDirectory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY('IsHadrEnabled') AS bit) AS [IsAlwaysOn_enabled],
convert(varchar(50), serverproperty(N'collation')) AS [Collation]
drop table #server"
$QueryTimeout = 120
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables
March 13, 2018 at 5:55 pm
Does this help? http://www.sqlservercentral.com/scripts/PowerShell/167128/
March 13, 2018 at 5:56 pm
And perhaps this? http://www.sqlservercentral.com/articles/Stairway+Series/134672/
March 13, 2018 at 5:58 pm
thanks Lynn
I found the first one and getting that into my script now -
must have missed the second one though - will need to have a read of it
regards
March 15, 2018 at 12:43 pm
I finally managed to do what I wanted - not sure if this is the most efficient way but it does the job for now
It took a while and I had issues with the ds.table array(?) and tying that into the table schema - what I learnt here was that the column names have to be the same. I had issues also with the quotes setup in the insert statement to escape quotes.
Anyway in the hopes it might help someone else or if someone can tell me i can do this better -!
#loop through the serverlist.txt files for the list of servers to collect information for
foreach (
$servername in (get-content E:\Powershell_scripts\serverlist.txt))
{
#
$ServerInstance = $servername
$Database = "Master"
$ConnectionTimeout = 30
$Query = "
create table #server(ID int, Name sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver
declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUTSELECT CONVERT(varchar(20), SERVERPROPERTY('MachineName')) HostName,
CONVERT(varchar(20), SERVERPROPERTY('servername')) SQLServerName,
CONVERT(varchar(20), SERVERPROPERTY('InstanceName')) SQLServerInstanceName,
CONVERT(varchar(20), SERVERPROPERTY('Instance')) Instance,
CASE
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '8.00.%' THEN 'Microsoft SQL Server 2000'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '9.00.%' THEN 'Microsoft SQL Server 2005'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '10.00.%' THEN 'Microsoft SQL Server 2008'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '10.50.%' THEN 'Microsoft SQL Server 2008 R2'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '11.0%' THEN 'Microsoft SQL Server 2012'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '12.0%' THEN 'Microsoft SQL Server 2014'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '13.0%' THEN 'Microsoft SQL Server 2016'
WHEN convert(varchar(50), SERVERPROPERTY(N'ProductVersion')) LIKE '14.0%' THEN 'Microsoft SQL Server 2017'
END 'MajorVersion',
convert(varchar(50),SERVERPROPERTY(N'ProductVersion')) [VersionString],
convert(varchar(50),SERVERPROPERTY(N'ProductLevel')) [ProductLevel],
(select Value from #server where Name = N'Language') [Language],
(select Value from #server where Name = N'Platform') [Platform],
convert(varchar(50), SERVERPROPERTY(N'Edition')) [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') [Processors],
(select Value from #server where Name = N'WindowsVersion') [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') [PhysicalMemory],
@RegRootDir [RootDirectory],
SERVERPROPERTY('IsClustered') [IsClustered],
case WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 1
else 0
end [IsAlwaysOn_enabled],
convert(varchar(50), serverproperty(N'collation')) [SQLServerCollation]drop table #server
"
$QueryTimeout = 120
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
#next command comments out as this essentially outputs the table array to the screen for debugging purposes
#$ds.Tables
#
$insertquery ="
INSERT INTO [dbo].[Servers]
([HostName]
,[SQLServerName]
,[SQLServerInstanceName]
,[Instance]
,[MajorVersion]
,[VersionString]
,[ProductLevel]
,[Language]
,[Platform]
,[Edition]
,[Processors]
,[OSVersion]
,[PhysicalMemory]
,[RootDirectory]
,[IsClustered]
,[IsAlwaysOn_enabled]
,[SQLServerCollation])
VALUES
('" + $ds.tables[0].Rows[0].HostName + "','" +
$ds.tables[0].Rows[0].SQLServerName + "','" +
$ds.tables[0].Rows[0].SQLServerInstanceName + "','" +
$ds.tables[0].Rows[0].Instance + "','" +
$ds.tables[0].Rows[0].MajorVersion + "','" +
$ds.tables[0].Rows[0].VersionString + "','" +
$ds.tables[0].Rows[0].ProductLevel + "','" +
$ds.tables[0].Rows[0].Language + "','" +
$ds.tables[0].Rows[0].Platform + "','" +
$ds.tables[0].Rows[0].Edition + "','" +
$ds.tables[0].Rows[0].Processors + "','" +
$ds.tables[0].Rows[0].OSVersion + "','" +
$ds.tables[0].Rows[0].PhysicalMemory + "','" +
$ds.tables[0].Rows[0].RootDirectory + "'," +
$ds.tables[0].Rows[0].IsClustered + "," +
$ds.tables[0].Rows[0].IsAlwaysOn_enabled + ",'" +
$ds.tables[0].Rows[0].SQLServerCollation + "')"
#the ds.tables[0].Rows[0] is coded this way as essentially only one row is being returned from the earlier select statement
#echo $insertquery
Invoke-SQLcmd -query $insertquery -ServerInstance "localhost" -Database Compliance
}
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply