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 : https://gallery.technet.microsoft.com/Validate-Boundary-Group-d85ed496

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 >

output

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.

Advertisements

ConfigMgr : Machine added to a ConfigMgr group is not captured during the Delta Discovery Process (ConfigMgr 2007)

usa_new_york_manhattan_rockefeller_center_binoculars_112290_602x339

When adding machines to a Security group you usually want them to appear in the collection quickly although there can be a delay while waiting on full discovery.  If increasing the Full Discovery Polling schedule is not an option, we wondered if there might be another way to speed up this process by getting the machine’s updated memberof information captured via the Delta Discovery process.

To test this we added machine A to Security Group – Computers-TEST.  We also created a collection based on the security group Computers-TEST:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = “SMS\\Computer-TEST”

We then enabled delta discovery [default 5 minutes] on the following:

Active Directory System Discovery
Active Directory System Group Discovery
Active Directory Security Group Discovery

Unfortunately when the delta discovery process ran there was no DDR created for the machine A.  We checked the LDAP search filters that were applied [for the LDAP path specified in the discovery method] and found the following in the Active Directory Security Group Discovery log:

search filter = ‘(&(uSNChanged>=149673)(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=2147483648)))’
In the above LDAP filter – it is checking for any Security Group with a uSNChanged value greater than or equal to 149672
information available here >
1.2.840.113556.1.4.804 = LDAP_MATCHING_RULE_BIT_OR

A match is found if any bits from the attribute match the value. This rule is equivalent to a bitwise OR operator (http://msdn.microsoft.com/en-us/library/windows/desktop/aa746475(v=vs.85).aspx).

2147483648 = ADS_GROUP_TYPE_SECURITY_ENABLED (http://msdn.microsoft.com/en-us/library/windows/desktop/ms677935(v=vs.85).aspx)

We checked the Active Directory System Discovery log and found this:

search filter = ‘(&(uSNChanged>=149673)(&(objectClass=user)(objectCategory=computer)))’

The same was found in the Active Directory System Group Discovery log:

search filter = ‘(&(uSNChanged>=149673)(&(objectClass=user)(objectCategory=computer)))’

In the above two LDAP filters, it is checking for a Class-User and Category-Computer with respective higher than or equal to uSNChanged values.

NOTE The default logging does not indicate what Active Directory Attributes are to be viewed. These are listed on the Active Directory System Discovery > Properties > Active Directory Attribute tab.

We then manually ran the LDAP search filters via LDP and found that  when adding a computer to a security group, the security groups uSNChanged value increases and the computers uSNChanged value remains the same.

The ‘member’ attribute changes on the security group which triggers the uSNChanged bump.  The USN value for the machine does not increase as it is a “back link” attribute that is not populated like normal attributes (source : http://technet.microsoft.com/en-us/library/cc961761.aspx).

So the uSNChanged value of the machine has to increase for System Group Discovery Process to create a DDR for that machine, and only then will the attribute be fetched. Delta Discovery does not capture this occurrence of change in the machine due to this “back link” factor, hence the full discovery process is required for the machine to appear.

A workaround would be to change a field like the ‘Description’ field which then bumps up the uSNChanged value.  This  gets captured via the System Group Discovery Process and a DDR is created.

Here’s how to run the LDAP filters manually.  The example below shows a list of computers with an uSNChanged value above 149673:

launch ‘ldp.exe’ >
connection > connect ‘leave the name field blank’ – default port 389
connection > bind > default is ‘bind as currently logged on user’ > ok
Browse > Search >
Base DN : DC=contoso,DC=com
Filter : search filter = ‘(&(uSNChanged>=149673)(&(objectClass=user)(objectCategory=computer)))’
Scope – select ‘Subtree’
Attributes – add uSNChanged – so it would look like
objectclass;name;description;canonicalName;usnchanged
now Hit – Run

The output will look something like this:

ldap_search_s(ld, “DC=contoso,DC=com”, 2, “(&(uSNChanged>=149673)(&(objectClass=user)(objectCategory=computer)))”, attrList, 0, &msg)
Getting 1 entries:
Dn: CN=Machine-A,OU=Computers,DC=contoso,DC=com
canonicalName: contos.com/computers/machine-A;
description: its time for change !;
name: Computer-A;
objectClass (2): top; person; organizationlPerson; user; computer;
uSNChanged: 149689;

The above ldap output indicates that 1 entry returned the computer “Machine-A” whose uSNChanged value was 149689 (higher than the search filter).

Reference article:

How to poll for object attribute changes in Active Directory on Windows 2000 and Windows Server 2003: http://support.microsoft.com/kb/891995

 

source : (my own post) https://blogs.technet.microsoft.com/configurationmgr/2012/03/27/machine-added-to-a-configmgr-group-is-not-captured-during-the-delta-discovery-process/

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


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.