In telemetry we trust?

More than patches

Telemetry is one of those things that tends to divide a room. On one hand it’s productive and accurate feedback for your product of choice and on the other hand it’s big brother spying on what you’re doing in that product. I wanted to share a recent experience with you based on my recent upgrade of Microsoft System Center Configuration Manager (SCCM) to the recent 1710 release as it may make you change your mind.

The long awaited 1710 release was made available last week a little before midnight in the UK on 20/11/17 (or 11/20 in the US Smile). The next morning I fired up my lab which runs the current branch version of SCCM, ran the early update ring PowerShell script and proceeded to whizz through the wizard in a speedy not hastily fashion. This was lab after all so on with the day job and I’ll check…

View original post 689 more words

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

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.