CMPIVOT 101

Posted on by Leave a comment

CMPivot is a tool in MEMCM (SCCM) that is under-rated or misunderstood by people. Its power is amazing, due to the fact it can live query machines in a collection, that are online, but also where devices are offline, use last known information from the MEMCM database

It uses the Kusto Query Language, so if you are familiar with basic SQL queries you can use this cheatsheet here https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet

If you are familiar with PowerShell then you recognise the Pipe operator | taking the output from one “cmdlet” and piping it to the next “cmdlet”

The following example shows how you can use two queries and join them and then only select the columns you want. So were are getting the Firmware information from each device where secureboot is false

then joining that information to the computersystem information

Then we are projecting the resultant object to only deliver 5 columns -device,Manufacturer,Model,Secureboot,UEFI.

Project is similar to SELECT in SQL or Select-Object in PowerSshell

Firmware | where SecureBoot == false | join (computersystem )| project device,Manufacturer,Model,Secureboot,UEFI

Here are some links on how to use the admin service to code these queries using powershell.

You can call CMPivot from the ConfigMgr Admin Service.

https://www.asquaredozen.com/2019/02/12/the-system-center-configuration-manager-adminservice-guide/

Here’s a post that goes into how you can query CMPivot over CMG. https://www.asquaredozen.com/2019/06/18/configmgr-1906-technical-preview-testing-cmpivot-over-cmg-using-adminservice/

Adam has a bunch of (somewhat working) scripts that you can see examples of usage for AdminService

https://github.com/AdamGrossTX/PowershellScripts/tree/master/ConfigMgr/AdminService

 

SCCM – Office 2016 updates install , but Windows 10 OS ones do not

Posted on by Leave a comment

You know how it is, Windows 7 is going EOL, you have had a bit of Windows 10 in play, starting at 1511 and upgraded last year to 1703 and you looking at rolling out 1809 and getting off Windows 7 before January 2020. You have thousands of Windows 7 devices and 50 – 100 Windows 10 mobile devices that you have been piloting with Windows 10, they are well less that the margin of error for devices with faulty Windows Update or SCCM Clients

So now your starting to roll out more devices and your finding that Windows Updates on Windows 10 is not working. Why?

Continue reading…

 

Powershell – Solarwinds – Get Devices Connected to Switch Ports

Posted on by Leave a comment

To get powershell access to Solarwinds Orion you need to install the SDK 

https://github.com/solarwinds/OrionSDK

The powershell module installed is

SWISPowershell

 

The best way to visually see what you are getting back is to first use SWQL Studio that in installed on your device when you install the SDK

Here is some example powershell code to get back the device and what switch port it is on

#Solarwinds Server
$SolarwindsServer = "SolarwindsServer"
#Username password is a local username password for Solarwinds
$SolarwindsUser = Read-Host -Prompt "Username"
$Password = Read-Host -Prompt "Password"
$swis = Connect-Swis -UserName $SolarwindsUser -Password $Password -Hostname $SolarwindsServer

#Get Connected devices, what port and switch they were connected to and when last seen
Get-SwisData $swis 'SELECT LastSeen, MACAddress, RawMAC, AdministrativeStatus, OperationalStatus, PortType, NodeAccessPoint, ClientSSID, NodeStatus, NodeStatusIcon, PortIcon, IPAddress, PortDescription, PortName, HostName, VendorIcon, ShutdownIcon, MACVendor, EndpointID, IsWireless, IsRWCommunityDefined, NodeID, PortID, MacUrl, PortUrl, APUrl, HostNameUrl,
                    MEMBERS.ContainerID, MEMBERS.MemberPrimaryID, MEMBERS.MemberEntityType, MEMBERS.Name
                    FROM Orion.UDT.MACCurrentInformation UDT
                    JOIN Orion.ContainerMembers MEMBERS   ON UDT.NodeID = MEMBERS.MemberPrimaryID
                    '

#Get Container(group)members returns nodes and the container they are in
Get-SwisData $swis 'SELECT ContainerID, MemberPrimaryID, MemberEntityType, Name, Status, MemberUri, MemberAncestorDisplayNames, MemberAncestorDetailsUrls, EntityDisplayName, EntityDisplayNamePlural, FullName, DetailsUrl,ContainerMembers.Container.Displayname AS ContainerName
                         FROM Orion.ContainerMembers