SCCM Report – Count All Computers by Model

How to create a MS Configuration manager report that will Count All Computers for each Model

This guide will show you how to create a report in Microsoft System Center Configuration Manager 2012(R2) / Current branch 1702. In this example we will be creating a report which lists all system models and displays a count of each model.

SCCM 2012 (last checked on build 1702) does not have a prebuilt report that will simply return a list of all the distinct manufacturer/model numbers for all the computers in the environment and a count of each type.

This SCCM report will retrieve all the computer models  in one column and count of each model in the the adjacent column:

SELECT        Model0 AS Model, COUNT(*) AS Count, Domain0

FROM            dbo.v_GS_COMPUTER_SYSTEM

GROUP BY Model0, Domain0

Output of the report:

sccm report Count Computer by Model

If you happen to have Lenovo models in your organisation, you will find it useful to translate the Lenovo’s models to real model names. The following query will do that:

SELECT COUNT(*) No_Of_Items, 

CASE WHEN MODEL0 IN('10AXS2PX00') THEN 'Lenovo M73'

WHEN MODEL0 IN('10ahs00d00') THEN 'Lenovo M83'

WHEN MODEL0 IN('10FCS06W00','10FCS0W500','10FHS00D00','10FHS07Q00','10FHS0AK00') THEN 'Lenovo M900'

WHEN MODEL0 IN('10MKS03H00','10MKS04G00','10MKS04H00') THEN 'Lenovo M910s '

WHEN MODEL0 IN('10A7A00P00','10A7S00P00','10A7A00L00','10A7CTO','10A7S00D00',

'10A7S00S00','10A7S02700','10A7S02800','10A7S02D00','10A8A02H0C',

'10A8S2E100','10A9003PIV','10A9S02X00') THEN 'Lenovo M93p'

WHEN MODEL0 IN('SLIC-BPC') THEN 'HP Compaq Elite 8300 BPC'

WHEN MODEL0 = 'To be filled by O.E.M.' THEN 'WeyTech'

ELSE Model0 END [Model]

FROM v_GS_COMPUTER_SYSTEM


WHERE model0 like '%hp%' or model0 like '%think%' or model0 like '%10%' or model0 like '%O.E.M%' or model0 like '%SLIC-BPC%'

GROUP BY CASE WHEN MODEL0 IN('10AXS2PX00') THEN 'Lenovo M73'

WHEN MODEL0 IN('10ahs00d00') THEN 'Lenovo M83'

WHEN MODEL0 IN('10FCS06W00','10FCS0W500','10FHS00D00','10FHS07Q00','10FHS0AK00') THEN 'Lenovo M900'

WHEN MODEL0 IN('10MKS03H00','10MKS04G00','10MKS04H00') THEN 'Lenovo M910s '

WHEN MODEL0 IN('10A7A00P00','10A7S00P00','10A7A00L00', '10A7CTO','10A7S00D00',

'10A7S00S00','10A7S02700','10A7S02800',  '10A7S02D00','10A8A02H0C',

'10A8S2E100','10A9003PIV','10A9S02X00') THEN 'Lenovo M93p'

WHEN MODEL0 IN('SLIC-BPC') THEN 'HP Compaq Elite 8300 BPC'

WHEN MODEL0 = 'To be filled by O.E.M.' THEN 'WeyTech'

ELSE Model0 END


ORDER BY No_Of_Items DESC, model

 Download the .rdl file for this report from here: All Computers in specific Collection

How to Ping a List of Computers

Many times It happens that we need to check if a list of computers is active, so how do you ping multiple computers at once?

Use this Powershell script to test connectivity to your list of computers. it will produce two lists of Bad (unresponsive) and Good(Responsive) computers.

You need to change “D:\scripts\list.txt” to the path where your saved your computers list file(.txt).

Also, you can un-comment  #Write-Host $name… to view the list of computers with different colors

$Mylist = Get-Content D:\scripts\list.txt #this is where you put your list of computers
Clear-Host
[System.Collections.ArrayList]$GoodArrayList = @()
[System.Collections.ArrayList]$BadArrayList = @()
 

foreach ($name in $Mylist){
  if (Test-Connection -ComputerName $name -Count 1 -ErrorAction SilentlyContinue){
    #Write-Host $name -ForegroundColor Cyan
    $GoodArrayList.Add($name)
  }
 
else{
    #Write-Host $name "down" -ForegroundColor Red
    $BAdArrayList.Add($name)
  }
}
Write-Host -ForegroundColor Cyan "Good Computers :)"
$GoodArrayList
Write-Host -ForegroundColor Red "Bad Computers :("
$BadArrayList

How to run python script on notepad++

How to  Configure Notepad++ to run a python script via python IDLE

If you are learning python and want to use notepad++ as a free as well as simple and easy to use editor, follow these simple steps:

Install python (2.7.x)

First thing: Download the python 2.7.x (current is 2.7.13) windows installer from http://www.python.org/download/ using the default settings. It should install python in the folder: C:\Python27. You can use a different version of python, just substitute out the version number wherever you see 27. Continue reading How to run python script on notepad++

0x8007052e error in task scheduler

0x8007052e error in task scheduler

Symptom: You get 0x8007052e error when trying to run scheduled task from task scheduler.

There can be several causes for this including authentication problem like bad password.

But I did not easily find documented online records for this error being caused by a group policy, so I think this post might help you – system admins.

Continue reading 0x8007052e error in task scheduler

Installing SCOM 2012 agent on a workgroup or DMZ machine using Certificate

How to Install SCOM Agent on an Un-trusted machine

In order for SCOM to communicate with an un-trusted machine like in a WorkGroup / DMZ / different forest, you will have to import a certificate so that SCOM will trust the foreign machine and will be able to communicate with it. The most common example is a SCOM Gateway.
This topic is a bit complicated but if you use a guide like this you should be OK.

I added the files that you will need as well so you dont have to copy-paste it. Additional files: REQconfig.inf certreq

First thing you will need is an inf file that contains the request.

Continue reading Installing SCOM 2012 agent on a workgroup or DMZ machine using Certificate

Memory leak

Memory leak – when the Available MegaBytes (memory\% available Mbytes) for the system has exceeded the threshold, system performance may be significantly diminish, this results in low OS and applications performance. End users will usually complain about slow computer performance and you probably get a help-desk call like: ‘My computer is running slow’, or server is running slow.

Memory leaks can be caused by:

  • Too many applications running simultaneously on the computer.
  • An application may be leaking memory over time.

To view the history for the memory\% available Mbytes, start memory available Mbytes in performance monitor or event task manager\performance.

Continue reading Memory leak

Public Desktop Icons are Deleted After Logon in Windows 7

I think I that a suitable name for  this post might be ‘The case of the missing desktop shortcuts’.  I spent nearly an hour or more resolving the so it will be the right thing to share it with you.

The Problem

In Windows 7,  Users can’t view objects and icons published on “C:\Users\Public\Desktop” .

Users cannot read data from “C:\Users\Public\Desktop”.

You suspect that a GPO – Group policy object, prohibit users from viewing icons on “C:\Users\Public\Desktop” folder.

Continue reading Public Desktop Icons are Deleted After Logon in Windows 7