ConfigMgr : Query to find SCCM client versions (ConfigMgr 2012)

agent-smith.png

download : SQLQuery-find-agentsversions

outputofquery.png


select sys.Client_Version0, "CM Name"=
case sys.Client_Version0
when '4.00.6487.2000' then 'CM07 SP2 (4.00.6487.2000)'
when '4.00.6487.2188' then 'CM07 R3 (4.00.6487.2188)'
when '4.00.6487.2187' then 'CM07 R3 (4.00.6487.2187)'
when '4.00.6487.2157' then 'CM07 R3 (4.00.6487.2157)'
when '5.00.0000.0000' then 'CM12 RTM (5.00.0000.0000)'
when '5.00.7804.1000' then 'CM12 SP1 (5.00.7804.1000)'
when '5.00.7804.1202' then 'CM12 SP1 CU1 (5.00.7804.1202)'
when '5.00.7804.1300' then 'CM12 SP1 CU2 (5.00.7804.1300)'
when '5.00.7804.1400' then 'CM12 SP1 CU3 (5.00.7804.1400)'
when '5.00.7958.1000' then 'CM12 R2 (5.00.7958.1000)'
when '5.00.7958.1101' then 'CM12 R2 KB 2905002(5.00.7958.1101)'
when '5.00.7958.1203' then 'CM12 R2 CU1 (5.00.7958.1203)'
when '5.00.7958.1303' then 'CM12 R2 CU2 (5.00.7958.1303)'
when '5.00.7958.1401' then 'CM12 R2 CU3 (5.00.7958.1401)'
else 'Others(non-Clients)'
End,count(*) [Total]
from v_R_System sys
where sys.Name0 not like 'unknown' and
sys.Client_Version0 not like '' and sys.Client_Version0 not like '0.0%'
group by sys.Client_Version0
order by Client_Version0

 

Advertisements

ConfigMgr : OSD : How To Create a Bootable USB Windows 7 Build Disk

Windows-10-USB-drive-bootable

Prerequisites

The following are required to build the Windows 7 USB Media:

  • Windows 7 Machine
  • USB Removable Drive

 

Step by Step: How to prepare the USB

The USB device must be prepared prior to generate the stand alone media.

  1. On a Windows 7 PC, Insert the USB device and launch the command line in administrator mode
  2. Enter: “Diskpart” from a command line
  3. In disk part, Enter: “List Disk” – to view the disk number
  4. Select the device with the command “Select Disk 1” and substitute the number 1 with the correct device ID on your system. Make sure you select the correct device as the next step WILL DESTROY ALL DATA on the device selected.
  5. Enter: “clean” in the diskpart command line and hit “enter”
  6. Enter “create partition primary” in the diskpart command line and hit “enter”
  7. Enter “select partition 1” in the diskpart command line and hit “enter”
  8. Enter “format quick fs=ntfs” in the diskpart command line and hit “enter”
  9. Enter “active” in the diskpart command line and hit “enter”
  10. Enter “assign” in the diskpart command line and hit “enter”
  11. Enter “exit” in the diskpart command line and hit “enter”

 

For easy reference please check the below screen shot

dos.png

Now copy the content  of the ISO .

ConfigMgr : SQL Query to find Application and version by location

earth-gps

Download :  SQLQuery-Findmachines-withoutlook-locationspecific

outlook-per-location.png


SELECT distinct
b.Netbios_Name0,
b.User_Name0,
b.ad_site_name0,
a.FileName,
a.FileVersion,
a.FilePath
FROM
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID
WHERE FileName = 'outlook.exe' and AD_Site_Name0 like '%perth%'
ORDER BY
b.Netbios_Name0


			

ConfigMgr : OSD : Build failure Troubleshooting and Logs gathering

win101

During the Build process if you encounter any issues you can perform further investigation with the below mandatory information

  • Machine model
  • Error code with snapshot
  • Error stage with snapshot (may be hiding behind the Task Sequence error window)
  • Single system failure OR multiple systems failures
  • Build Failure Logs (as described below)
  • Mention if any HW component has been replaced on the machine

