Count Software by Prompting for Publisher Name

Version: SCCM 2012 SP1 CU2 There may be times when you are asked to create a static report to count software from a specific publisher on workstations, servers, or both. You can create a dynamic report that will accept as input the publisher name. The following are SQL query examples to display software by publisher using wildcards if necessary. If…

Install Domain Controller on Windows Server 2012 R2

This post provides an overview to install a new domain controller on Windows Server 2012 R2. Please follow the pre-steps before beginning. Pre-Steps: Administrator account should have a strong password. Windows has been fully patched with software updates. Rename your server to a permanent name. When you install Windows Server, a random name is assigned. Assign…

Show Membership for a Collection

Version: SCCM 2012 SP1 CU2 This query will generate a list of members (device machines) for a given collection ID. If you are running this in SQL Management Studio, either declare and set the @variable or you can put the collection ID directly in the where section. If you will use SSRS, then this will serve…

Show Collections with Maintenance Windows

Version: SCCM 2012 SP1 CU2 This will generate a list of all collections with maintenance windows. The description field contains the maintenance schedule. The duration is measured in minutes. select SW.CollectionID, Coll.Name as ‘Collection Name’, SW.Description, SW.Duration as ‘Duration Mins’, Coll.MemberCount from v_ServiceWindow SW join v_Collection Coll on SW.CollectionID=Coll.CollectionID Sample:

List Hardware Info for Dell and Lenovo

This is a short Powershell script to display hardware information about Dell and Lenovo models. When creating scripts to gather information about Lenovo systems, please notice that the descriptive model information is stored in a different WMI class. Typically, OEM vendors store descriptive model information under the Win32_ComputerSystem class. With Lenovo, the descriptive model (e.g. ThinkPad X1 Carbon) is stored…

Search for Column Name in Tables or Views

There be times when you are trying to find information in SQL, but not knowing where to find relevant data makes things even more challenging. I some times use the filter option in SQL Management Studio to narrow down the name of tables or views. However, it may also be useful to display all tables or views that…

Show Drivers with Source Path

Version: SCCM 2012 SP1 CU2 This query will show drivers located in the CM12 console under Software Library -> Operating Systems -> Drivers. It will display driver names, source path, driver class, driver version, etc. SELECT CIDM.ManufacturerName,CIDM.ModelName, CICI.ContentSourcePath, CIDCI.DriverDate, CIDCI.DriverClass, CIDCI.DriverVersion, CIDCI.DriverProvider FROM v_CI_DriverModels CIDM join vCI_ConfigurationItems CICI on CIDM.CI_UniqueID=CICI.CI_UniqueID join v_CI_DriversCIs CIDCI on CIDM.CI_UniqueID=CIDCI.CI_UniqueID…