Show Keyboard Layouts by AD Site

Version: SCCM 2012 SP1 CU2

Recently, I had to determine keyboard layouts through out the corporate organization, globally. This was information needed for OS deployments and configuring the proper keyboard layout in answer files. I was trying to understand which keyboard layouts were typical for given branches (or AD sites). So, the following were steps taken to create this report.

  1. Enable Hardware Inventory for keyboard layouts. The keyboard layout is not part of the default hardware inventory settings. Fortunately, it’s already part of the local WMI repository on client machines. It’s simply a matter of enabling hardware inventory in the console.
  2. Import into SQL a mapping of keyboard layouts with regional descriptions.
  3. Create SQL report.

Step 1: Enable Hardware Inventory for keyboard layouts

  • From the CM12 Console, go to Administration
  • Click on Client Settings
  • Right-click on Default Client Settings and select Properties
  • Click on Hardware Inventory
  • Click on Set Classes …
  • Scroll down to Keyboard (Win32_Keyboard) and expand
  • Place a check on Device ID and Layout (see below image)

When clients perform the next hardware inventory cycle, they will report this data up to the primary site. A new SQL view will be created called “v_GS_KEYBOARD_DEVICE” containing this data.

report_keyboard1

Step 2: Import into SQL a mapping of keyboard layouts with regional descriptions.

This step is optional. However, it makes it easier to understand the type of keyboard layout since clients submit numerical keyboard codes. Importing this table will help to provide a more meaningful description of the layout.

  • Download the following zip file: keyboards.zip
  • Unzip the content keyboards.xlsx
  • Import content to the CM database into a custom table called Custom_KeyboardLayouts.
    • Using SQL Management Studio, right-click on your CM database (typically “CM_<site code>”)
    • Select “Task” -> “Import Data”
    • Click Next at the Import Wizard
    • At the “Choose a Data Source”, select “Microsoft Excel” for data source, browse to Excel file, set Excel version to “Microsoft Excel 2007”, ensure check is placed on “First Row has Column Names”, then click Next.
    • At the “Choose a Destination”, verify that the Database is set to the CM database (typically “CM_<site code>”), click Next.
    • At the “Specify Table Copy or Query”, choose Table Copy and click Next.
    • At the “Select Source Tables or Views”, click Next.
    • At the “Save and Run Package”, verify “Run Immediately” is set, click Next.
    • At the “Complete the Wizard”, click Finish.
    • Make certain your table has appropriate select permission. My CM database requires that the smsschm_users database role have “Select” permission.

Step 3: Create SQL report

Select sys.AD_Site_Name0 as 'AD Site', kbdl.Description, kbdl.Layout,  COUNT (distinct Sys.resourceid) as 'Count' from v_R_System sys
join v_GS_KEYBOARD_DEVICE kbd on sys.resourceid=kbd.resourceid
join Custom_KeyboardLayouts$ kbdl on kbd.Layout0=kbdl.Layout
Group By sys.AD_Site_Name0, kbdl.Description, kbdl.Layout
Order by sys.AD_Site_Name0

Sample Output:

report_keyboard2