SCORCH : Create your first simple Orchestrator runbook (Automating AD User Account Creation)

IT Snacks!

Welcome to the blog .. to be simple .. System Center Orchestrator is a workflow tool dedicated to automate repeated tasks .. this is done by integrating SCO with other systems by using what is called the integration packs and setting conditions that will trigger the solution you design “The Runbook” to do the repeated tasks you used to do one by one automatically reducing time and cost, I assume you have a little knowledge of Orchestrator as this blog is not dedicated to be an intro to SCO neither explaining SCO architecture ..

In this blog we will do the following (high level steps):-

1- Set the initialize data activity to receive the AD user account first name, last name and SAM account name (login name), get this activity from the “Runbook Control” area in the activities list.

2- Use the “Generate Random Text” activity to generate a random…

View original post 654 more words


ConfigMgr : Hardware Inventory flow

Being Configuration Manager Administrator

SCCM ..(Hardware Inventory)

Hardware Inventory

Hardware inventory is a feature in itself and functions independent to the other features of SCCM, but it closely depends on a successful deployment.

SCCM Setup has to complete successfully. The Hardware inventory feature on the server side solely depends on setup and successful SQL install. All the tables from the default sms_def.mof should get correctly created in SQL with the right credentials.

Similarly, for the hardware inventory agent to function as per requirements on the advanced client the client setup should have completed successfully and the client should have access to the MP for the advanced client. On the Advanced client the Inventory agent’s functionality is dependent on the running of the SMS Agent Host service (ccmexec).

The entire hardware inventory feature is dependent on the sms_def.mof file present in the clifiles.srchinv folder on the SiteServer. If that file is deleted or made corrupt…

View original post 1,169 more words

Power BI : Active Directory v/s SCCM Boundaries : [FREE PBIX]

Purpose :

Active Directory Sites and System Center Configuration Manager Boundaries are hard to keep in sync – especially in an environment where there are regular changes and several team managing each technology separately.

This Power BI dashboard solution will help analyze and reduce down the gaps in SCCM. The dashboard will help SCCM administrators / architects to resolve issues where clients are not covered – this helps in Client Health / Reporting and Compliance.

The dashboard is based off the Powershell script posted on Technet by Scott Breen and can be downloaded from here :

How to use this :

A. The script can be configured to run on schedule via Scheduled Tasks on a server or via System Center Orchestrator.

B. The output CSV can be placed on a network share on local folder.

C. Power BI will read this csv and perform the necessary DAX operations.

D. Power BI can further be used to publish on o365 on the Power BI portal. Power BI gateway can be configured so that the content is autoupdated on a schedule. Embedded Power BI options can be used to inject the dashboard into Sharepoint or any site.

Backend engine :

The output [download csv here] of the script needs some work to be done and looks like this >


The last column is most important in this csv output – the result ‘False’ indicates the machine is not covered by any SCCM Boundary. The other columns provide plenty of information – but its not really presentable or understandable from the get go.

The conversion of the csv data from crude data to human readable format is performed by Power BI and via DAX. The csv file before conversion is available here – BoundaryCheck-csv

The PowerBI dashboard is configured to pull information from C:\PowerBI\adsite-vs-boundaries\Boundarycheck.csv

Get it now !


Download the PBIX.

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
    • 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.


  •  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 >


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 –
  •  Windows Update Agent Repair

Use the WUA from 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.
' 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.


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.

ID={A9E7A07D-223D-4F5D-93D5-15AF5B72E05C}): Delivered successfully to host 

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\\incoming.

Inv-Relay Task: Processing message body    
 Relay: FileType= SMX    
 Relay: Outbox dir: C:\Program Files (x86)\Microsoft Configuration Manager\inboxes\auth\\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\\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.

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 folder itself.


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!   )


    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> 
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


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 |

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.


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)


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.

    sys.Name0 ‘Machine’,
    sys.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    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’
    v_R_System SYS
    MAX(Creation_Date0) AS Creation_Date
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
    ON sys.ResourceID=cs.ResourceID
    ON sys.ResourceID=bios.ResourceID
    MAX(A.[InstallDate0]) AS [InstallDate0]
    ) AS X
    ON sys.ResourceID = X.ResourceID
    ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
    ON sys.ResourceID=ram.ResourceID
    ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
    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
    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’,”),
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    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 ,

    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.


