forked from Skatterbrainz/sccm
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Get-CMDeviceInfo.ps1
89 lines (88 loc) · 3.53 KB
/
Get-CMDeviceInfo.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
#requires -version 3.0
<#
.DESCRIPTION
Return query results for specific devices within ConfigMgr SQL database
.PARAMETER ServerName
ConfigMgr SQL Server hostname
.PARAMETER SiteCode
ConfigMgr site code
.PARAMETER ComputerNames
Names of computers to query information about
.EXAMPLE
$data = .\Get-CMDeviceInfo.ps1 -ServerName "cm01.contoso.local" -SiteCode "P01" -ComputerNames "DT123","DT456"
.NOTES
1.0.1 - DS - Initial release
1.0.2 - DS - fixed SQL query to correct outer join issue
#>
[CmdletBinding()]
param (
[parameter(Mandatory=$True, HelpMessage="ConfigMgr SQL Server host name")]
[ValidateNotNullOrEmpty()]
[string] $ServerName,
[parameter(Mandatory=$True, HelpMessage="ConfigMgr site code")]
[ValidateNotNullOrEmpty()]
[string] $SiteCode,
[parameter(Mandatory=$True, HelpMessage="Computer Names to query")]
[ValidateNotNullOrEmpty()]
[string[]] $ComputerNames
)
Write-Verbose "----------------------------------------------"
if ($ComputerNames.Count -gt 1) {
Write-Verbose "(Get-CmDeviceInfo - $($ComputerNames.Count) names)"
}
else {
Write-Verbose "(Get-CmDeviceInfo - $ComputerNames)"
}
$DatabaseName = "CM_$SiteCode"
Write-Verbose "database name is $DatabaseName"
$queryBase = @"
SELECT DISTINCT
dbo.v_R_System.Name0 AS [Name],
dbo.v_R_System.ResourceID,
dbo.v_R_System.AD_Site_Name0 AS [ADSite],
dbo.vWorkstationStatus.ClientVersion,
dbo.vWorkstationStatus.LastHardwareScan AS [LastHwScan],
DATEDIFF(dd,dbo.vWorkstationStatus.LastHardwareScan,GETDATE()) AS InvAge,
dbo.vWorkstationStatus.LastPolicyRequest,
dbo.vWorkstationStatus.LastHealthEvaluationResult AS [LastHealthEval],
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OperatingSystem],
CASE
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 10586) THEN '1511'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 14393) THEN '1607'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 15063) THEN '1703'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 16299) THEN '1709'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 17134) THEN '1803'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 17604) THEN '1809'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 7601) THEN 'SP1'
WHEN (dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 = 9600) THEN 'RTM'
ELSE dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 END AS OsBuild,
dbo.vWorkstationStatus.LastDDR,
dbo.vWorkstationStatus.SystemType,
CASE
WHEN (dbo.vWorkstationStatus.IsVirtualMachine = 1) THEN 'Y'
ELSE 'N' END AS IsVM,
dbo.vWorkstationStatus.UserName,
dbo.vWorkstationStatus.UserDomain,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [SerialNumber]
FROM
dbo.v_R_System
LEFT OUTER JOIN
dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
LEFT OUTER JOIN
dbo.vWorkstationStatus ON dbo.v_R_System.ResourceID = dbo.vWorkstationStatus.ResourceID
"@
$query = $queryBase
if ($ComputerNames.Count -gt 1) {
$complist = ($ComputerNames | %{"'$_'"}) -join ','
$query += " WHERE (dbo.v_R_System.Name0 IN ($complist)) ORDER BY dbo.v_R_System.Name0"
}
else {
$query += " WHERE (dbo.v_R_System.Name0 = '$ComputerNames')"
}
#Write-Verbose $query
.\tools\Get-CMSQLQueryData.ps1 -Query $query -SQLServerName $ServerName -SiteCode $SiteCode