SCCM SQL Query : Get Machine and User Information From A Specified Collection

By San -
Select
SD.Name0 ‘Machine Name’,
SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’,
SD.User_Name0 ‘Login ID’,
SD.User_Domain0 ‘Account Domain’,
USR.Full_User_Name0 ‘Full Name’,
PCB.SerialNumber0 ‘Serial Number’,
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SAS.SMS_Assigned_Sites0 ‘Assigned Site Code’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID
Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID
Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID

Where COL.Name = ‘All Systems’

Comments

Post a Comment

Popular posts from this blog

Power BI : Microsoft Office Telemetry Dashboard

By San -
Image
Microsoft Office's Telemetry Dashboard is an essential add-on for giving your environment an Office Health Index - where you have it, licensing, upgrade, tracking issues, AppCompat etc. The insight telemetry provide is priceless and yet free. Here the data sources are both the telemetry database, telemetry excel dashboard, and the SCCM database. Installation + Configuration of Telemetry : https://technet.microsoft.com/en-us/library/jj863580.aspx?f=255&MSPPError=-2147217396 Configure and deploy the dashboard : https://technet.microsoft.com/en-us/library/jj853312.aspx?f=255&MSPPError=-2147217396 want the  pbix  file ?  share this post on  twitter  or  fb  and comment with the link of ur shared post & your email in the comment window below.
Read more

SCCM SQL Query : Find SCCM Client Health

By San -
select sys.Name0 as 'Computer Name', sys.User_Name0 as 'User Name', summ.ClientStateDescription, case when summ.ClientActiveStatus = 0 then 'Inactive' when summ.ClientActiveStatus = 1 then 'Active' end as 'ClientActiveStatus', summ.LastActiveTime, case when summ.IsActiveDDR = 0 then 'Inactive' when summ.IsActiveDDR = 1 then 'Active' end as 'IsActiveDDR', case when summ.IsActiveHW = 0 then 'Inactive' when summ.IsActiveHW = 1 then 'Active' end as 'IsActiveHW', case when summ.IsActiveSW = 0 then 'Inactive' when summ.IsActiveSW = 1 then 'Active' end as 'IsActiveSW', case when summ.ISActivePolicyRequest = 0 then 'Inactive' when summ.ISActivePolicyRequest = 1 then 'Active' end as 'ISActivePolicyRequest', case when summ.IsActiveStatusMessages = 0 then 'Inactive' when summ.IsActiveStatusMessages = 1 the...
Read more