COMPUTER INFORMATION
DECLARE @ComputerName nvarchar(30)
SET @ComputerName = ''
SELECT Distinct sys.Name0 AS 'Name'
,sys.Full_Domain_Name0 AS 'Domain'
,sys.User_Name0 AS 'Last Logon User'
,usr2.Mail0 AS 'Last Logon User mail'
,CASE WHEN usr.User_Name0 is NULL THEN sys.managedBy0 ELSE usr.Name0 END AS 'Managedby'
,usr.Mail0 AS 'Email (managedby)'
,CONVERT(char(10), sys.Last_Logon_Timestamp0,126) AS 'Last Logon'
,cs.Manufacturer0 AS 'Manufacturer'
,CASE
WHEN cs.Model0 = '20J9S0DL16' THEN 'ThinkPad L570'
WHEN cs.Model0 = '20J9S0DL01' THEN 'ThinkPad L570'
WHEN cs.Model0 = '20HJS0D21F' THEN 'ThinkPad P51'
WHEN cs.Model0 = '20HJS0D201' THEN 'ThinkPad P51'
WHEN cs.Model0 = '20HMS17T1Q' THEN 'ThinkPad X270'
WHEN cs.Model0 = '20HMS17T01' THEN 'ThinkPad X270'
WHEN cs.Model0 = '30BGS01B0U' THEN 'ThinkStation P320'
WHEN cs.Model0 = '20JJS13601' THEN 'ThinkPad Yoga 370'
ELSE cs.Model0 END AS 'Model'
,CASE
WHEN se.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN se.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop' END AS 'Type'
,prc.Name0 AS 'Processor'
,prc.NumberofCores0 AS 'Cores'
,os.Caption0 AS 'OS'
,CASE os.Version0
WHEN '10.0.10240' THEN '1507'
WHEN '10.0.10586' THEN '1511'
WHEN '10.0.14393' THEN '1607'
WHEN '10.0.15063' THEN '1703'
WHEN '10.0.16299' THEN '1709'
WHEN '10.0.17134' THEN '1803'
WHEN '10.0.17763' THEN '1809'
WHEN '10.0.18317' THEN '1903'
ELSE os.Version0 END AS 'OS Version'
,CASE os.OSLanguage0
WHEN 1033 THEN 'English'
WHEN 1031 THEN 'German'
ELSE 'Other'
END AS 'OS Language'
,CASE WHEN sys.active0 = 1 THEN 'Active'
ELSE 'Not Active' END AS 'Status'
,ch.ClientStateDescription AS 'Client State'
,CONVERT(char(10), cds.DeploymentEndTime,126) AS 'Client Install Date'
--,ch.LastMPServerName AS 'Management Point'
,CONVERT(char(10), SWSCAN.LastScanDate,126) AS 'SW Scan'
,CONVERT(char(10), HWSCAN.LastHWScan,126) AS 'HW Scan'
,DATEDIFF(day, ch.LastDDR, GETDATE()) AS 'Days Last Active'
FROM v_R_System AS sys
left JOIN v_GS_COMPUTER_SYSTEM as cs ON cs.ResourceID = sys.resourceID
left JOIN v_GS_SYSTEM_ENCLOSURE as se ON se.resourceID = sys.resourceID
left JOIN v_GS_OPERATING_SYSTEM as os ON os.ResourceID = sys.ResourceID
LEFT JOIN v_GS_LastSoftwareScan AS SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS AS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
LEFT JOIN v_CH_ClientSummary AS ch ON ch.ResourceID = sys.ResourceID
LEFT JOIN v_RA_System_MACAddresses AS mac ON mac.ResourceID = sys.ResourceID
LEFT JOIN v_GS_PROCESSOR AS prc ON prc.ResourceID = sys.ResourceID and prc.NumberOfCores0 is not null
LEFT JOIN (select sys.ResourceID, sys.Netbios_Name0,
(select top 1 ou2.System_OU_Name0 from v_RA_System_SystemOUName ou2
where ou.ResourceID = ou2.ResourceID and LEN(ou2.System_OU_Name0) = MAX(LEN(ou.System_OU_Name0))) AS OU
from v_R_System_Valid sys
inner join v_RA_System_SystemOUName ou on sys.ResourceID = ou.ResourceID
group by sys.Netbios_Name0, ou.ResourceID, sys.ResourceID) AS OU ON OU.ResourceID = sys.ResourceID
LEFT JOIN v_ClientDeploymentState as cds on cds.SMSID = sys.SMS_Unique_Identifier0
LEFT JOIN v_r_user AS usr on usr.Distinguished_Name0 = sys.managedBy0
LEFT JOIN v_r_user AS usr2 on usr2.User_Name0 = sys.User_Name0
WHERE sys.name0 like '%' + @ComputerName + '%'
ORDER BY sys.Name0
*************************************************************
SECURITY GROUP MEMBERSHIP
SELECT system_group_name0 AS 'Security Group'
,sys.Name0 AS 'Machine'
FROM v_R_System AS sys
JOIN v_RA_System_SystemGroupName AS sgn ON sgn.ResourceID = sys.ResourceID
WHERE system_group_name0 LIKE '%SecurtiyGroupName%'
ORDER BY Name0
*************************************************************
BOUNDARY GROUP MEMBERS
SELECT GroupName.Name AS 'Boundary Group'
,count(ip_subnets0) AS 'Machine Count'
FROM v_RA_System_IPSubnets
LEFT JOIN vSMS_Boundary AS bondary ON v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
LEFT JOIN vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID = GroupMembers.BoundaryID
LEFT JOIN vSMS_BoundaryGroup AS GroupName ON GroupMembers.GroupID = GroupName.GroupID
GROUP BY GroupName.Name
ORDER BY 'Machine Count' DESC
*************************************************************
COLLECTION INFORMATION
DECLARE @CollectionID nvarchar(30)
SET @CollectionID = ''
SELECT col.SiteID AS 'Collection ID'
,col.CollectionName AS 'Collection Name'
,col.MemberCount AS 'Member Count'
,col.IncludeExcludeCollectionsCount AS 'Collection Members'
,SUM(CASE WHEN cr.RuleType = 1 THEN 1 ELSE 0 END) AS 'Direct Members'
,col.LimitToCollectionID AS 'Limited to Collection ID'
,col.LimitToCollectionName AS 'Limited to Collection Name'
,col.ObjectPath AS 'Console Path'
FROM vCollections AS col
JOIN Collection_Rules AS cr ON cr.CollectionID = col.CollectionID
WHERE col.SiteID = @CollectionID
GROUP BY col.SiteID
,col.CollectionName
,col.MemberCount
,col.IncludeExcludeCollectionsCount
,col.LimitToCollectionID
,col.LimitToCollectionName
,col.ObjectPath
SELECT col.SiteID AS 'Collection ID'
,col.CollectionName AS 'Collection Name'
,CASE RuleType
WHEN 1 THEN 'Direct Rule'
WHEN 2 THEN 'Query Rule'
WHEN 3 THEN 'Include Collection'
WHEN 4 THEN 'Exclude Collection'
ELSE 'Device Rule'
END AS 'Rule Type'
,cr.QueryName AS 'Member Name'
,CASE WHEN RuleType = 1 THEN CAST(cr.MachineID AS nvarchar(30) )
WHEN RuleType = 2 THEN 'n/a'
WHEN Ruletype in (3,4) THEN col2.SiteID END AS 'Member ID'
FROM vCollections AS col
JOIN Collection_Rules AS cr ON cr.CollectionID = col.CollectionID
LEFT JOIN (SELECT SiteID, CollectionName FROM vCollections) AS col2 ON col2.CollectionName = cr.QueryName
WHERE col.SiteID = @CollectionID
ORDER BY cr.RuleType DESC, cr.QueryName
*************************************************************
COLLECTION MEMBERS
DECLARE @CollectionID nvarchar(30)
SET @CollectionID = ''
SELECT col.CollectionID AS 'Collection ID'
,col.CollectionName AS 'Collection Name'
,sys.Name0 AS 'Name'
,sys.Full_Domain_Name0 AS 'Domain'
,sys.User_Name0 AS 'Last Logon User'
,sys.AD_Site_Name0
,cs.Manufacturer0 AS 'Manufacturer'
,cs.Model0 AS 'Model'
,os.Caption0 AS 'OS'
,CASE WHEN sys.active0 = 1 THEN 'Active'
ELSE 'Not Active' END AS 'Status'
,ch.ClientStateDescription AS 'Client State'
--,ch.LastMPServerName AS 'Management Point'
,SWSCAN.LastScanDate
,HWSCAN.LastHWScan
FROM v_FullCollectionMembership AS fcm
JOIN vCollections AS col ON fcm.CollectionID = col.SiteID
JOIN v_R_System AS sys ON fcm.resourceID = sys.ResourceID
left JOIN v_GS_COMPUTER_SYSTEM as cs ON cs.ResourceID = sys.resourceID
left JOIN v_GS_OPERATING_SYSTEM as os ON os.ResourceID = sys.ResourceID
LEFT JOIN v_GS_LastSoftwareScan AS SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS AS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
LEFT JOIN v_CH_ClientSummary AS ch ON ch.ResourceID = sys.ResourceID
WHERE fcm.CollectionID = @CollectionID
*************************************************************
COLLECTION SCHEDULE TIMES
SELECT CG.CollectionName AS 'Collection Name'
,CG.SITEID AS 'Collection ID'
,CASE VC.CollectionType
WHEN 0 THEN 'Other'
WHEN 1 THEN 'User'
WHEN 2 THEN 'Device'
ELSE 'Unknown' END AS 'Collection Type'
,vc.ObjectPath AS 'Path'
,CASE
WHEN CG.Schedule like '%0102000%' THEN 'Every 1 minute'
WHEN CG.Schedule like '%010A000%' THEN 'Every 5 mins'
WHEN CG.Schedule like '%0114000%' THEN 'Every 10 mins'
WHEN CG.Schedule like '%011E000%' THEN 'Every 15 mins'
WHEN CG.Schedule like '%0128000%' THEN 'Every 20 mins'
WHEN CG.Schedule like '%0132000%' THEN 'Every 25 mins'
WHEN CG.Schedule like '%013C000%' THEN 'Every 30 mins'
WHEN CG.Schedule like '%0150000%' THEN 'Every 40 mins'
WHEN CG.Schedule like '%015A000%' THEN 'Every 45 mins'
WHEN CG.Schedule like '%0100100%' THEN 'Every 1 hour'
WHEN CG.Schedule like '%0100200%' THEN 'Every 2 hours'
WHEN CG.Schedule like '%0100300%' THEN 'Every 3 hours'
WHEN CG.Schedule like '%0100400%' THEN 'Every 4 hours'
WHEN CG.Schedule like '%0100500%' THEN 'Every 5 hours'
WHEN CG.Schedule like '%0100600%' THEN 'Every 6 hours'
WHEN CG.Schedule like '%0100700%' THEN 'Every 7 hours'
WHEN CG.Schedule like '%0100B00%' THEN 'Every 11 Hours'
WHEN CG.Schedule like '%0100C00%' THEN 'Every 12 Hours'
WHEN CG.Schedule like '%0101000%' THEN 'Every 16 Hours'
WHEN CG.Schedule like '%0100008%' THEN 'Every 1 days'
WHEN CG.Schedule like '%0100010%' THEN 'Every 2 days'
WHEN CG.Schedule like '%0100028%' THEN 'Every 5 days'
WHEN CG.Schedule like '%0100038%' THEN 'Every 7 Days'
WHEN CG.Schedule like '%0192000%' THEN '1 week'
WHEN CG.Schedule like '80000%' THEN 'Update Once'
WHEN CG.SChedule = '' THEN 'Manual'
ELSE 'Unknown' END AS 'Update Schedule'
,CASE VC.RefreshType
WHEN 1 THEN 'Manual'
WHEN 2 THEN 'Scheduled'
WHEN 4 THEN 'Incremental'
WHEN 6 THEN 'Scheduled and Incremental'
ELSE 'Unknown' END AS 'RefreshType'
,VC.MemberCount AS 'Member Count'
,(SELECT COUNT(CollectionID) FROM v_CollectionRuleQuery CRQ WHERE CRQ.CollectionID = VC.SiteID) AS 'RuleQueryCount'
,(SELECT COUNT(CollectionID) FROM v_CollectionRuleDirect CRD WHERE CRD.CollectionID = VC.SiteID) AS 'RuleDirectCount'
FROM collections_g AS CG
LEFT JOIN v_collections AS VC on VC.SiteID = CG.SiteID
ORDER BY CG.Schedule DESC
*************************************************************