Troubleshooting Steps

  1. Do not restart the system when the build failure occurs and shows the Task Sequence error
  2. Please note the error code with snapshot
  3. Note the error stage – the stage build got failed (may be behind the Task Sequence error window)
  4. Press F8 key for Command Prompt
  5. Refer the below table OSD Logs Path for logs location based on failure stage
  6. Copy all the log files to the USB drive connected to System
  7. Attach the gathered logs with incident / email to us for investigation

OSD Logs Path

Smsts.log is found in different locations depending on the stage of failure:

Build Failure Stage Log file location
WindowsPE, before HDD format x:\windows\temp\smstslog\smsts.log
WindowsPE, after HDD format x:\smstslog\smsts.log
Windows, SCCM agent not installed c:\_SMSTaskSequence\Logs\Smstslog\smsts.log
Windows x64, SCCM agent installed c:\windows\sysWOW64\ccm\logs\Smstslog\smsts.log
Task Sequence completed x64 c:\windows\sysWOW64\ccm\logs\smsts.log

Network Setup and Domain Join

For issues related to Build failure at domain joining step please gather the below logs

% SystemRoot %\debug\netsetup.log

ConfigMgr : Discovery Methods in System Center Configuration Manager 1706 CB

In Today’s tutorial, we learn about the discovery methods in System center configuration manager.

We have below discovery methods in System Center Configuration Manager.
– Active Directory Forest Discovery
– Active Directory Group Discovery
– Active Directory System Discovery
– Active Directory User Discovery
– Heartbeat Discovery
– Network Discovery

Active Directory Forest Discovery

By enabling Active Directory Forest Discovery, we can find resources from AD forests. When you configure the forests to discover AD sites and subnets, configuration manager can automatically create boundaries from this information.

Active Directory Group Discovery
By enabling Active Directory Group Discovery, we can discover the resources using AD group membership of computers and users.

Active Directory System Discovery
By enabling Active Directory System Discovery, we can find the computers in Active Directory Domain Services.

Active Directory User Discovery
By enabling Active Directory User Discovery, we can find the user accounts in AD domain Services.

Heartbeat Discovery
By configuring these settings, we can configure interval for configuration manager clients to periodically send a discovery data record to the site.

Network Discovery
By configuring these settings, we can configure settings and polling intervals to discover resources on the network like subnets, SNMP-enabled.

We can enable all these discovery methods by following below steps.
Login to Configuration manager console, Administration, expand hierarchy configuration and click on discovery methods, in results pane you will find all the discovery methods as below. To enable go to properties of each discovery methods.DiscoveryMethods
Thank you for reading ☺
Keep Learning ☺

Power BI : ConfigMgr : Indepth Hardware Inventory Report – (Free Pbix, query and guide)

the-main-dashboard

This post will guide you through connecting Power BI Desktop to your SCCM Infra and pulling out awesome indepth hardware info based off a SQL Query.

