Powershell to do almost any thing

Below is a powershell script that will let you read servers from a table:-



CREATE TABLE [Core].[InstanceDatabases](
[DatabaseID] [bigint] IDENTITY(1,1) NOT NULL,
[InstanceID] [bigint] NOT NULL,
[DatabaseName] [varchar](50) NULL,
[RemoteDBID] [int] NULL,
[sysEffectiveFromDate] [datetime] NULL,
[sysEffectiveToDate] [datetime] NULL,
[Active] [bit] NULL,
 CONSTRAINT [PK_Instance_Databases] PRIMARY KEY CLUSTERED
(
[DatabaseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Core].[InstanceDatabases]  WITH CHECK ADD  CONSTRAINT [FK_InstanceDatabases_InstanceDetails] FOREIGN KEY([InstanceID])
REFERENCES [Core].[InstanceDetails] ([InstanceID])
GO

ALTER TABLE [Core].[InstanceDatabases] CHECK CONSTRAINT [FK_InstanceDatabases_InstanceDetails]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT (getutcdate()) FOR [sysEffectiveFromDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ('2099-01-01') FOR [sysEffectiveToDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ((0)) FOR [Active]
GO

In here can be stored all the servers you would like to connect to.

You will also require a few external scripts from other devs:-

http://poshcode.org/4967

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

You can then run any SQL on all boxes in your estate and place the answer back to a table.

Code:-


##########
Dave Dunckley
Version 1.1
Please fell free to use as your like, please post back any modifications.
############

. D:\Powershell\TSM\Plugins\invoke-sqlcmd2.ps1

. D:\Powershell\TSM\Plugins\Out-DataTable

cls

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$wmiquery = "select SystemName, Caption, DeviceID, DriveType, FileSystem, FreeSpace, Size from win32_LogicalDisk where DriveType = 3"


########################################
# Set Config Settings for this session #
########################################
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100

################################
# Setup and populate variables #
################################

# TSM Monitoring Server
$ManagementServer="servrename"

# DBA Monitoring DB
$ManagementDB="Monitor"

# OutCSV file
$CSVOUT = 'D:\Powershell\TSM\Temp\DBLastUsed.csv'

# Query String for servers to Collect Info from
$q = "SELECT distinct upper(InstanceName) FROM $ManagementDB.Core.InstanceDetails WHERE [InstanceScan] = 1 ORDER BY 1;"

# Query Conection String
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Initial Catalog=$MonitoringDB; Trusted_Connection=true");

# Open Connection
$sconn.Open()

# Open Data Adapter and create data table
$da = new-object System.Data.SqlClient.SqlDataAdapter ($q, $sconn)
$dt = new-object System.Data.DataTable

#Fill Data table
$da.fill($dt)

# Set Stage table name for Truncate
$StageTable = "DBLastUsed"

# Truncate Stage Table
Invoke-Sqlcmd –ServerInstance $ManagementServer –Database $ManagementDB –Query "truncate table stage.$StageTable"

#Loop though each server


foreach ($row.host_connection in $dt.Rows)
{
$Server = $($Row[0])
   
 
#run SQL
      invoke-sqlcmd2 -ServerInstance $Server -Database "master" -Query "
   
SELECT @@servername as servername, DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2"   |Export-CSV -NoTypeInformation $CSVOUT


#Output to temp CSV

$csvDataTable = Import-CSV -Path $CSVOUT | Out-DataTable


#Create conection

$ConnectionString = "Data Source=$ManagementServer; Database=$ManagementDB; Trusted_Connection=True;";

#Import from temp CSV directly to SQL

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
$bulkCopy.DestinationTableName = "stage.$StageTable"
$bulkCopy.WriteToServer($csvDataTable)
   



#close connections

$connSQLOUT.Open()


$cmd = $connSQLOUT.CreateCommand()
   
   
 
   
   $connSQLOUT.Close()
   
   
   


}

#enjoy!