cm2012_report.ps1

#########################################################
### SCCM 2012 Collect Info v.0.1.1
### Efimov (ge][) Gennady, http://gexeg.blogspot.com/
#########################################################

param (
    [string]$SQLServer = ".",
    [string]$CMDatabase,
    [string]$resultfile,
    [string]$xslfile = "cm2012_report.xsl",
    [switch]$help
    )

function help(){
    Write-Host "Efimov (ge][) Gennady"
    Write-Host "http://gexeg.blogspot.com/`n"
    Write-Host "Usage:`ncm2012_report.ps1 [-Server <string>] [-CMDatabase <CM Database>] [-resultfile <String>] [-xslfile <String>] [-help]"
}

function parse(){
    if ($help){
        help
        exit
    }
    if (-not $resultfile){
        $path = (Get-Item .).FullName
        $resultfile = Join-Path -Path $path -ChildPath ("{0}_{1}.xml" -f "cm2012_report",[System.DateTime]::Now.ToString("ddMMyyyy-hh.mm.ss"))
        Set-Variable -Name "resultfile" -Scope 1 -Value $resultfile
    }
}

function CreateSQLCmd($SQLConnection, $SQLText){
    $cmd = New-Object System.Data.SqlClient.SqlCommand($SQLText,$SQLConnection)
    #$cmd = $SQLConnection.CreateCommand()
    $cmd.CommandType = "Text"
    #$cmd.CommandText = $SQLText
    return $cmd
}

function GetCMDatabase($SQLConnection){
    if ($SQLConnection.State -eq "Open"){
        $result = CreateSQLCmd $SQLConnection "select name from sys.databases where name like 'CM[_][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]'"
        return $result.ExecuteScalar()
    }
    else {
        Write-Error "Соединение с сервером закрыто"
    }
    return $DatabaseName
}

function ReturnSQLResult($SQLConnection,$SQLText){
    $cmd = CreateSQLCmd $SQLConnection $SQLText
    $datareader = $cmd.ExecuteReader()
    $dataTable = New-Object System.Data.DataTable
    $dataTable.load($datareader)
    ,$dataTable
}


$inc = {
    $script:i += 1
    $script:i
}

parse

