As Grant said, Powershell is your friend.
Start with defining a central db where you'll store all your collected data and tables to store data (redacted for your requirement only)
$SQLSRVR = “xxx”
$DB = “DBA”
$TableServerInfo = “PS.ServerInfo”
$TableDatabaseinfo = "PS.DatabaseInfo"
$TableDiskSpace = "PS.DiskSpaceInfo"
Then define the connection to each server you'll collect::
$SQLCON = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLSRVR;Initial Catalog=$DB;Integrated Security=SSPI")
$SQLCON.open()
$SQL = $SQLCON.CreateCommand()
and where the script should find your server list:
ForEach ($instance in Get-Content "C:\PS\servers.txt") #change the directory to where your site list is stored
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
Define query spaces:
$dbs=$s.Information
$db=$s.Databases
$res = $instance.split('\')[0]
Then define your queries as you need the information from SQL:
$DatabaseServerInfo = $dbs | Select Parent, FullyQualifiedNetName, Physicalmemory, Processors, PlatForm, NetName, OSVersion, language, Edition, Version, BuildNumber, Collation, IsClustered, IsHADREnabled, IsSingleUser, Get-Date
$DatabaseInfo = $db | select Parent, Name, Size, AvailabilityDatabaseSynchronizationState, CompatibilityLevel, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage, IsAccessible, IsUpdateable, RecoveryModel, LastBackupDate, LastLogBackupDate, MirroringStatus, Owner, ReadOnly, Get-Date, CreateDate
$DiskSpaceInfo = Get-WmiObject win32_logicaldisk -computername $instance.Split('\')[0] | Where-Object { $_.DriveType -eq 3 } | Select-Object SystemName,Name, DeviceID, Size, FreeSpace
Insert statements from each query into your predefined tables:
Foreach($DatabaseServer in $DatabaseServerInfo)
{
$Parent = $DatabaseServer.parent
$FullyQualifiedNetName = $DatabaseServer.FullyQualifiedNetName
$PhysicalMemory = $DatabaseServer.PhysicalMemory
$Processors = $DatabaseServer.Processors
$Platform = $DatabaseServer.Platform
$NetName = $DatabaseServer.NetName
$OSVersion = $DatabaseServer.OSVersion
$language = $DatabaseServer.language
$Edition = $DatabaseServer.Edition
$BuildNumber = $DatabaseServer.BuildNumber
$Collation = $DatabaseServer.Collation
$IsClustered = $DatabaseServer.IsClustered
$IsHADREnabled = $DatabaseServer.IsHADREnabled
$DateUpdated = Get-Date
$Version = $DatabaseServer.Version
$IsSingleUser = $DatabaseServer.IsSingleUser
$SQLupdate = "Insert into $TableServerInfo values ('$Parent', '$FullyQualifiedNetName', '$PhysicalMemory', '$Processors', '$Platform',
'$NetName', '$OSVersion', '$language', '$Edition','$Version', '$BuildNumber', '$Collation', '$IsClustered', '$IsHADREnabled', '$IsSingleUser', '$DateUpdated')"
Write-host $SQLupdate
$SQL.CommandText = $SQLupdate
$SQL.ExecuteNonQuery() > $null
}
Just posting sample code, but this should at least get you started, this is not perfect by any means, so feel free to modify and improve.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle