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 : 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 Guide : Holistic Standardization and Fine Tuning

marketing-analytics

This is one guide that will take your SCCM skills and environment to a whole new level ! This would give you good insight into your infra and also let you understand how the enterprise class Microsoft Suite is behaving under the hood. The guide has also been implemented in several real world production environments and has brought the server systems availability & performance up by 5-10 %.

This guide will also get your environment standardized, organized – get you insights into health on a perspective no tool or query can. Additionally it can be used for expansion / consolidation / migration / upgrades and Configuration Drifts. The logic can also be transmuted to any application which would have heavy configuration and optimization requirements.

PROBLEM STATEMENT

SCCM/ConfigMgr Infrastructure covers the entire globe, there are multiple servers catering each location and they have numerous SCCM installations and their configurations have drifted over the time.

The SCCM configurations are currently too varied and we are not receiving the optimum performance from the servers and also facing issues day to day.

4 areas that are currently drifting or problematic ::

Backup Schedules / Discovery / Maintenance Tasks / Collection (Refresh Schedules & Runtime)

  1. Overlapping actions : At present the SCCM backup will run – discovery also runs at same time along with some maintenance tasks.
  2. Too aggressive : Certain actions or schedules are too aggressive and hamper the server performance.
  3. Drift : Configurations have drifted over time and there is no monitoring or what is the benchmark – to notice the deviation from desired configuration.
  4. Unmanaged Items : There are settings that must have been created for a test – but have been forgotten – collection refresh schedules are high on them.
  5. Reporting : The issues with reporting, AppPortal or Integrated App Portals, weekend deployments are all culminating from poor SCCM scavenging process and overlapping aggressive activities.

Document all your findings in the Holistic Standardization and Fine Tuning.

BEFORE


SCCM Backup Schedules – Current

finetuning1


SCCM Discovery – Current

finetuning2.png


SCCM Maintenance Tasks – Current

finetuning3.png


SCCM Collection Configuration – Current

Use the Powershell scripts attached to find the expensive and lengthy collection refresh cycles.

finetuning4

From the above captures its obvious there is a configuration drift and non-standard modifications which have been made – maybe even for temporary periods – but get forgotten over time. One classic example is application/OS deployments – where ConfigMgr can be ‘tuned up’ to deliver and deploy faster – but this is not required in day to day operations.


Activities Overlap – Current

finetuning5.png


RECOMMENDATION

  1. Run Performance Counters to capture the current server performance – get a baseline of peak and off hours behavior.
  2. Stagger the activities on the server, have them run spaced out and have least overlaps – this will guarantee high server availability.
  3. Space out the backup schedules – discoveries and maintenance tasks.
  4. Have discoveries run on both Central and Primaries.
  5. Change source of deletion – disable on the primaries and only delete on the central = giving single source for deletion giving better reporting and rid of the duplicate deletion action which was leading to inconsistent data.
  6. Post changes run capture new set of Performance Counters to capture the new server performance – this would the delta of improvement and new baseline.

TIMELINE

2-3 months, have four phased changes –

1.Backup [Configuration to be set in 1 go]

2.Discovery [2 Pilot – Rest Production]

3.Maintenance Tasks [Pilot on 2 Primaries and rest in Production]

4.Collections [Pilot on 2 Primaries and rest in Production]

OUTCOME

The standardization sheet would be the template of reference for any deviation from desired configuration.

finetuning6.png

AFTER

SCCM Backup Schedules – Standardized

finetuning7.png


SCCM Discovery – Standardized

finetuning8.png


SCCM Maintenance Tasks – Standardized

finetuning9.png


SCCM Collection Configuration – Standardized

finetunin10.png


Activities Spread Out

finetunin11.png


POST REMEDIATION

4 areas that get corrected ::

Backup Schedules / Discovery / Maintenance Tasks / Collection (Refresh Schedules & Runtime)

  1. Spaced out actions : Actions on the servers will be given enough room for its runtime.
  2. Less strain on server : This activity is aiming to lower the process/disk utilization and give a higher server availability and faster response.
  3. Baselines : Configurations have drifted over time and there is no monitoring or what is the benchmark – to notice the deviation from desired configuration
  4. Control : There are settings that must have been created for a test – but have been forgotten – collection refresh schedules are high on them.
  5. Better Reporting : The issues with reporting, App Portal, weekend deployments are all culminating from poor SCCM scavenging process and overlapping aggressive activities.

The configuration would be the baseline for any new server – this additionally would also assist in fine tuning the migrated SCCM 2012 environment

TIMELINE

Future migration and baseline maintenance, quarterly re-run of the above checks.

OUTCOME

Ease of configuration for the new SCCM environment and upgrades.

BEYOND

SCCM Infra for Future

finetunin12.png


SCCM Troubleshooting : Ironing out Patching for ConfigMgr 2007 (upto 95% compliance)

This guide will take you through troubleshooting guides, tips, tricks and tools to achieve 95%+ compliance in your environment and maximum coverage. The guide assumes the following environmental conditions:

 

  1. Microsoft System Center Configuration Manager 2007 R3
  2. WSUS 3.0 SP2 used as base for Software Updates
  3. Windows Client endpoints
  4. Three tier SCCM hierarchy

 

– Table of Contents –

  1. Server Side 
    • SCCM Side Clean-up 
    • Discovery Configuration 
    • Distribution Point / DP Group 
    • Site Info v/s WSUS Infra 
    • WSUS Sync up to date 
    • Top Down Checks 
    • Autonomous WSUS 
    • Windows Update Agent Version 
    • Windows Update Agent Repair 
  2. Client Side 
  3. Monitoring Tools

 

  •  Server Side
    •  SCCM Side Clean-up
      • Obsolete

Obsolete clients are those that have been replaced by new ones. This usually happens during refresh OS deployments where the hardware stays the same and thus the hardware id is the same but the SMS GUID changes because the OS has been reloaded or the GUID is regenerated for another reason but the hardware remains the same.

Reasons – 
1. Hard disk swapping
2. Renaming machines
3. Reimage OS
4. Reinstalling SMS/SCCM agent on the machines without proper uninstall.

If machines is showing up as Obsolete – that is good to delete, manually or via scheduled task within SCCM

 

  • No Client
    • Identify machines where they show up as no client,
    • If Client = NO and Site Code = blank – this indicate machine is not belonging to a particular boundary and it needs to be defined.
  • Inactive
    • Inactive client s are those that have not been discovered recently by the heartbeat discovery. The definition of recently is defined in the delete task as a number of days. Please note that obsolete client s are also marked inactive.Reasons
      Offline machines
      2. Machines having DNS issue/No name resolution
      3. Machines are in inventory stock

 

Refer the collection created via #WQL1 , machines are good to clean-up if Inactive=YES,

  • Duplicate – these are variants of obsolete, or outcomes of causes as explained in obsolete.

 To find duplicate entries – create a collection with the following WQL query >

Reference : #WQL1

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,
R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join 
SMS_R_System as s1 on s1.ResourceId = r.ResourceId   full join SMS_R_System 
as s2 on s2.Name = s1.Name   where s1.Name = s2.Name 
and s1.ResourceId != s2.ResourceId

Based on the results – machines which are good to be deleted if Obsolete = YES.

  •  Unapproved – These as long as site code and domain are correct – good to be approved
  • Unknown domain
    • Machines reporting in from unknown domain could mean
      • Machine disjoin from current domain and rejoined another
      • Machine from another domain received incorrect SCCM client installation/push
      • Machines from other domain are using incorrect installation switches
  •  Unknown hostname naming format
    • Most multi-national organizations follow standards to maintain machine naming based on region and country
    • APDDSERIALNUMBER = AP – APAC, DD – Desktop
    • NALLSERIALNUMBER = NA – North America, LL – Laptop
    • Any deviation from these naming standards means the machine should not be in SCCM.

 

  • Site to Site Communication

Verify SCCM site communication is healthy.

  • Create test collections/packages on Central Site and then view on the Primary Sites
  • Verify machines reporting in Primary are available on Central – dump ‘All Systems’ from both and do vlookup to find out GAP.
  • Run preinst syncchild SITECODE against any primary not in sync.

 

  •  Discovery Configuration
    • Confirm that the correct OU’s are being queried within Active Directory, any new additions or new OU’s need to be sync’ed with AD team.
    • Heartbeat discovery is enabled and configured for weekly [mild] – daily [aggressive/urgency]

 

  •  Distribution Point / DP Group
    • A good sanity check is to target a test package to all DP groups and in the review window analyse which distribution points did not get selected.
    • Verify the Deployment Packages are targeted to all active distribution points – this helps attain maximum coverage, so machines from every corner of the infrastructure get the necessary patches.
  •  Site Info v/s WSUS Infra

 

For keeping a tidy sync between WSUS and SCCM – depending on your SCCM SUP’s spread – you can compare WSUS downstream servers v/s the built-in report 75 of SCCM.

http://servername/SMSReporting_sitecode/Report.asp?ReportID=75

  •  WSUS Sync up to date

Check last sync between downstream and upstream partners – if there is a gap that needs to be rectified.

  •  Top Down Checks

Start the checks on the WSUS console from top to down – start from Central and then check on the primaries – make sure all servers are reporting in and have latest sync.

  •  Autonomous WSUS

Verify none of the WSUS downstream partners are showing up as Autonomous.

If they are fix with this method

Check site to site replication
Sitectrl - parent settings
Sitectrl file exchange
Pointing to self for updates
Upstream server checkbox
ConfigMgr side configuration flip and flipback

 

  •  Windows Update Agent Version

Windows Update Agent version differences in the environment can cause issues.

Machines with older versions of WUA will not be able to sync latest updates or different products.

Use following SQL Statement to make a custom report >

SELECT v_R_System.Netbios_Name0, v_GS_WINDOWSUPDATEAGENTVERSIO.Version0
FROM v_GS_WINDOWSUPDATEAGENTVERSIO
INNER JOIN v_R_System ON v_GS_WINDOWSUPDATEAGENTVERSIO.ResourceID = 
v_R_System.ResourceID
WHERE v_GS_WINDOWSUPDATEAGENTVERSIO.Version0 <> 'ISNULL'
ORDER BY v_GS_WINDOWSUPDATEAGENTVERSIO.Version0

The result would indicate disparities between the WUA versions in the environment and these can be targeted.

Push the latest WUA to these machines.

 

  1. Make sure kb2734608 is installed on all WSUS servers
  2. Push latest WUA – https://support.microsoft.com/kb/949104?wa=wsignin1.0
  •  Windows Update Agent Repair

Use the WUA from https://support.microsoft.com/kb/949104?wa=wsignin1.0 to repair broken Windows Update Agents on machines – with unique scan errors etc and also helps machines which are unable to update the WUA.

  • WOL – Wake on Lan

Utilize SCCM’s most underrated/underutilized feature – Wake On Lan. This benefits in patching – so that machines can be woken up and then patched and they get auto rebooted as per requirement. This benefits in reaching out to machines in off hours – times when users are not present – great to combine with ‘Maintenance Windows’. This way you will get to set a time for when these machines will receive the patches (offhours) + ability to wake them up for the activity. Reduces load on the network and off hours maintenance is always appreciated.

  • State Message Drop

Evaluations from machines sometimes are not reported in correctly, machines at times have the patches installed but same does not reflect in the compliance reports. This is caused due to drop in state messages from client to server, or from server to server and at times shows up as Enforcement State Unknown.

There is a method to trigger the full state message to be sent from client to server >

 

Option Explicit
On Error Resume Next
Call RefreshServerComplianceState
' WScript.Echo "Finished"
Sub RefreshServerComplianceState()
dim newCCMUpdatesStore
' Create the COM object.
set newCCMUpdatesStore = CreateObject ("Microsoft.CCM.UpdatesStore")
' Refresh the srvr compliance state by running the RefreshServerComplianceState method.
newCCMUpdatesStore.RefreshServerComplianceState
' Output success message.
'wscript.echo "Ran RefreshServerComplianceState."
End Sub

This script can be found on the web in various places and simply makes use of the SCCM SDK to cause the client to resend it’s data to the MP.  It’s a convenient way to force some state messages up so we don’t have to wait on components to insert them.

Typically, a vb script is executed simply by using cscript as show below.  But note that this fails.  I show this to illustrate the problem you might see if trying to run the script yourself.  SCCM is a 32 bit application but, in this case, running on a 64 bit server.  The default version of cscript is the 64 bit version – and generally this works fine with any vbscript.  In this case, however, the call being made requires the 32 bit version of cscript which you must run out of the syswow64 folder as shown in the second example.

cmd1.png

At this point we have to wait for the next state message polling cycle which will cause all state messages to be sent back up.

Our polling cycle has now fired and if we look at the statemessage.log we see that the state information has been pulled, formatted (into XML) and sent to the MP.

 