try {
    $conBldr = new-object  System.Data.SqlClient.SqlConnectionStringBuilder
    $conBldr.Server = $SQLServer
    $conBldr.Database = "master"
    $conBldr["Integrated Security"] = $true
    $sqlConnection = new-object System.Data.SqlClient.SQLConnection($conBldr.ConnectionString)
    Write-Progress -Activity "Подключение к серверу..." -Id 1 -Status "Running" -PercentComplete 1
    Write-Host "Open Connection"
    $sqlConnection.Open()
    if (-not $CMDatabase){
        $CMDatabase = GetCMDatabase $sqlConnection
        if (-not $CMDatabase) {
            Write-Error "Не удается определить сайт. Попробуйте указать в параметрах скрипта"
            return
        }
    }
    write-host "Сайт SCCM: $CMDatabase"

    $SqlConnection.ChangeDatabase($CMDatabase)

    Write-Progress -Activity "Сбор данных..." -Id 1 -Status "Running" -PercentComplete 25
    Write-Host "Сбор данных..."


    $boundary = ReturnSQLResult $SQLConnection "select * from BoundaryEx"
    $boundaryGroup = ReturnSQLResult $SQLConnection "select * from BoundaryGroup"
    $boundaryGroupMem = ReturnSQLResult $SQLConnection "select bgm.*, bg.Name, b.Value from [dbo].[BoundaryGroupMembers] bgm
       join dbo.BoundaryGroup bg on bgm.GroupID = bg.GroupID
       join dbo.BoundaryEx b on bgm.BoundaryID = b.BoundaryID"
    $boundaryGroupSys = ReturnSQLResult $SQLConnection "select * from BoundaryGroupSiteSystem"
    $sites = ReturnSQLResult $SQLConnection "select * from vSites"
    $subSites = ReturnSQLResult $SQLConnection "select * from vSiteAndSubsites"
    $srvComp = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_SC_Component]"
    $siteDef = ReturnSQLResult $SQLConnection "select * from SC_SiteDefinition"
    $siteDefProp = ReturnSQLResult $SQLConnection "select sd.SiteNumber, sd.SiteCode, sdp.*
        from SC_SiteDefinition sd join vSMS_SC_SiteDefinition_Properties sdp on sd.SiteNumber = sdp.ID"
    $siteDefPropList = ReturnSQLResult $SQLConnection "select sd.SiteNumber, sd.SiteCode, sdp.*
        from SC_SiteDefinition sd join [dbo].[vSMS_SC_SiteDefinition_PropertyLists] sdp on sd.SiteNumber = sdp.ID"
    $CompProp = ReturnSQLResult $SQLConnection "select c.ComponentName, cp.*
        from [dbo].[vSMS_SC_Component_SDK] c join [dbo].[vSMS_SC_Component_Properties] cp on c.ID = cp.ID"
    $CompPropList = ReturnSQLResult $SQLConnection "select c.ComponentName, cp.*
        from [dbo].[vSMS_SC_Component_SDK] c join [dbo].[vSMS_SC_Component_PropertyLists] cp on c.ID = cp.ID"
    $addr = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_SC_Address]"
    $addrProp = ReturnSQLResult $SQLConnection "select a.SiteCode, a.DesSiteCode, ap.*
       from [dbo].[vSMS_SC_Address_SDK] a join [dbo].[vSMS_SC_Address_Properties] ap on a.ID = ap.ID"
    $addrPropList = ReturnSQLResult $SQLConnection "select a.SiteCode, a.DesSiteCode, ap.*
       from [dbo].[vSMS_SC_Address_SDK] a join [dbo].[vSMS_SC_Address_PropertyLists] ap on a.ID = ap.ID"
    $ClientSettings = ReturnSQLResult $SQLConnection "SELECT * FROM vSMS_ClientSettings union all
        select [ID],[UniqueID],[Name],[Description],[Type],[Enabled],[Priority],[Flags],[DateCreated]
        ,[DateModified],[CreatedBy],[LastModifiedBy],[rowversion],[SourceSite],[IsTombstoned],
        NULL,[AssignmentCount]  from vSMS_ClientSettingsDefault"

    $ClientSettingsProp = ReturnSQLResult $SQLConnection "select a.Name, cs.Name, conf.*
    from vSMS_ClientAgentConfig_Base conf join ClientAgent a on conf.AgentID = a.ID
        join vSMS_ClientSettings cs on conf.SettingsID = cs.ID"

    $sys = ReturnSQLResult $SQLConnection "select * from vSMS_SC_SysResUse"
    $sysProp = ReturnSQLResult $SQLConnection "select s.RoleName, s.ServerName, sp.*
        from [dbo].[vSMS_SC_SysResUse_SDK] s join [dbo].[vSMS_SC_SysResUse_Properties] sp on s.ID = sp.ID"
    $sysPropList = ReturnSQLResult $SQLConnection "select s.RoleName, s.ServerName, sp.*
        from [dbo].[vSMS_SC_SysResUse_SDK] s join [dbo].[vSMS_SC_SysResUse_PropertyLists] sp on s.ID = sp.ID"

    $scprop = ReturnSQLResult $SQLConnection "select * from vSMS_SC_Property_SDK"
    $scpropList = ReturnSQLResult $SQLConnection "select * from vSMS_SC_PropertyList_SDK"

    $packages = ReturnSQLResult $SQLConnection "select * from v_Package"
    $supckages = ReturnSQLResult $SQLConnection "select * from vSMS_SoftwareUpdatesPackage_List"
    $driverPackages = ReturnSQLResult $SQLConnection "select * from [dbo].[v_DriverPackage]"
    $imagePackages = ReturnSQLResult $SQLConnection "select * from [dbo].[v_ImagePackage]"
    $bootPackages = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_BootImagePackage]"
    $osSourcePckg = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_OSInstallPackage]"
    $pkgDp = ReturnSQLResult $SQLConnection "select p.Name, dp.* from  [dbo].[v_DistributionPoint] dp join SMSPackages p on dp.PackageID = p.PkgID"
    $adv = ReturnSQLResult $SQLConnection "select * from vSMS_Advertisement_List"


    $dp = ReturnSQLResult $SQLConnection "select * from [dbo].[v_DistributionPoints]"
    $dpg = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_DistributionPointGroup]"
    $dpgCol = ReturnSQLResult $SQLConnection "select * from [dbo].[vSMS_DPGroupCollections]"
    $dpgMem = ReturnSQLResult $SQLConnection "select dp.ServerName, dpg.Name
        from [dbo].[v_DPGroupMembers] dpgm join v_DistributionPoints dp on dpgm.DPNALPath = dp.NALPath
           join vSMS_DistributionPointGroup dpg  on dpgm.GroupID = dpg.GroupID"

    $program = ReturnSQLResult $SQLConnection "select * from v_Program"
    $col = ReturnSQLResult $SQLConnection "select * from v_Collection"
    $colDirect = ReturnSQLResult $SQLConnection "select crd.*, c.Name, s.Name0, s.Netbios_Name0, s.Resource_Domain_OR_Workgr0
        from v_CollectionRuleDirect crd join v_Collection c on crd.CollectionID = c.CollectionID join v_R_System s on crd.ResourceID = s.ResourceID"
    $colQuery = ReturnSQLResult $SQLConnection "select crq.*, c.Name from v_CollectionRuleQuery crq join v_Collection c on crq.CollectionID = c.CollectionID"
    $colSettings = ReturnSQLResult $SQLConnection "select * from vSMS_CollectionSettings"
    $svcWindows = ReturnSQLResult $SQLConnection "select c.Name, sw.* from [v_ServiceWindow] sw join v_Collection c on sw.CollectionID = c.CollectionID"

    $supSync = ReturnSQLResult $SQLConnection "select * from v_SoftwareUpdateSource"
    $adForest = ReturnSQLResult $SQLConnection "select * from vActiveDirectoryForests"


    Write-Progress -Activity "Формирование данных..." -Id 1 -Status "Running" -PercentComplete 50
    Write-Host "Формирование данных..."
    $report = New-Object System.Object
    $report | Add-Member -Name "Boundary" -MemberType NoteProperty -Value $boundary
    $report | Add-Member -Name "BoundaryGroup" -MemberType NoteProperty -Value $boundaryGroup
    $report | Add-Member -Name "BoundaryGroupMem" -MemberType NoteProperty -Value $boundaryGroupMem
    $report | Add-Member -Name "BoundarySys" -MemberType NoteProperty -Value $boundaryGroupSys
    $report | Add-Member -Name "Sites" -MemberType NoteProperty -Value $sites
    $report | Add-Member -Name "SubSites" -MemberType NoteProperty -Value $subSites
    $report | Add-Member -Name "SrvComp" -MemberType NoteProperty -Value $srvComp
    $report | Add-Member -Name "SiteDef" -MemberType NoteProperty -Value $siteDef
    $report | Add-Member -Name "SiteDefProp" -MemberType NoteProperty -Value $siteDefProp
    $report | Add-Member -Name "SiteDefPropList" -MemberType NoteProperty -Value $siteDefPropList
    $report | Add-Member -Name "CompProp" -MemberType NoteProperty -Value $CompProp
    $report | Add-Member -Name "CompPropList" -MemberType NoteProperty -Value $CompPropList
    $report | Add-Member -Name "Addr" -MemberType NoteProperty -Value $addr
    $report | Add-Member -Name "AddrProp" -MemberType NoteProperty -Value $addrProp
    $report | Add-Member -Name "AddrPropList" -MemberType NoteProperty -Value $addrPropList
    $report | Add-Member -Name "ClientSettings" -MemberType NoteProperty -Value $ClientSettings
    $report | Add-Member -Name "ClientSettingsProp" -MemberType NoteProperty -Value $ClientSettingsProp
    $report | Add-Member -Name "Sys" -MemberType NoteProperty -Value $sys
    $report | Add-Member -Name "SysProp" -MemberType NoteProperty -Value $sysProp
    $report | Add-Member -Name "SysPropList" -MemberType NoteProperty -Value $sysPropList
    $report | Add-Member -Name "SCProp" -MemberType NoteProperty -Value $scprop
    $report | Add-Member -Name "SCPropList" -MemberType NoteProperty -Value $scpropList
    $report | Add-Member -Name "Packages" -MemberType NoteProperty -Value $packages
    $report | Add-Member -Name "SUPPackages" -MemberType NoteProperty -Value $supckages
    $report | Add-Member -Name "DriverPackages" -MemberType NoteProperty -Value $driverPackages
    $report | Add-Member -Name "ImagePackages" -MemberType NoteProperty -Value $imagePackages
    $report | Add-Member -Name "BootPackages" -MemberType NoteProperty -Value $bootPackages
    $report | Add-Member -Name "OSPackages" -MemberType NoteProperty -Value $osSourcePckg
    $report | Add-Member -Name "PackageDistribution" -MemberType NoteProperty -Value $pkgDp
    $report | Add-Member -Name "Advert" -MemberType NoteProperty -Value $adv
    $report | Add-Member -Name "DP" -MemberType NoteProperty -Value $dp
    $report | Add-Member -Name "DPG" -MemberType NoteProperty -Value $dpg
    $report | Add-Member -Name "DGP2Col" -MemberType NoteProperty -Value $dpgCol
    $report | Add-Member -Name "DPGMembers" -MemberType NoteProperty -Value $dpgMem
    $report | Add-Member -Name "Programs" -MemberType NoteProperty -Value $program
    $report | Add-Member -Name "Collection" -MemberType NoteProperty -Value $col
    $report | Add-Member -Name "CollectionDirectRule" -MemberType NoteProperty -Value $colDirect
    $report | Add-Member -Name "CollectionQueryRule" -MemberType NoteProperty -Value $colQuery
    $report | Add-Member -Name "CollectionSettings" -MemberType NoteProperty -Value $colSettings
    $report | Add-Member -Name "ColSvcWindows" -MemberType NoteProperty -Value $svcWindows
    $report | Add-Member -Name "SUPSync" -MemberType NoteProperty -Value $supSync
    $report | Add-Member -Name "ADForest" -MemberType NoteProperty -Value $adForest

    Write-Progress -Activity "Сохраняем данные ..." -Id 1 -Status "Running" -PercentComplete 99
    $report | Export-Clixml $resultfile -Depth 1
    $xmlstr = Get-Content $resultfile
    $xmlstr = "<?xml version=`"1.0`"?>`n`r<?xml-stylesheet type=`"text/xsl`" href=`"$xslfile`"?>`n`r" + $xmlstr
    Set-Content -Path $resultfile -Encoding UTF8 -Value $xmlstr
    Write-Progress -Activity "Завершено..." -Id 1 -Status "Running" -PercentComplete 100 -Completed
}
finally{
    if ($sqlConnection.State -eq "Open"){
        Write-Host "Close Connection"
        $sqlConnection.Close()
    }
}