5. Reconfigure the SQL Server

Home > Edit Queries.


Query Editor Window > Home > Data Source Settings


On Data Source Setting Window > Change Source


Enter the Server and Database details – then Hit ‘ok


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.

Powershell : OpsMgr : SCOM 2012 Prereqs Powershell

Start-Transcript -Path c:transcript0.txt -noclobber
# This scripts needs unrestricted access
Write-Host “This scripts needs unrestricted access (Set-ExecutionPolicy Unrestricted.)” -ForegroundColor Green
Write-Host “The prereq setup for System Center 2012 R2 Operations Manager takes around 15 minutes depending on your internet speed” -ForegroundColor Green
# Setting the variables.
$folderpath0 = ‘C:Source’
$ShareName = “Source$”
#Check if folder exists, if not, create it
if (Test-Path $folderpath0){
Write-Host “The folder $folderPath0 exists.”
} else{
Write-Host “The folder $folderPath0 does not exist, creating…” -NoNewline
New-Item $folderpath0 -type directory | Out-Null
Write-Host “done!” -ForegroundColor Green
# Check if file exists, if not, download it
$file0 = $folderPath0+”SQLSysClrTypes.msi”
$file1 = $folderPath0+”Reportviewer.msi”
if (Test-Path $file0){
write-host “The file $file0 exists.”
} else {
# Download System CLR Types for SQL Server 2012
Write-Host “System CLR Types for SQL Server 2012” -nonewline -ForegroundColor yellow
$clnt = New-Object System.Net.WebClient
Write-Host “done!” -ForegroundColor Green
if (Test-Path $file1){
write-host “The file $file1 exists.”
} else {
# Download Microsoft Report Viewer 2012 Runtime
Write-Host “Microsoft Report Viewer 2012 Runtime” -nonewline -ForegroundColor yellow
$clnt = New-Object System.Net.WebClient
Write-Host “done!” -ForegroundColor Green
# Install Windows Features.
Get-Module servermanager
Install-WindowsFeature -Name Web-Server
Install-WindowsFeature -Name Web-Default-Doc
Install-WindowsFeature -Name Web-Dir-Browsing
Install-WindowsFeature -Name Web-HTTP-Errors
Install-WindowsFeature -Name Web-Static-Content
Install-WindowsFeature -Name Web-Http-Logging
Install-WindowsFeature -Name Web-Request-Monitor
Install-WindowsFeature -Name Web-Stat-Compression
Install-WindowsFeature -Name Web-Filtering
Install-WindowsFeature -Name Web-Windows-Auth
Install-WindowsFeature -Name Web-Net-Ext
Install-WindowsFeature -Name Web-Net-Ext45
Install-WindowsFeature -Name Web-Asp-Net
Install-WindowsFeature -Name Web-ISAPI-Ext
Install-WindowsFeature -Name Web-ISAPI-Filter
Install-WindowsFeature -Name Web-Mgmt-Console
Install-WindowsFeature -Name Web-Metabase
Install-WindowsFeature -Name NET-Framework-Core
Install-WindowsFeature -Name NET-HTTP-Activation
Install-WindowsFeature -Name NET-WCF-HTTP-Activation45
Install-WindowsFeature -Name Windows-Identity-Foundation
Install-WindowsFeature -Name Telnet-Client
# Install System CLR Types for SQL Server 2012
Write-Host “Installing System CLR Types for SQL Server 2012..” -nonewline
$PSScriptRoot = Split-Path -Path $MyInvocation.MyCommand.Path
msiexec /qb /i “$folderPath0SQLSysClrTypes.msi” | Out-Null
Write-Host “done!” -ForegroundColor Green
Start-Sleep -s 10
# Install Microsoft Report Viewer 2012 Runtime
Write-Host “Microsoft Report Viewer 2012 Runtime..” -nonewline
$PSScriptRoot = Split-Path -Path $MyInvocation.MyCommand.Path
msiexec /qb /i “$folderPath0ReportViewer.msi” | Out-Null
Write-Host “done!” -ForegroundColor Green
Start-Sleep -s 10
Write-Host “The server needs to be restarted before you start the System Center 2012 R2 Operations Manager installation.” -nonewline -BackgroundColor Black -ForegroundColor Red