And for advanced users – skip the rest and Download PBIX

  1. The SQL Query – [Download SQL Query]

    The Query brings in Machine Name, AD site, User Name, Top User, OS, Service Pack, Manufacturer, Model, Serial Number, BIOS Date, BIOS Version, Managed Date, Memory, Memory Slots, Type of Proc, Disk Size, PC Type.

    ———————————————————————————————–
    SELECT DISTINCT
    sys.Name0 ‘Machine’,
    sys.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END as TopUser,
    REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) OS,
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
    CS.Manufacturer0 ‘Manufacturer’,
    CS.Model0 Model,
    BIOS.SerialNumber0 ‘Serial Number’,
    BIOS.ReleaseDate0 as BIOSDate,
    BIOS.SMBIOSBIOSVersion0 as BIOSVersion,
    (SELECT CONVERT(DATE,sys.Creation_Date0)) ‘Managed Date’,
    SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
    COUNT(RAM.ResourceID) ‘# Memory Slots’,
    REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
    SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
    CASE SE.ChassisTypes0
    when ‘1’ then ‘Other’
    when ‘2’ then ‘Unknown’
    when ‘3’ then ‘Desktop’
    when ‘4’ then ‘Low Profile Desktop’
    when ‘5’ then ‘Pizza Box’
    when ‘6’ then ‘Mini Tower’
    when ‘7’ then ‘Tower’
    when ‘8’ then ‘Portable’
    when ‘9’ then ‘Laptop’
    when ’10’ then ‘Notebook’
    when ’11’ then ‘Hand Held’
    when ’12’ then ‘Docking Station’
    when ’13’ then ‘All in One’
    when ’14’ then ‘Sub Notebook’
    when ’15’ then ‘Space-Saving’
    when ’16’ then ‘Lunch Box’
    when ’17’ then ‘Main System Chassis’
    when ’18’ then ‘Expansion Chassis’
    when ’19’ then ‘SubChassis’
    when ’20’ then ‘Bus Expansion Chassis’
    when ’21’ then ‘Peripheral Chassis’
    when ’22’ then ‘Storage Chassis’
    when ’23’ then ‘Rack Mount Chassis’
    when ’24’ then ‘Sealed-Case PC’
    else ‘Undefinded’
    END AS ‘PC Type’
    FROM
    v_R_System SYS
    INNER JOIN (
    SELECT
    Name0,
    MAX(Creation_Date0) AS Creation_Date
    FROM
    dbo.v_R_System
    GROUP BY
    Name0
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
    LEFT JOIN v_GS_COMPUTER_SYSTEM CS
    ON sys.ResourceID=cs.ResourceID
    LEFT JOIN v_GS_PC_BIOS BIOS
    ON sys.ResourceID=bios.ResourceID
    LEFT JOIN (
    SELECT
    A.ResourceID,
    MAX(A.[InstallDate0]) AS [InstallDate0]
    FROM
    v_GS_OPERATING_SYSTEM A
    GROUP BY
    A.ResourceID
    ) AS X
    ON sys.ResourceID = X.ResourceID
    INNER JOIN v_GS_OPERATING_SYSTEM OS
    ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
    LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
    ON sys.ResourceID=ram.ResourceID
    LEFT OUTER join dbo.v_GS_LOGICAL_DISK D
    ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
    LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
    ON SYS.ResourceID = U.ResourceID
    LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE on SYS.ResourceID = SE.ResourceID
    LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En on SYS.ResourceID = En.ResourceID
    GROUP BY
    sys.Creation_Date0, sys.Name0,
    sys.AD_Site_Name0 , CS.UserName0 ,REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) , REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ,
    CS.Manufacturer0 , CS.Model0 ,BIOS.SerialNumber0 , REPLACE (cs.SystemType0,’-based PC’,”),
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END,
    CASE SE.ChassisTypes0
    when ‘1’ then ‘Other’
    when ‘2’ then ‘Unknown’
    when ‘3’ then ‘Desktop’
    when ‘4’ then ‘Low Profile Desktop’
    when ‘5’ then ‘Pizza Box’
    when ‘6’ then ‘Mini Tower’
    when ‘7’ then ‘Tower’
    when ‘8’ then ‘Portable’
    when ‘9’ then ‘Laptop’
    when ’10’ then ‘Notebook’
    when ’11’ then ‘Hand Held’
    when ’12’ then ‘Docking Station’
    when ’13’ then ‘All in One’
    when ’14’ then ‘Sub Notebook’
    when ’15’ then ‘Space-Saving’
    when ’16’ then ‘Lunch Box’
    when ’17’ then ‘Main System Chassis’
    when ’18’ then ‘Expansion Chassis’
    when ’19’ then ‘SubChassis’
    when ’20’ then ‘Bus Expansion Chassis’
    when ’21’ then ‘Peripheral Chassis’
    when ’22’ then ‘Storage Chassis’
    when ’23’ then ‘Rack Mount Chassis’
    when ’24’ then ‘Sealed-Case PC’
    else ‘Undefinded’
    END ,

    BIOS.ReleaseDate0 ,
    BIOS.SMBIOSBIOSVersion0

    ORDER BY sys.Name0

    ———————————————————————————————–

  2. 2. The Query Output – sample

query output

This should give an idea of what all data can be played with for reference.

3. Download the PBIX file.

4. Dashboard when first launched.