<![LOG[StateMessage body: <?xml version="1.0" encoding="UTF-16"?> 
 <Report><ReportHeader><Identification><Machine><ClientInstalled>1</ClientInstalled><ClientType>1</ClientType><ClientID>GUID:F1B03089-9EA1-4A64-83DC-5F8E77BDDEB4</ClientID><ClientVersion>4.00.6487.2000</ClientVersion><NetBIOSName>STEVERAC64-2</NetBIOSName><CodePage>437</CodePage><SystemDefaultLCID>1033</SystemDefaultLCID></Machine></Identification><ReportDetails><ReportContent>State Message Data</ReportContent><ReportType>Full</ReportType><Date>20110107184030.662000+000</Date><Version>1.0</Version><Format>1.0</Format></ReportDetails></ReportHeader><ReportBody><StateMessage MessageTime="20110107183046.153000+000" SerialNumber="1431"><Topic ID="21e49ac6-a273-4a61-9794-eb675bc743e5" Type="500" IDType="3"/><State ID="2" Criticality="0"/><UserParameters Flags="0" Count="1"><Param>102</Param></UserParameters></StateMessageserParameters></StateMessage></ReportBody></Report> 
 ]LOG<![LOG[CStateMsgManager::GetSignEncyptMode]LOG]!><time="12:40:31.037+360" date="01-07-2011" component="StateMessage" context="" type="1" thread="3592" file="statemsg.cpp:1820"> 
 <![LOG[Successfully forwarded State Messages to the MP]LOG]!><time="12:40:31.099+360" date="01-07-2011" component="StateMessage" context="" type="1" thread="3592" file="statemsg.cpp:1527">

 

 

I’ve truncated the XML due to size – leaving only a single state message – we will come back to the XML shortly as it really isn’t very friendly in this format.

Note that although the statemessage.log records that the messages have been dispatched to the MP, the statemessage system doesn’t actually do the movement of data to the MP – CCMExec does that as we see from the small snip below.  There is actually a bit more that goes on behind the scenes at this point but it’s sufficient to know that CCMExec sends data to the MP – in this case the MP_Relay component.

<![LOG[OutgoingMessage(Queue='mp_mp_relayendpoint', 
ID={A9E7A07D-223D-4F5D-93D5-15AF5B72E05C}): Delivered successfully to host 
'STEVERACPRI.STARTREKNG.COM'.]LOG]!>

Switching to the MP now, when data arrives for processing at MP_Relay (again, a bit more behind the scenes stuff happens but not worth diving into here since most folks will never come across a need to dig in) we see the data processed and translated to .SMX file format and placed in auth\statesys.box\incoming.

Inv-Relay Task: Processing message body    
 Relay: FileType= SMX    
 Relay: Outbox dir: C:\Program Files (x86)\Microsoft Configuration Manager\inboxes\auth\statesys.box\incoming    
 Relay: Received 0 attachments    
 Relay: 0 of 0 attachments succesfully processed    
 Inv-Relay: Task completed successfully   

If we take a look at the auth\statesys.box\incoming directory – and if we are fast enough – we will see the .SMX files there and being processed.  Typically you won’t see much here – if you do it’s worth investigating what messages are here and not being processed.  If we find a .SMX and crack it open with notepad we can see the detail but the formatting leaves much to be desired.

smx.png
A little trick here.  If you rename the .SMX and simply add a .XML extension – so <filename>.SMX.XML and double click it you get a much nicer formatting of the data through IE.  I’ve highlighted the info in the file that is key – we can easily see the machine details and also state message details we have become familiar with – and also the unique state message ID.

Note:  If you are going to rename these files then you should copy them out first so you aren’t impacting the statesys.box folder itself.

xml.png

From here all that remains is to process these state messages into the database.  You can see these messages processed in the statesys.log – something similar to the following.

 

Found new state messages to process, starting processing thread    
 Thread "State Message Processing Thread #0" id:5076 started    
 CMessageProcessor - Detected parent site 'CEN'    
 CMessageProcessor - Processing file: mdlbp169.SMW    
 CMessageProcessor - Processed 1 records with 0 invalid records.    
 CMessageProcessor - Successfully replicated file "mdlbp169.SMW" to parent site CEN.    
 CMessageProcessor - Processed 1 message files in this batch, with 0 bad files.    
 Thread "State Message Processing Thread #0" id:5076 terminated normally

 

The DB processing component often can be seen by enabling SQL tracing.  That doesn’t help much here so we turn to SQL profiler – which does give us a hint at what is happening but still doesn’t’ completely pull back the covers.  Suffice it to say that there are a number of SQL stored procedures responsible for processing state messages – and also a number of tables in the database that store state message data.  The stored procedures that do the state message processing generally start with the name spProcess – there are a number of them.  Those interested should be able to dig in further from here to see the tables involved, etc.

OK, with processing into the DB complete we have finished the state message process flow – well, almost.  The last thing to discuss is state message resyncs.

I have already alluded to the fact that the site server keeps track of state messages as they come in – so that if any state messages are missing they can be flagged and a resync requested from time to time.  State messages and their data are important so we definitely don’t want to miss any.

As state messages come in the unique ID is read and stored in the database.  As processing continues this data is constantly updated and if a gap is detected we flag it and store any missing state messages in the SR_MissingMessageRanges table.  One would hope that this table will be empty but in production we may see data in the table here and there.  This table is used to help keep track of state messages needing resync.

Any needed resyncs are evaluated hourly – but that does not mean that systems will be running a resync hourly.  The site control file specifies a few settings of interest (with the site control file always apply the caution – look, don’t touch!   )

 

BEGIN_COMPONENT 
   <SMS_STATE_SYSTEM> 
   <6> 
   <STEVERACPRI> 
    PROPERTY <Loader Threads><><><4> 
    PROPERTY <Inbox Polling Interval><><><900> 
    PROPERTY <Loader Chunk Size><><><256> 
    PROPERTY <Max Chunks Fetched><><><100> 
    PROPERTY <Resync Check Interval><><><60> 
    PROPERTY <Min Missing Message Age><><><2880> 
    PROPERTY <Heartbeat Msg Interval><><><15> 
 END_COMPONENT
PROPERTY <Resync Merge Interval In Hours><><><72>

 

Resync Check Interval is set to 60 minutes – this is the schedule at which we check for any systems needing state message resyncs.

Min Missing Message Age is set to 2880 – this is the amount of time a message has to be missing before a resync is requested.

Resync Merge Interval in Hours is set to 72 – this is the amount of time between resyncs of a client.  This interval helps ensure that clients won’t resync over and over again.  You should not see a client resync more frequently that the interval set here.

 

  • WSUS automated cleanup via powershell

 https://gallery.technet.microsoft.com/scriptcenter/WSUS-Clean-Powershell-102f8fc6

 

This little script will perform a clean up of declined and superceded updates in the WSUS database.  It uses the .Net class “Microsoft.UpdateServices.Administration” assembly, which should be loaded on your WSUS server.

Note that this script will stop the WSUS service twice, but these services will be restarted correctly by the script, and no user action is needed.

# Performs a cleanup of WSUS.
# Outputs the results to a text file.
$outFilePath = '.\wsusClean.txt'
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;
$cleanupScope.DeclineSupersededUpdates = $true      
$cleanupScope.DeclineExpiredUpdates         = $true
$cleanupScope.CleanupObsoleteUpdates     = $true
$cleanupScope.CompressUpdates                  = $true
#$cleanupScope.CleanupObsoleteComputers = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupManager = $wsus.GetCleanupManager();
$cleanupManager.PerformCleanup($cleanupScope) | Out-File -FilePath $outFilePath

 

  • Enforcement State Unknown
    • Analyse the repeat offenders from past 4 to 5 months.

Report Name | States 1 – Enforcement states for a deployment

Assuming that each month has a unique deployment, within the report – it will

 

  •  Client Side
    • Tools

Client Actions Tool | https://sccmcat.codeplex.com/

Description
SCCM Client Actions Tool or SCCM CAT is a practical and simple HTA application for performing most common day-to-day administrative tasks on System Center Configuration Manager 2007 clients. The tool allows running actions remotely on one or more computers. A list of computers can be provided either from a file (XLS, XLSX, CSV, TXT), loaded from SCCM collection or simply entered as a text into a text area. The main goal for creating this tool was to have something simple that doesn’t have any prerequisites and doesn’t require installation. Just pick it up and run. It’s an alternative to SCCM console and right-click tools because it’s not always possible to install SCCM console everywhere and manage clients in closed environments. The features are as follows:

  • Initiate most common SCCM client schedule actions.
  • Initiate various actions to manipulate SCCM client agent. Install/uninstall agent, change GUID, assign site code, change cache size, etc.
  • Initiate SCCM client health checks and fixes. Allows running checks with and without fixes as well as full health check.
  • Initiate various administrative actions on workstations. Copy a file to remote computers, refresh policies, reset security settings, wake on LAN, etc.
  • Query for different values from remote computers. Query for wide range of information such as current management point, available advertisements, logged-on user, WSUS server, WUA version, patch status, system uptime, reboot pending state, etc.
  • Switch between integrated authentication and alternate credentials. Supports using multiple credentials. When logged on username has not enough rights it’s possible to specify alternate credentials by clicking on a link in top-right corner. Windows XP requires that cmdkey.exe is available in HTA folder.
  • Check for newer versions of the tool on startup.
  • Automatically install SCCM client during health check in case version is too old or agent does not work. Optional feature that can be enabled in configuration file.
  • Save list of offline computers for later use. Optional feature that can be enabled in configuration file.
  • Easily configure client installation properties. Ccmsetup.exe command line can be created dynamically by using GUI controls.
  • Use TXT, CSV, XLS or XLSX files as the data source. Files with TXT extension must have computer names on each line. Excel worksheets are read from column A starting from second row and it’s possible to write results back to worksheet. Using exported CSV from SCCM console is also supported.
  • Populate computer list from SCCM collection. Allows loading all collection members into a list.
  • Manually enter computer names into a textbox. Allows manually entering one or more computers in a text box for quick actions.
  • Supports both 32-bit and 64-bit OS on clients.
  • Supports Windows XP SP2 and newer operating systems on clients.
  • Displays real-time progress. Works when running HTA on Windows 7 or Windows Server 2008. Useful when there are thousands of computers and it would be nice to know how much is done. HTA window may not update as smoothly in Windows XP and Windows Server 2003, but it works.
  • Log is created in a text area and in a file. Lastlog.log is written to HTA folder. By default the log is using Trace32 log formatting. Log can be opened directly in application. It’s also possible to keep log history.
  • Uses configuration file to store default settings.

 

 

 

 

IT Challenges

IT administrators and IT support staff need easier access to key information about software and operating system deployments, client health, and compliance with regulations.  They must ensure that their systems and software meet the configuration requirements established for the organization.  And they need the ability to track this information without having access to a System Center Configuration Manager console.

Solution

The Microsoft System Center Configuration Manager 2007 Dashboard lets customers track application and operating system deployments, security updates, the health status of computers, and IT compliance with key regulations—with an easy to use, customizable Web interface.  Because the Dashboard is built on Windows® SharePoint® Services, IT staff can access information without using the Configuration Manager console. The Dashboard is a free Solution Accelerator, and fully supported by Microsoft.

Key Benefits

Benefits of the dashboard include:

  • Actionable information out of the box. The dashboard comes with valuable, built-in datasets that IT managers can access without using the Configuration Manager console.
  • Centralized, near-real-time access to key information. The graphical dashboard lets customers view any Configuration Manager data set in near-real time—without leaving their desk.
  • Easy to build and configure.The dashboard’s wizard-based tools let customers easily create new dashboards in minutes.
  • Easy to customize. The dashboard can easily be customized to meet the needs of different departments and other groups. Any data set in the Configuration Manager database can be presented on the dashboard, in chart, gauge, and table formats.
  • Flexible & interactive. Users can easily filter data and create ad hoc, custom views. Filters allow users to quickly drill down from high-level to more specific data.

 

Download the re-eval script and wsus cleanup powershell script

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.

Power BI : How to make quick awesome looking dashboard for SCCM

An awesome example of what power bi can do – and its all interactive and dynamic content – which sync’s with your sql data in real time. a little bit of sql and little bit of excel does the magic.

The dashboard above was created to show the remnants of Office 2010 in the environment.

what all do we have in here ?
A dynamically created map based on location pulled from SCCM
a scrolling ticker indicating increase or decrease in count as per trends
count of machines per location
trends indicated by a simple graph 
a dynamic water level indicators for data.
a dynamic pyramid based breakup
Query used to pull from SQL >
——————————————————————————————
SELECT       distinct vrs.AD_Site_Name0, vrs.Distinguished_Name0, vrs.Netbios_Name0, vrs.Resource_Domain_OR_Workgr0, vrs.User_Name0, sof.ProductName0, sof.ProductVersion0, sof.Publisher0
FROM            v_R_System AS vrs LEFT OUTER JOIN
                         v_GS_INSTALLED_SOFTWARE AS sof ON vrs.ResourceID = sof.ResourceID
WHERE        sof.ProductName0 like ‘Microsoft Office Standard 2010’ or  sof.ProductName0 like  ‘Microsoft Office Professional 2010’ or  sof.ProductName0 like  ‘Microsoft Office Professional Plus 2010’
——————————————————————————————
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.