$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
$basepath="C:Report"
$instancepath=$basepath + "SQL Inventoryinstances.txt"
$outputfile="SQL InventoryOutPut1SQLServer_Inventory_" + $isodate + ".csv"
-- $((Get-Date).ToString('dd-MMM-yy'))
$outputfilefull = $basepath + $outputfile $filePath = ""
$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "
create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver declare @SmoRoot nvarchar(512)
DECLARE @sn NVARCHAR(128)
DECLARE @sa NVARCHAR(128)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSetup', N'SQLPath', @SmoRoot OUTPUT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEMCurrentControlSetservicesSQLSERVERAGENT',N'ObjectName', @sn OUTPUT;
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEMCurrentControlSetservicesMSSQLSERVER',N'ObjectName', @sa OUTPUT; SELECT
@@SERVERNAME as ServerName,@@ServiceName as [InstanceName], case when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '12.%' then 'SQL Server 2014'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '11.%' then 'SQL Server 2012'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.5%' then 'SQL Server 2008R2'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '9.0%' then 'SQL Server 2005'
else 'Not Found'
end as VersionName,
SERVERPROPERTY(N'ProductVersion') AS [Number],
SERVERPROPERTY('ProductLevel') AS SP,
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
case when CAST(SERVERPROPERTY('IsClustered') AS bit) =1 then 'YES'
else 'NO' END
AS [IsClustered],
case when CAST(SERVERPROPERTY('IsClustered') AS bit)= 1 then (select serverproperty('ComputerNamePhysicalNetBIOS'))
else NULL END as Active_Node_Name,
(SELECT NodeName
FROM sys.dm_os_cluster_nodes where NodeName !=(select serverproperty('ComputerNamePhysicalNetBIOS'))) as Passive_Node_Name,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ISHADREnabled')) like 0 THEN 'Disabled'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ISHADREnabled')) like 1 THEN 'Enabled'
ELSE 'unknown'
END AS ISHADREnabled,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 0 THEN 'Not started'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 1 THEN 'Started'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('HadrManagerStatus')) like 2 THEN 'Not started'
ELSE 'unknown'
END AS HadrManagerStatus,
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode],
@sa as [SQLService_Account],
@sn as [SQLAgent_Account],
(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%')AS max_server_memory_MB,
(SELECT value_in_use FROM sys.configurations WHERE name like '%min server memory%')AS min_server_memory_MB,
convert(sysname, serverproperty(N'collation')) AS [Collation],
(select Value from #SVer where Name = N'ProductName') AS [Product],
--(select Value from #SVer where Name = N'Language') AS [Language],
(select Value from #SVer where Name = N'Platform') AS [Platform], (select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],
(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory_In_MB],
@SmoRoot AS [RootDirectory],
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath,
SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName, CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 1 THEN 'Personal or Desktop Engine'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 2 THEN 'Standard'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 3 THEN 'Enterprise'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 4 THEN 'Express'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 5 THEN 'SQL Database'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 6 THEN 'SQL Data Warehouse'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EngineEdition')) like 8 THEN 'Managed Instance' ELSE 'unknown'
END AS EngineEdition,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('LicenseType') AS LicenseType drop table #SVer
"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr) $cn.Close()
} $dt | export-csv -NoTypeInformation $outputfilefull $filepath=$outputfilefull |