Dont panic – this is just the clean slate view with no data.

clean-dashboard

5. Reconfigure the SQL Server

Home > Edit Queries.

step1

Query Editor Window > Home > Data Source Settings

step2

On Data Source Setting Window > Change Source

step3

Enter the Server and Database details – then Hit ‘ok

step4

Voila ! you are done ! You dynamic Power BI Dashboard is available. Use the Data Slicers on top or interactive donut pie in the center to filter and analyze – then export the end results to csv.the-main-dashboard
Go ahead put in your feedback / comments once you give this a try.

SCCM SQL Query : SQL Query for Locations and Boundaries

Download SQL Query
 
The Query :

select sys1.Name, sys1.DefaultSiteCode,

(select SUBSTRING(sys2.ServerNALPath, CHARINDEX(‘\\’, sys2.ServerNALPath) + 2,

CHARINDEX(‘”]’, sys2.ServerNALPath) – CHARINDEX(‘\\’, sys2.ServerNALPath) – 3 ) +

CASE sys2.Flags WHEN ‘1’ Then ‘ (Slow)’ WHEN ‘0’ THEN ” END + ‘; ‘ as ‘data()’

from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID

for XML path(”)) as ‘Site System’,

(select sys4.Value + ‘; ‘ as ‘data()’ from vSMS_BoundaryGroupMembers as sys3

left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID

for XML path(”)) as ‘Boundary’, sys1.ModifiedOn, sys1.ModifiedBy

from vSMS_BoundaryGroup as sys1


Output :

Name
DefaultSiteCode
Site System
Boundary
ModifiedOn
ModifiedBy
Bahrain
EME
bahrain.contoso.com;
Bahrain – Wifi
26:20.0
contoso\admin1
India
APA
india.contoso.com;
India
06:35.0
contoso\admin2
London
EME
london.contoso.com;
London Extranet
52:51.0
contoso\admin3
New York
AME
newyork.contoso.com;
New York Wifi
55:54.0
contoso\admin4
Lima
SME
lima.contoso.com;
Lima Extranet
56:29.0
contoso\admin5
Moscow
EME
moscow.contoso.com;
Moscow
55:47.0
contoso\admin6

SCCM SQL Query : Find SCCM Client Health

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 then ‘Active’
end as ‘IsActiveStatusMessages’,
summ.LastOnline,
summ.LastDDR,
summ.LastHW,
summ.LastSW,
summ.LastPolicyRequest,
summ.LastStatusMessage,
summ.LastHealthEvaluation,
case when LastHealthEvaluationResult = 1 then ‘Not Yet Evaluated’
when LastHealthEvaluationResult = 2 then ‘Not Applicable’
when LastHealthEvaluationResult = 3 then ‘Evaluation Failed’
when LastHealthEvaluationResult = 4 then ‘Evaluated Remediated Failed’
when LastHealthEvaluationResult = 5 then ‘Not Evaluated Dependency Failed’
when LastHealthEvaluationResult = 6 then ‘Evaluated Remediated Succeeded’
when LastHealthEvaluationResult = 7 then ‘Evaluation Succeeded’
end as ‘Last Health Evaluation Result’,
case when LastEvaluationHealthy = 1 then ‘Pass’
when LastEvaluationHealthy = 2 then ‘Fail’
when LastEvaluationHealthy = 3 then ‘Unknown’
end as ‘Last Evaluation Healthy’,
case when summ.ClientRemediationSuccess = 1 then ‘Pass’
when summ.ClientRemediationSuccess = 2 then ‘Fail’
else ”
end as ‘ClientRemediationSuccess’,
summ.ExpectedNextPolicyRequest
from v_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
order by sys.Name0

SCCM SQL Query : Find Lync Versions

select  distinct v_R_System.User_Name0,v_r_system.Name0,v_R_System.Active0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.TimeStamp,v_Add_Remove_Programs.InstallDate0,v_Add_Remove_Programs.Version0
from v_Add_Remove_Programs
inner join v_R_System on v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID
where v_Add_Remove_Programs.DisplayName0 like ‘Microsoft Lync 2010’