System Center, Technology

Cranky Dave sez Knock that Shit Off

Maybe it’s because I had a string of calls which rubbed the same raw nerve endings too close together. Idk. But I feel like the message isn’t getting out there. Please, if you work with customers, and see these things, urge the living shit out of them to reconsider. If that doesn’t work, duct tape and white vans are still available.

  • Doing things “just because” it’s been that way since 2010
  • Giving out local admin rights like candy. If you want to be drug dealer, run for Congress or Senate.
  • Using login scripts as first response to every need.
  • Repackaging every app installer, even when it only needs “/S” to run quiet
  • Stop over-complicating things just to be cute/clever. Look at me virtualizing my virtual servers inside another virtual server with 3 databases, and I only have 100 devices to manage.
  • Read the #(*$&@(*#$&(_(*@(#*$&) docs and follow the “supported” terms. Stop assuming you’re smarter than a room full of MVPs, *and* a yacht filled with drunk attorneys who all graduated from Harvard.
  • If your environment has to be complicated, it’s most likely because your business is over-complicated, and possibly broken. (Paul’s rule: If you automate a broken process, you will only ever get an automated broken process).
  • Stay within support – Don’t cry for help with your SQL 2005 “mission-critical” database. If it was “mission-critical” it would be running on a supported version.
  • Keep your shit patched – SQL 2016 is nice, but RTM is like 2 service packs and 12 cumulative updates behind! This isn’t 1962. We finished the Moon program a while ago.
  • Do I sound cranky? I’m grabbing a Snickers.

databases, Scripting, System Center, Technology

Basic ConfigMgr HealthChecks using PowerShell

Image result for prostate exam

I’m long overdue for a deep-dive (pardon the pun), so drink-up and let’s move on…

The power provided by PowerShell comes from a lot of different directions, one of them being that you can leverage a ton of built-in functionality without having buy additional software licensing, or even write all the messy code. That’s right, once again, I’m on my “modules are freaking fantabulously increditastical” soap box. I’ll be using a few different modules to do the heavy lifting:

And even though I won’t be showcasing it in this post, if you wish to export anything to Excel, rather than hopping through CSV first, take a look at the module ImportExcel by Doug Finke (the Export-Excel function in particular).

Heads-Up: This is not intended to be a “solution” that you simply download and run. I prefer to share the basic pieces and a direction, and let you take it and run with it however (and wherever) you like. Sharing a fully-bolted, polished solution doesn’t leave you with room to explore and customize without a lot of reverse engineering. Here’s the bricks, have fun building.

If you’re wondering why I’m not covering CMHealthCheck, it’s because (A) it would violate the “heads-up” goal mentioned above, and (B) that module is becoming a bit dated anyway (I’m working on a replacement, feedback is always welcome).

And Now for a Word on Modules

I’ve been in a few discussions about “make vs. buy” or “build vs. borrow” view of scripting. For decades (yes, I’m that freaking old, so you’ll have to speak up), I had always leaned towards building everything. Even when finding a near-perfect match online, I would rewrite it to my tastes. Not anymore. Time is more precious, and I’m not too proud to accept someone else might have provided a better option than I would have built.

In 2020, the state of online sharing is 1000 times what it was 10 years ago. It’s now to the point where not finding a close match for a desired technical solution is the exception, rather than the norm. Only the newest emerging things are lagging behind, mostly due to the trend of over-stimulated coke-snorting CI/CD fanaticism, but I’ll leave that for another episode of “Old man says GTFO my lawn you little CI/CD pipeline bastards!” But, I digress.

To me, modules are like car parts. Even when you build, or restore, a car, you’re not likely going to make EVERY single part from scratch (unless you own a smelting factory, chrome dip tank, a cow farm for leather, and so on). Most components are built by someone else. So, building things from parts is just a natural thing to me. It’s organic. Okay, soap box session is done. Let’s move on.

Getting Things Ready

To perform almost any health assessments, you’ll need sufficient access to the resources. In a typical ConfigMgr environment (if there is a typical ConfigMgr environment), this will translate into:

  • Full Administrator (in ConfigMgr)
  • ServerAdmin (in the SQL instance)
  • Local Administrator (on the site servers)

These are often granted to the account which was used to install the Configuration Manager site. Hopefully, it’s not an actual “user” account (that a human logs in with every day), but a service-type account. If you are not a DBA (or the DBA-ish person who “owns” the SQL instance) you may want to confer with them first, to make sure you don’t step on any toes. Pro-tip: bring doughnuts and fresh jokes.

When I say “Local Administrator”, I don’t mean adding your domain account directly into the local Administrators group, although that does work. It’s recommended that you are a member via domain group membership and that whole AG(U)DLP chain of handling that Microsoft has recommended for decades.

I already mentioned the PowerShell modules I’ll reference, so those need to be installed on your working computer (not on the site servers or database server, unless that’s all you’re working on)

To save on repetitive typing, let’s define some variables to use throughout the following examples. Replace the string values with whatever your TEST LAB environment uses:

$dbhost = "cm01.contoso.local" # site SQL host FQDN
$cmhost = "cm01.contoso.local" # CM primary site host FQDN
$site   = "P01" # CM site code
$cmdb   = "CM_P01" # CM site database

MECM Site Information

To help with finding things in SQL, mainly the default Views, I recommend running the following snippet, so you can use the results to search more easily:

$dbviews = Get-DbaDbView -SqlInstance $dbhost -Database $cmdb -ExcludeSystemView

An example for finding views which relate to something like “site”…

$dbviews | Where {$_.Name -match 'site'} | select name

You can also pass this into a cheap GridView (only $0.99 while supplies last) to pick-and-run your favorite view…

$view = $dbviews | Out-GridView -Title "Pick a View to Query" -OutputMode Single
if (![string]::IsNullOrEmpty($view)) {
  $query = "select * from $view"
  Invoke-DbaQuery -SqlInstance $dbhost -Database $cmdb -Query $query
}

I have a slightly more fancy version of the above sample, as a function, up on my GitHub at http://bit.ly/2SYYOOL. You can load it directly into a console session, and run it, using Invoke-Expression…

iex (New-Object System.Net.WebClient).DownloadString('http://bit.ly/2SYYOOL')
Invoke-CmDbView -SqlInstance $dbhost -Database $cmdb

Site Information Summary

Invoke-DbaQuery -SqlInstance $dbhost -Database $cmdb -Query "select * from v_Site"

General Client Information

I recommend saving the output of the following script to a variable, for use as a baseline for other operations (rather than requesting new data for each sub-query). I’m using $cmdevices for this example…

$cmdevices = Invoke-DbaQuery -SqlInstance $dbhost -Database $cmdb -Query "select * from v_CombinedDeviceResources where (name not like '%unknown%') and (name not like '%provisioning device%') order by name" | 
select Name,MachineID,SerialNumber,MACAddress,DeviceOS,DeviceOSBuild,CoManaged,ClientVersion,IsVirtualMachine,ADSiteName,LastMPServerName,LastPolicyRequest,LastDDR,LastHardwareScan,LastSoftwareScan,LastActiveTime,LastClientCheckTime,ClientCheckPass

From this you can filter on things like the following examples.

Devices with Old or Missing Hardware Inventory

Find devices which haven’t reported hardware inventory yet…

$cmdevices | Where {[string]::IsNullOrEmpty($_.LastHardwareScan)}

Find devices which have reported hardware inventory in the past, but not with the past 30 days…

$cmdevices | Where {(-not[string]::IsNullOrEmpty($_.LastHardwareScan)) -and ((New-TimeSpan -Start $_.LastHardwareScan -End (Get-Date)).Days -gt 30)}

Compare Device Coverage with AD

$adComps = Get-ADComputer -Filter * -Properties lastlogontimestamp,whenCreated,operatingsystem,description

I included some additional attributes in case I want to also compare last-login dates, and so on. But anyhow, to use this to compare devices between AD and MEM, you can run some super-basic tests like this…

$adComps | Where {$_.Name -notin $cmdevices} | select Name
$cmdevices | Where {$_.Name -notin $adComps} | select Name

The example above shows I have more devices in Active Directory which are not in the ConfigMgr database, than I have devices in ConfigMgr which are not in Active Directory. What kind of “health” is this? It’s a measure of how clean and controlled your environment really is.

General Windows Host Information

Get-CimInstance -ClassName "Win32_ComputerSystem" -ComputerName $cmhost
Get-CimInstance -ClassName "Win32_OperatingSystem" -ComputerName $cmhost
Get-CimInstance -ClassName "Win32_SystemEnclosure" -ComputerName $cmhost
Get-CimInstance -ClassName "Win32_Product" -ComputerName $cmhost
Get-CimInstance -ClassName "Win32_BIOS" -ComputerName $cmhost

Disks and Disk Space

Get-CimInstance -ClassName "Win32_LogicalDisk" -ComputerName $cmhost | % {
  [pscustomobject]@{ 
    Drive  = $_.DeviceID
    Name   = $_.VolumeName
    SizeGB = [math]::Round(($_.Size / 1GB),2)
    FreeSpaceGB = [math]::Round(($_.FreeSpace / 1GB),2)
    Used   = [math]::Round($_.FreeSpace / $_.Size, 2)
  }
}

Network Connection Properties

Get-CimInstance -ClassName "Win32_NetworkAdapterConfiguration" -ComputerName $cmhost | 
    Where {$_.IPEnabled -eq $True} | 
        Select IPAddress,DefaultIPGateway,IPSubnet,MACAddress,DNSServerSearchOrder,DNSDomainSuffixSearchOrder | ForEach-Object {
            [pscustomobject]@{
                IPAddress   = $_.IPAddress -join ','
                IPGateway   = $_.DefaultIPGateway -join ','
                IPSubnet    = $_.IPSubnet -join ','
                MACAddress  = $_.MACAddress
                DNSServers  = $_.DNSServerSearchOrder -join ','
                DNSSuffixes = $_.DNSDomainSuffixSearchOrder -join ','
            }
        }

File Shares

Get the file shares, folder and share permissions. This information can be used to further automate for “drift” reporting and remediation, when someone (or some process) modifies them for whatever reason. (Note: The following example has no exception handling. You may want to add some nested try/catch handling inside the foreach-object (%) section.)

$shares = Get-CimInstance -ClassName "Win32_Share" -ComputerName $cmhost | 
  where {$_.Name -ne 'IPC$'} | % { 
    $spath = "\\$cmhost\$($_.Name)"
    $fpath = "\\$cmhost\$($_.Path -replace ':','$')"
    $perms1 = Get-CPermission -Path $spath
    $perms2 = Get-CPermission -Path $fpath
    [pscustomobject]@{
      Name = $spath
      Path = $_.Path
      Description = $_.Description
      SharePermissions = $perms1
      FilePermissions = $perms2
    }
  }

Stopped or Failed Services

Another common check is looking for services which are set to “automatic” but are not currently running…

Get-CimInstance -ClassName Win32_Service -ComputerName $cmhost |
  Where {$_.StartMode -eq 'Auto' -and $_.State -ne 'Running'}

Ooooh. Missing Updates?

What about those pesky Windows updates on your site systems? Yeah, they need them. And SQL Server updates too.

Get-WindowsUpdate -ComputerName $cmhost -WindowsUpdate
# note: if the -ComputerName connection fails, try using Enter-PSSession instead

Event Logs

The Windows Event Log is a gold mine for finding current and potential issues with a Windows Server.

# system log "critical","warning" and "error" entries in the last 24 hours...
$xfilter = @'
<QueryList>
  <Query Id="0" Path="System">
    <Select Path="System">*[System[(Level=1 or Level=2 or Level=3) and TimeCreated[timediff(@SystemTime) &lt;= 86400000]]]</Select>
  </Query>
</QueryList>
'@
$sysEvents = Get-WinEvent -LogName "System" -ComputerName $cmhost -FilterXPath $xfilter

# application log "critical","warning" and "error" entries in the last 24 hours...
$xfilter = @'
<QueryList>
  <Query Id="0" Path="Application">
    <Select Path="Application">*[System[(Level=1  or Level=2 or Level=3) and TimeCreated[timediff(@SystemTime) &lt;= 86400000]]]</Select>
  </Query>
</QueryList>
'@
$appEvents = Get-WinEvent -LogName "Application" -ComputerName $cmhost -FilterXPath $xfilter

ConfigMgr Server Logs

Oh boy. This part isn’t fun. You can search the server and component status events within the site database, which is often faster, but I’ll save that for another post.

For this one, I borrowed a very nice script by Adam Bertram, aka “Adam the Automator” (Twitter: @adbertram) and modified it slightly (okay, I poured dumb sauce all over it) to read server logs instead of client logs. I realize that some logs don’t follow a consistent internal format, so if you know of a better alternative, please chime in?

iex (New-Object System.Net.WebClient).DownloadString('http://bit.ly/2vIhtXk')
$logs = ('sitecomp','dataldr','hman','distmgr','smsexec','wsyncmgr')
$logs | ? {
  Get-CmServerLog -ComputerName $cmhost -SiteCode $site -LogName $_ | ? {$_.Category -eq 'Error'}
}

Database Information

A Configuration Manager site isn’t much good without a SQL Server database. And a SQL Server database isn’t much good if it’s suffering from issues resulting from mis-configuration, neglect of maintenance and updates, and so on. So any real “health check” of a system implies checking all the parts which it depends on, which in this case is the site database.

SQL Instance Summary

This will return basic version and update information, such as version, build, service pack, cumulative update and KB levels, and support status.

Get-DbaBuildReference -SqlInstance $dbhost

Getting SQL Server update compliance can be tricky. At least it has been for me, and probably because I’m retarded AF. But if you find it tricky too, then maybe it’s from something else, but anyhow, here’s one way…

# import a magical function from the world of beyond...
Test-DbaBuild -SqlInstance $dbhost -Latest

CM Database Summary

This will return a summary of your SQL database, such as name, instance, status, recovery model, compatibility level, collation, owner, and basic backup info.

Get-DbaDatabase -SqlInstance $dbhost -Database $cmdb

Connection Authentication Scheme

Test-DbaConnectionAuthScheme -SqlInstance $dbhost

SQL Instance Memory Allocation

This will return summary information about the current maximum memory limit, and current usage for the instance (in megabytes).

Get-DbaMaxMemory -SqlInstance $dbhost

You can also retrieve current memory usage stats…

Get-DbaMemoryUsage -ComputerName $dbhost

Database File Information

This will return details about each .mdf and .ldf file for your CM database, such as path, size, status, reads/writes, and more.

$dbfiles = Get-DbaDbFile -SqlInstance $dbhost -Database $cmdb

Database File Auto-Growth Information

This is basically an extension of the example above, which dives more into the auto-growth aspects.

$dbfiles | select LogicalName,Size,Growth,GrowthType,UsedSpace,NextGrowthEventSize,TypeDescription

Database Index Fragmentation

This will return the current fragementation state of your database indexes (indices?) I prefer to break this into two (2) parts: a query file, and the script code. The query file contains only the SQL statement, which the script code imports using the -File parameter. The first example below is the SQL statement, followed by the PowerShell script.

SELECT 
  dbschemas.[name] as 'Schema',
  dbtables.[name] as 'Table',
  dbindexes.[name] as 'Index',
  indexstats.avg_fragmentation_in_percent as 'FragPct',
  indexstats.page_count as 'PageCount' 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
  INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
  INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
  INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
  AND indexstats.index_id = dbindexes.index_id
WHERE 
  indexstats.database_id = DB_ID()
ORDER BY 
  indexstats.avg_fragmentation_in_percent desc
$qfile = "[ENTER_THE_SCRIPT_PATH_HERE]\Documents\indexfrag.sql"
$threshold = 40 # index frag percent baseline, whatever you prefer
$stats = Invoke-DbaQuery -SqlInstance $dbhost -Database $cmdb -File $qfile
[math]::Round(($stats | where {$_.FragPct -gt $threshold}).Count / $stats.count, 2)

Failed SQL Agent Jobs (last 24 hours)

Get-DbaAgentJobHistory -SqlInstance $dbhost -StartDate (Get-Date).AddHours(-24) | Where {$_.Status -ne "Succeeded"}

Database Backup History

Get-DbaDbBackupHistory -SqlInstance $dbhost

Conclusion

I think I’ve talked enough for now, and I’m out of coffee. As I mentioned earlier (I think), this is only a sampling of some of the things you can bolt together using off-the-shelf modules, and some minimal touch-up work.

As the MECM or MEM/CM team adds more to the Management Insights library of tools, you can expect to peel off a few custom tools, but that may be a gradual process. Keep an eye on this feature with each new build that you install.

This isn’t at all restricted to MEM/CM/ConfigMgr, or even SQL Server (even though I spent a lot on this).

Now, put on your best pair of Latex gloves, and smile. 🙂

databases, Projects, Scripting, System Center, Technology

Cool SQL Tricks with DbaTools and MEM ConfigMgr

If you’ve ever wanted to export information from a Configuration Manager site database, you’ve probably found many different ways to do it. Sometimes the management console provides an easy method, sometimes not. Sometimes the SMS Provider (WMI) makes it easy, sometimes not (think set-join, operations for example). Then there’s SQL queries.

One of the best features of SQL, as a language, is the ability to perform dataset joins, or combining results from multiple source tables/views/functions as if the source data was all in one place (it kind of is, but kind of isn’t). Then comes the “how do I run a query against the database from PowerShell?” question.

A lot of examples use .NET ADO methods like connections, datareaders, data adapters, and so on. Which is fine, but yuck! That’s a lot of messy code and I just took a shower. I’m lazy, and I hate writing more lines of code than I really need. Enter PowerShell modules.

Modules are basically toolboxes, filled with tools to help with certain tasks, by taking care of the messy or complicated stuff for you in the background.

If you’ve ever used PowerShell, you’ve used modules. Just type Get-Module and press Enter to see the ones currently loaded in memory. To see all of the modules installed add -ListAvailable after it (e.g. Get-Module -ListAvailable).

One module I absolutely love is dbatools. It’s a toolbox of functions for dealing with SQL Server from inside and outside. By “inside” I mean interacting with tables, views, functions, agent jobs, and so forth. By “outside” I mean server configuration, statistics, backups, processes, memory allocation, version and build info, and much more. It really is amazing. (follow @psdbatools on Twitter for updates and tips)

Install-Module dbatools

Then type “Get-Command -Module dbatools” and lean closer to your screen. Slide a plate under your mouth to catch the drool.

I use a lot of functions in that module, and even so, only about 10% of the overall list. That’s still enough for my needs, and when new needs arise, it’s usually ready for me. For a full list of commands – click here. (note: if it’s missing something, you can suggest it, or contribute via their GitHub site).

Invoke-DbaQuery

The Invoke-DbaQuery function submits a query to a specified database from either a file or text. To use a file, specify the full path/name using the -File parameter. To use text, specify the -Query parameter. Here’s an example for showing all the Collections, along with their ID and membership count…

Note that the -SqlInstance parameter refers to the SQL server hostname (and instance name, if not referencing the “default” instance), and the -Database parameter is the name of the database (I’m not being snarky, I really meant that).

$query = "select CollectionId,Name,MemberCount from v_Collection order by Name"
Invoke-DbaQuery -SqlInstance "cm01.contoso.local" -Database "cm_p01" -Query $query

Before you get too excited, and start getting twitchy fingers, please heed the following recommendation:

  • Do not EVER submit changes directly to the database for a ConfigMgr site. No writing or deleting, renaming, altering of any kind. Just read operations.
  • Limit your read operations to just what you need, and keep in mind that every request you submit adds to the overhead it is already dealing with.
  • TEST EVERYTHING IN AN ISOLATED, NON-PRODUCTION LAB ENVIRONMENT THOROUGHLY BEFORE YOU EVER THINK ABOUT TRYING IT IN PRODUCTION (unless you win the PowerBall and just don’t care, but don’t forget me?)

WMI vs SQL vs REST (Admin Service)

Some of you may ask about the differences between SQL, WMI and REST when it comes to reading data. WMI (SMS Provider) has been the go-to since, well, forever. The problems with WMI for intensive data operations are:

  • WMI is slower than SQL. Typically a lot slower. Compared with SQL queries, it’s like listening to Mitch McConnell talk after he’s had 4 martini’s, and then listening to John Moschitta Jr. talk after he’s had 4 cans of Red Bull. And WMI queries (WQL) don’t support compound join operations.
  • The Admin Service (REST API) is newer, and will eventually replace the SMS Provider. It provides a robust channel using a web service, which, like the SMS Provider, acts as a broker between the requestor and the actual SQL database. If you’ve used the Modern Driver Management solution from SCConfigMgr.com, the task sequence step which runs a PowerShell script is using a custom web service which provides the same function (only at a smaller scale)
  • SQL is by far the fastest for returning large datasets, and excels at complex join operations. But it’s also tapping into the very heart of ConfigMgr and should be done carefully.

The Admin Service is probably going to become the go-to path for interacting, programmatically, with MEM/CM databases, and it is very cool indeed. But for me, there will always be a soft spot for using SQL. So don’t take this as a “you should use SQL always for everything” suggestion. It’s really just ONE of several ways you can get data out of the site.

For more on the Admin Service, read Sandy‘s article here, and Adam Gross‘s article here.

Back to SQL for a moment… Another example, this one for querying the general MEM/CM site information:

$query = "select * from v_Site"
Invoke-DbaQuery -SqlInstance <server\instance> -Database <name> -Query $query

As of Tech Preview build (5.00.8946.1000) there are 1757 non-system views in the CM_xxx database schema. Sometimes it can be a challenge to search (or filter) within SSMS to find one you need. PowerShell can make it a little easier, especially with Get-DbaDbView.

$views = Get-DbaDbView -SqlInstance "cm01" -Database "cm_p01" -ExcludeSystemView | select Name | sort Name

Because this is often a slow return, I recommend saving the output to a variable (as shown above). This makes it less painful to filter/sort than sending a new request.

Getting more serious, like putting-my-pants-on serious, let’s check the database index fragmentation status. For this, since the query is a bit longer, I will dump it in a file and save it as “indexfrag.sql” (shown below) and use the -File parameter to submit the contents to the SQL instance. I also want to save the output to a variable, so I can perform some filtering and sorting:

SELECT 
  dbschemas.[name] as 'Schema',
  dbtables.[name] as 'Table',
  dbindexes.[name] as 'Index',
  indexstats.avg_fragmentation_in_percent as 'FragPct',
  indexstats.page_count as 'PageCount' 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
  INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
  INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
  INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
  AND indexstats.index_id = dbindexes.index_id
WHERE 
  indexstats.database_id = DB_ID()
ORDER BY 
  indexstats.avg_fragmentation_in_percent desc
$stats = Invoke-DbaQuery -SqlInstance "cm01" -Database "cm_p01" -File "C:\users\clueless\Documents\indexfrag.sql"

Now I can filter the results to see how bad my fragmentation really is:

[math]::Round(($stats | where {$_.FragPct -gt 40}).Count / $stats.Count, 2)

My lab database doesn’t seem too bad (0.03% of indices are more than 40% fragmented), but that’s because I use Ola Hallengren‘s DB Maintenance Solution (as explained by Steve Thompson)

By the way, speaking of Ola Hallengren’s solution, dbatools has a built-in function for installing it. Check out Install-DbaMaintenanceSolution. One caveate is that it doesn’t create the nice Maintenance Plan that Steve describes, and some of the jobs aren’t configured with all the options (e.g. IndexOptimize – USER_DATABASES).

To list the Agent jobs, use Get-DbaAgentJob:

Get-DbaAgentJob -SqlInstance "cm01" # dumps a lot of details for each job

Gluing things together

These are just a few building blocks, and you can easily start building all sorts of diabolical planet-saving awesomeness to earn your next bonus check. For example:

  • Query for Devices which meet (or don’t meet) a given condition
  • Output to a CSV, XLSX file, or HTML file
  • Attach to an email and send it on a schedule

Aside from basic things like this, you can install the module on ANY machine in your environment which has access to the network, it doesn’t need to be on the SQL host (actually, it’s preferred that you don’t install things on your site servers unless absolutely necessary).

Having dbatools on a workstation, or utility server, allows you to consume data from multiple instances and databases. You can use modules like dbatools for building custom automation processes. For example, you can install it on an Azure Automation Hybrid Worker to leverage it with Runbooks. This is handy for pulling data from disconnected environments (think multiple AD forests without lateral trusts). Even shaping the extracted data for uploading into Log Analytics (now Azure Monitor).

The sky is the limit. The world is your oyster. Now, drink some super-caffeinated stuff and get busy!

Cloud, Devices, Scripting, System Center, Technology, windows

Rant 42 – Getting In Tune with Intune

Keep in mind that everything I’m about to say is within the context of the following:

  • This is entirely focused on managing Windows desktops and laptops. This has nothing to do whatsoever with mobile devices.
  • This is based on querying the Graph API “beta” version, but much of it applies to v1.0 as well
  • I’m more comfortable crawling inside Configuration Manager and SQL Server databases
  • It’s entirely possible that I’m a complete idiot and haven’t the slightest clue what I’m talking about
  • I’m struggling to be funny, but I’m tired and pissed off trying to get inventory data for a customer and keep landing on “if this was ConfigMgr I’d have it in 5 seconds”
  • After three weeks of no beer, wine or anything fun, I just finished two very tasty beers. And now my fingers want to type some shit out.

In the Beginning

(Morgan Freeman voice here) Most of the last 20 years I’ve worked in “IT” has been muddling with computer devices of various kinds; not necessarily from a hardware aspect (calm down), but from a software and infrastructure angle. Deploying, configuring, managing, repairing, replacing, decommissioning. Okay, and breaking, and swearing at. Mostly those which run some flavor of Microsoft Windows.

As such, there are many commonplace scenarios I’ve dealt with, which may seem very familiar to you

  • Inventory
  • Health (prescriptive and reactionary)
  • Disposition
  • Compliance
  • Financial
  • Legal
  • Control

The first three are pretty common everywhere, regardless of what hardware or software brands are used. The fourth is emerging as the top contender. And while financial and legal aspects are important, ask any attorney, it’s the last item, Control, that takes the lead. After all, you can’t really guarantee any of the others without having control in some respect.

After almost 25 years of getting involved with various aspects of inventory management, what I find most interesting is a seemingly disconnected view of Control and Inventory. Many organizations seem to view them as semi-related, or unrelated. But they are actually entirely related. Like politicians and pedophiles, they’re impossible to separate.

You need inventory to gain control, and you need control to gather inventory. And for decades, traditional (on-prem) solutions have evolved and matured to meet the needs of almost every customer in every environment when it comes to inventory and control.

Most modern cloud-based offerings offer a wide variety of control capabilities, but are lacking when it comes to inventory. This is arguably due to having a mobile device focus, at least during their inception. Mobile phones typically don’t incur the same depth of inventory concern as desktop and laptop computers. This is partly due to the platform restrictions and licensing costs for available apps (consider the most expensive phone app vs. the most expensive desktop apps).

The operational aspects are different, as are the lifecycle management aspects. Think of the typical sequence of events in the life of a mobile phone and compare that to a typical laptop or workstation. Before you get your panties tied around your neck in angst, consider deploying most mobile apps with deploying things like ArcGIS, AutoCAD, Inventor, USB-related software, device drivers, language packs, multiple user profiles, and so on. Yeah, they’re not quite the same animals

For decades, Active Directory was the backbone of device inventory management, but in 2020 that’s no longer exclusive. Not that AD was a comprehensive or robust solution in that regard, but that it was a back-plane on which other applications, scripts, databases, and so on were extended. AD was, and still is, a foundation for managing devices (and users and groups). And now there’s Azure AD.

More and more customers, particularly smaller organizations, don’t want any “servers” to manage. Many larger organizations have smaller teams that they’d like to be less encumbered with on-premises infrastructure. They want devices to live in the cloud, and be managed entirely, and exclusively, in the cloud.

The challenge is that cloud solutions were built, initially, to address a different need: mobile devices (mostly phones and tablets). Traditional desktops and laptops have enjoyed decades of maturing management tools. And now the market is pushing one solution to cover a different need.

The more that customers come knocking on our doors with questions about replacing on-prem inventory and management systems with Intune, the more challenges I encounter with gaps in functionality. This is particularly true for inventory management. And since a huge number of customers rely on inventory for a variety of business needs, they demand a solution that provides comprehensive and accurate inventory data.

That said, here’s what I’ve compiled from customer discussions, as well as my own (limited) experiences. Intune management features for Windows computers could use some improvements in at least the following areas:

  • More comprehensive inventory:
    • BIOS information (vendor, version, dates, etc.)
    • Driver information (name, vendor, version, dates, etc.)
    • Applications: Just copy what ConfigMgr gathers, that’ll nail it
    • Custom file inventory (by extension, by wildcard, etc.)
    • User Profiles
    • Windows Services
    • NIC device info, adapter config info (IP addresses, gateway, DNS, etc.)
    • Address some quirks: PhysicalMemoryInBytes always shows zero (0), ethernetMacAddress is empty for almost all devices, and chassisTypes shows “unknown”
    • Windows Events (filtering, reporting)
  • Win32 application deployments
    • More robust and simplified troubleshooting (log access/viewing, custom logs)
    • More robust lifecycle management: upgrades, complex batch execution (task sequences), and so on.
  • Patching could be better, particularly having the ability to decline/reject specific updates
  • Run PowerShell scripts on a schedule, view/edit scripts in the portal, and view a history of all past-deployed PowerShell scripts per device
  • I would add REST API (Graph) changes as well, but these would depend on the preceding suggestions
  • I realize some of this is possible with things like ATP and other premium tools, but as a base product, it needs more from the start.
  • I could go on, but I’ll just point you to UserVoice

Hallucinatory Thoughts

Related image

Ever since Microsoft made a bold push for Intune, and the “cloud first, mobile first” motto was announced, the ConfigMgr masses reacted quickly with a message that “ConfigMgr is dead”. I don’t think Microsoft really intended (or expected) that would grow so quickly.

The dilemma this likely caused was two-fold: Internal and External

First, the ConfigMgr team was pushing on with an enormous backlog of new features, and fixes; investing heavily in beefing up ConfigMgr features. Allowing the public perception to go unchecked could risk impacting sales and revenue, and impacting team morale at a critical time. No one wants to work on a project with a doomed future. Valuable people would leave, and potentially-valuable people would avoid it entirely.

Second, imagine you’re a business looking for direction on how to manage (mostly Windows-based) devices on-prem and out in the wild. You have an older version of ConfigMgr, and are struggling to decide whether it’s worth upgrading, or finding a new solution. A pervasive message of a “dead product” would almost certainly steer you towards another solution. Again, impacting sales revenue and so on.

To avoid both risks, Microsoft had to shore-up the message that there’s a future, and it was both products combined as a unified solution. And rightly so. It makes perfect sense. They’ve admitted this is a journey and worth sticking to if you want to reap the benefits of a superior solution at a better (more affordable) cost.

However…

There’s still that group of customers who weren’t using ConfigMgr to begin with. They’re using <insert any name of inexpensive product> to manage inventory and reporting, software deployments, patch management, and so on. I’ll paraphrase a quote from one of my customers, but it matches most customers in a similar situation:

“When you compare Intune with an on-prem solution, like PDQ or LAN Sweeper, as limited as they are compared with ConfigMgr, they still outshine Intune for managing of, and reporting on, Windows desktops and laptops. And we already own them. We need a compelling reason to drop this and pay subscription for something new. It’s just not there yet.”

To be honest, most of them I’ve spoken with would love to switch to Intune. The notion of eliminating on-prem infrastructure, and using a web browser from anywhere, is very appealing. They’re not saying “no”, but “not yet”. They’re keeping an eye on it, and many have trial accounts or smaller paid subscriptions, to continue testing and learning, and comparing.

But Why?

Related image

I really don’t know why Intune hasn’t pushed as hard and fast as ConfigMgr development has (I mean, holy McShit!). People still ask me, “Hey Dan!” (“It’s Dave”) “Right. Doug, why don’t you pick on Configuration Manager anymore? “

My answer is “Because, (A) my name is Dave and (B) there’s really nothing to pick on anymore.” That’s right. The CM team is knocking it out of the park. If you don’t believe me, come to MMS and find out for yourself. I did.

I’m sure the Intune team has equally lovely, hard-working people, lovingly hard at work to make the love work like it should. Or something like that. But I haven’t sat at a table with them, so they don’t know my face or what kind of car I drive either. So, for now at least, I can focus on them.

So, Now What?

Some of the limitations (missing capabilities) can be addressed using things like PowerShell script deployments and Win32 app deployments. But these are also limited. Let’s say you wanted to override the hamstrung inventory capabilities using a custom PowerShell script, or using an Azure Function or Automation Runbook.

Of these, the PowerShell script makes the most sense for two reasons:

  • Most likely you’re working with laptops, which roam around and aren’t accessible over the WAN 24/7, like desktops typically are.
  • Initiating the process from the remote client insures the greatest probability of success due to timing (when it’s on, and connected, it runs, instead of poking from afar repeatedly and trying again and again)

Pulling data from a device isn’t difficult, thanks to CIM/WMI and PowerShell. But you need to store the inventory (and other) query results somewhere if you want to make use of it. Excluding the use of a third-party tool, that leaves you with a two primary options:

  • PowerShell + Azure Storage Account (e.g. blob container)
  • PowerShell + Log Analytics

Both of these require some sort of credentials to access and import data. Whether that’s username/password, or a key, it has to be embedded in the script, which makes it vulnerable and risky.

Another issue is that deploying a PowerShell script from Intune only runs one time per device. Implementing a recurring/scheduled update requires either recreating the same script as a new assignment, or creating a scheduled task, and hope it doesn’t stop working for whatever reason. Either way, it’s reinventing a wheel that’s been around for DECADES. Sort of like “Here’s a shiny new car! Wheels are still in planning, but you can make your own

Summarizing my Conclusion

Once again, paraphrasing my colleagues and customers, and anyone who doesn’t run away as soon as I start talking to them, I would say MOST people trying to manage Windows devices would LOVE to do so from a nice and pretty web portal, and without a lot of on-prem infrastructure to mess with. Buy the new device, join it to a cloud party, and manage the open bar from the cloud.

In 2020, that vision is closer than it has ever been to becoming a reality. But the other reality is that not only are there still some serious technical challenges (rural connectivity, bandwidth, idiot users, crappy applications, bullshit drivers, more idiots, decaf coffee, JRE, McAfee, etc.) that remain persistent, but they aren’t going away anytime soon. If the economy doesn’t tank in the meantime, I think in the next five years this will be as commonplace as mobile phones are today. I hope so.

But then again, in 2020, I still have to fiddle with BIOS settings, and the ConfigMgr Query Rule dialog box may outlive today’s most powerful cockroaches. So, while I’m hopeful, I still don’t have a flying car, or that paperless office I was promised 20 years ago.

Finger’s crossed.

Namaste!

Cloud, Devices, Scripting, Technology, windows

Export HW/SW Inventory Data from Intune Devices using PowerShell

What is this recent torrent of Intune gibberish coming from this foul-mouthed idiot? Is he some sort of “expert”? Bah! Nope! I’m just working with it a bit more lately, so I figured I’d brain-dump on it while I can (and to help me recall things if I step away from it for a few months).

Background and Setup

The inventory data for Intune-managed Windows 10 devices is stored in Azure and exposed through the Graph API. And while it can seem challenging to find good examples for accessing it with PowerShell, there is in fact a very nice repository of example scripts on the Microsoft GitHub site at https://github.com/microsoftgraph/powershell-intune-samples

Given that I’m still learning my way around Intune, and Graph, the first thing I found helpful were the examples ManagedDevices_Get.ps1, and ManagedDevices_Apps_Get.ps1, under the ManagedDevices folder. Both of these were very helpful and I was able to pull the data I needed.

However, since I needed to query 1800+ devices, I noticed the default “page” limit returns only the first 1000 records (devices). Then I found they also posted a nice example ManagedDevices_Get_Paging.ps1, which I merged with the ManagedDevices_Get.ps1, and was able to pull all of the devices at one time. The make part that needs help are lines 179 to 187 (below)…

$DevicesNextLink = $DevicesResponse."@odata.nextLink"
while ($DevicesNextLink -ne $null){
    $DevicesResponse = (Invoke-RestMethod -Uri $DevicesNextLink -Headers $authToken -Method Get)
    $DevicesNextLink = $DevicesResponse."@odata.nextLink"
    $Devices += $DevicesResponse.value
}

After that, I added the 2 or 3 lines of code to query the installed applications and add those to an output object (a master set of data for each device, including hardware, operating system and applications). I added this to a new function (below) to return the data for further processing.

function Get-DsIntuneDeviceData {
	[CmdletBinding()]
	param(
		[parameter(Mandatory)][string] $UserName,
		[parameter()][switch] $ShowProgress,
		[parameter()][switch] $Detailed
	)
	Get-DsIntuneAuth -UserName $UserName
	$Devices = Get-ManagedDevices
	Write-Host "returned $($Devices.Count) managed devices"
	if ($Devices){
		$dx = 1
		$dcount = $Devices.Count
		foreach ($Device in $Devices){
			if ($ShowProgress) { 
				Write-Progress -Activity "Found $dcount" -Status "$dx of $dcount" -PercentComplete $(($dx/$dcount)*100) -id 1
			}
			$DeviceID = $Device.id
			$uri = "https://graph.microsoft.com/beta/deviceManagement/manageddevices('$DeviceID')?`$expand=detectedApps"
			$DetectedApps = (Invoke-RestMethod -Uri $uri -Headers $authToken -Method Get).detectedApps
			$dx++
			if ($Detailed) {
				$disksize  = [math]::Round(($Device.totalStorageSpaceInBytes / 1GB),2)
				$freespace = [math]::Round(($Device.freeStorageSpaceInBytes / 1GB),2)
				$mem       = [math]::Round(($Device.physicalMemoryInBytes / 1GB),2)
				[pscustomobject]@{
					DeviceName   = $Device.DeviceName
					DeviceID     = $DeviceID
					Manufacturer = $Device.manufacturer
					Model        = $Device.model 
					MemoryGB     = $mem
					DiskSizeGB   = $disksize
					FreeSpaceGB  = $freespace
					SerialNumber = $Device.serialNumber 
					OSName       = $Device.operatingSystem 
					OSVersion    = $Device.osVersion
					Ownership    = $Device.ownerType
					Category     = $Device.deviceCategoryDisplayName
					Apps         = $DetectedApps
				}
			}
			else {
				$disksize  = [math]::Round(($Device.totalStorageSpaceInBytes / 1GB),2)
				$freespace = [math]::Round(($Device.freeStorageSpaceInBytes / 1GB),2)
				[pscustomobject]@{
					DeviceName   = $Device.DeviceName
					DeviceID     = $DeviceID
					OSName       = $Device.operatingSystem 
					OSVersion    = $Device.osVersion
					Apps         = $DetectedApps
				}
			}
		}
	}
	else {
		Write-Host "No Intune Managed Devices found..." -f green
		Write-Host
	}
}

The full trainwreck can be safely viewed here. Be sure to wear rubber gloves while handling it.

With that, I decided to drop it into a new module to make it easier to access and reuse. I also added a few more functions, with the help of examples from Matthew Dowst and Eli Shlomo and some calls to PowerShell module ImportExcel, by Doug Finke. I named this module ds-intune.

Example

This example was tested on ds-intune 0.3.

Install-Module ds-intune
Get-Command -Module ds-intune

The two functions I’ll use below are Get-DsIntuneDeviceData and Export-DsIntuneAppInventory.

$CustomerName = "Contoso"
$UserName = "<your_AzureAD_UserPrincipalName>"
Connect-AzureAD
# be patient, this step can take a while if you have more than 50 machines
$devices = Get-DsIntuneDeviceData -UserName "john.doe@contoso.com" -ShowProgress -Detailed
Export-DsIntuneAppInventory -DeviceData $devices -Title $CustomerName -UserName $user -Overwrite -Show -Verbose

As always: Please post comments or corrections, winning lottery numbers, tasteless jokes, and happy thoughts. Here or at the GitHub repo.

Tomorrow I’m off to Ft. Myers for 3 days of work. Wish me luck.

Cheers!

Cloud, Devices, Scripting, Technology, windows

Find Intune Devices with the Jan 2020 Windows 10 CU Patch Installed

I’ve been playing around with a bunch of different code fragments from all over the place, but I think I found a good mix that works. For now at least. Special (huge, gigantic) thanks to Matthew Dowst and Eli Shlomo for code examples which were used to build the following.

Requirements

  • Intune subscription, with devices being managed with software updates
  • A LogAnalytics Workspace with Update Compliance solution added for collecting telemetry data
  • PowerShell
  • Azure / Log Analytics: SubscriptionId, ResourceGroup, WorkspaceName
  • LogAnalyticsQuery.psm1 (below)
  • Invoke-LogAnalyticsQuery.ps1 (below)

Assembly

First, I got a huge leg-up with a KQL query from Matthew Dowst to show Log Analytics results from WaaSDeploymentStatus…

WaaSDeploymentStatus
| where TimeGenerated > ago(1d)
| where ReleaseName contains "KB4534273"
| summarize arg_max(TimeGenerated, *) by ComputerID
| project Computer, ComputerID, ExpectedInstallDate, DeploymentStatus, DetailedStatus
| render table

From there, I added a few (small) changes to show the OSName and OSVersion. But since each KB is matched to a particular build/version of Windows 10 (e.g. 1903 = KB4528760, 1809 = KB4534273, etc.) I ended up needing to match the query to the build so I look for the relevant data.

Next, I stumbled over this (actually, I stumbled over the cat, and a pair of slippers first, then the code)… https://www.eshlomo.us/query-azure-log-analytics-data-with-powershell/

I applied some voodoo magic brain sauce, and sprinkled some caffeine dust on it as follows:

$apiVersion = "2017-01-01-preview"

<#
	.DESCRIPTION
		Invokes a query against the Log Analtyics Query API.

	.EXAMPLE
		Invoke-LogAnaltyicsQuery -WorkspaceName my-workspace -SubscriptionId 0f991b9d-ab0e-4827-9cc7-984d7319017d -ResourceGroup my-resourcegroup
			-Query "union * | limit 1" -CreateObjectView

	.PARAMETER WorkspaceName
		The name of the Workspace to query against.

	.PARAMETER SubscriptionId
		The ID of the Subscription this Workspace belongs to.

	.PARAMETER ResourceGroup
		The name of the Resource Group this Workspace belongs to.

	.PARAMETER Query
		The query to execute.
	
	.PARAMETER Timespan
		The timespan to execute the query against. This should be an ISO 8601 timespan.

	.PARAMETER IncludeTabularView
		If specified, the raw tabular view from the API will be included in the response.

	.PARAMETER IncludeStatistics
		If specified, query statistics will be included in the response.

	.PARAMETER IncludeRender
		If specified, rendering statistics will be included (useful when querying metrics).

	.PARAMETER ServerTimeout
		Specifies the amount of time (in seconds) for the server to wait while executing the query.

	.PARAMETER Environment
		Internal use only.
	.NOTES
		Adapted heavily from Eli Shlomo's example at https://www.eshlomo.us/query-azure-log-analytics-data-with-powershell/
#>
function Invoke-LogAnalyticsQuery {
	param (
		[Parameter(Mandatory)][string] $WorkspaceName,
		[Parameter(Mandatory)][guid] $SubscriptionId,
		[Parameter(Mandatory)][string] $ResourceGroup,
		[Parameter(Mandatory)][string] $Query,
		[string] $Timespan,
		[switch] $IncludeTabularView,
		[switch] $IncludeStatistics,
		[switch] $IncludeRender,
		[int] $ServerTimeout,
		[string][ValidateSet("", "int", "aimon")] $Environment = ""
	)

	$ErrorActionPreference = "Stop"

	$accessToken = GetAccessToken
	$armhost = GetArmHost $environment
	$queryParams = @("api-version=$apiVersion")
	$queryParamString = [string]::Join("&", $queryParams)
	$uri = BuildUri $armHost $subscriptionId $resourceGroup $workspaceName $queryParamString

	$body = @{
		"query" = $query;
		"timespan" = $Timespan
	} | ConvertTo-Json

	$headers = GetHeaders $accessToken -IncludeStatistics:$IncludeStatistics -IncludeRender:$IncludeRender -ServerTimeout $ServerTimeout
	$response = Invoke-WebRequest -UseBasicParsing -Uri $uri -Body $body -ContentType "application/json" -Headers $headers -Method Post

	if ($response.StatusCode -ne 200 -and $response.StatusCode -ne 204) {
		$statusCode = $response.StatusCode
		$reasonPhrase = $response.StatusDescription
		$message = $response.Content
		throw "Failed to execute query.`nStatus Code: $statusCode`nReason: $reasonPhrase`nMessage: $message"
	}

	$data = $response.Content | ConvertFrom-Json

	$result = New-Object PSObject
	$result | Add-Member -MemberType NoteProperty -Name Response -Value $response

	# In this case, we only need the response member set and we can bail out
	if ($response.StatusCode -eq 204) {
		$result
		return
	}

	$objectView = CreateObjectView $data

	$result | Add-Member -MemberType NoteProperty -Name Results -Value $objectView

	if ($IncludeTabularView) {
		$result | Add-Member -MemberType NoteProperty -Name Tables -Value $data.tables
	}

	if ($IncludeStatistics) {
		$result | Add-Member -MemberType NoteProperty -Name Statistics -Value $data.statistics
	}

	if ($IncludeRender) {
		$result | Add-Member -MemberType NoteProperty -Name Render -Value $data.render
	}
	$result
}

function GetAccessToken {
	$azureCmdlet = get-command -Name Get-AzureRMContext -ErrorAction SilentlyContinue
	if ($null -eq $azureCmdlet) {
		$null = Import-Module AzureRM -ErrorAction Stop;
	}
	$AzureContext = & "Get-AzureRmContext" -ErrorAction Stop;
	$authenticationFactory = New-Object -TypeName Microsoft.Azure.Commands.Common.Authentication.Factories.AuthenticationFactory
	if ((Get-Variable -Name PSEdition -ErrorAction Ignore) -and ('Core' -eq $PSEdition)) {
		[Action[string]]$stringAction = {param($s)}
		$serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext, $stringAction)
	} 
	else {
		$serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext)
	}

	# We can't get a token directly from the service credentials. Instead, we need to make a dummy message which we will ask
	# the serviceCredentials to add an auth token to, then we can take the token from this message.
	$message = New-Object System.Net.Http.HttpRequestMessage -ArgumentList @([System.Net.Http.HttpMethod]::Get, "http://foobar/")
	$cancellationToken = New-Object System.Threading.CancellationToken
	$null = $serviceCredentials.ProcessHttpRequestAsync($message, $cancellationToken).GetAwaiter().GetResult()
	$accessToken = $message.Headers.GetValues("Authorization").Split(" ")[1] # This comes out in the form "Bearer <token>"

	$accessToken
}

function GetArmHost {
	param(
		[string] $environment
	)

	switch ($environment) {
		"" {
			$armHost = "management.azure.com"
		}
		"aimon" {
			$armHost = "management.azure.com"
		}
		"int" {
			$armHost = "api-dogfood.resources.windows-int.net"
		}
	}

	$armHost
}

function BuildUri {
	param (
		[string] $armHost,
		[string] $subscriptionId,
		[string] $resourceGroup,
		[string] $workspaceName,
		[string] $queryParams
	)

	"https://$armHost/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/" + `
		"microsoft.operationalinsights/workspaces/$workspaceName/api/query?$queryParamString"
}

function GetHeaders {
	param (
		[string] $AccessToken,
		[switch] $IncludeStatistics,
		[switch] $IncludeRender,
		[int] $ServerTimeout
	)

	$preferString = "response-v1=true"

	if ($IncludeStatistics) {
		$preferString += ",include-statistics=true"
	}

	if ($IncludeRender) {
		$preferString += ",include-render=true"
	}

	if ($null -ne $ServerTimeout) {
		$preferString += ",wait=$ServerTimeout"
	}

	$headers = @{
		"Authorization" = "Bearer $accessToken";
		"prefer" = $preferString;
		"x-ms-app" = "LogAnalyticsQuery.psm1";
		"x-ms-client-request-id" = [Guid]::NewGuid().ToString();
	}

	$headers
}

function CreateObjectView {
	param (
		$data
	)

	# Find the number of entries we'll need in this array
	$count = 0
	foreach ($table in $data.Tables) {
		$count += $table.Rows.Count
	}

	$objectView = New-Object object[] $count
	$i = 0;
	foreach ($table in $data.Tables) {
		foreach ($row in $table.Rows) {
			# Create a dictionary of properties
			$properties = @{}
			for ($columnNum=0; $columnNum -lt $table.Columns.Count; $columnNum++) {
				$properties[$table.Columns[$columnNum].name] = $row[$columnNum]
			}
			# Then create a PSObject from it. This seems to be *much* faster than using Add-Member
			$objectView[$i] = (New-Object PSObject -Property $properties)
			$null = $i++
		}
	}

	$objectView
}
Export-ModuleMember Invoke-LogAnalyticsQuery

Then I built an array / list (okay, a stupid nested array like a noob, geez) to match the OS versions to the respective KB numbers. The KQL query also has an added line for OSVersion, and the project statement adds OSVersion and OSBuild to the output stream.

param (
	[string] $WorkspaceName = "<your workspace name>",
	[guid] $SubscriptionId = "<your subscription id>",
	[string] $ResourceGroupName = "<your resource group name>"
)
if (!(Get-Module LogAnalyticsQuery)) { Import-Module .\LogAnalyticsQuery.psm1 }

$kblist = (('1903','KB4528760'),('1809','KB4534273'),('1803','KB4534293'),('1709','KB4534276'))

$results = @()

foreach ($kbset in $kblist) {
	$query = @"
WaaSDeploymentStatus
	| where TimeGenerated > ago(1d)
	| where OSVersion == "$($kbset[0])"
	| where ReleaseName contains "$($kbset[1])" 
	| summarize arg_max(TimeGenerated, *) by ComputerID
	| project Computer, ComputerID, OSVersion, OSBuild, ExpectedInstallDate, DeploymentStatus, DetailedStatus
	| render table 
"@
	$params = @{
		Query          = $query
		WorkspaceName  = $WorkspaceName
		SubscriptionId = $SubscriptionId
		ResourceGroup  = $ResourceGroupName
	}

	$results += ($(Invoke-LogAnalyticsQuery @params).Results)
}

$results

Putting these both in the same folder as LogAnalyticsQuery.psm1 and Invoke-LogAnalyticsQuery.ps1 (respectively), I can run it to compile results and pump them out to a gridview …

.\Invoke-LogAnalyticsQuery.ps1 | Out-GridView

Or output to an Excel workbook using Doug Finke’s ImportExcel PowerShell module…

.\Invoke-LogAnalyticsQuery.ps1 | Export-Excel -Path "c:\reports\CU-installs.xlsx" -Show -WorksheetName "Installs" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow

Cheers!

Technology, windows

Quick Assist – The Overlooked Remote Assistant

Troubleshooting and assisting remote computer users has always been a challenge. For years, with earlier Windows versions, we had Remote Assistance. But many organizations never gave it much attention, and instead skipped right over it to third-party products like TeamViewer, LogMeIn, <fill-in-name>VNC, and others. Many are of them are fine and do a great job. Many of them also impose feature limits unless you pay for the “professional”, “enterprise” or “premium” edition, etc.

Since Windows 10 1709, Microsoft added another alternative to Remote Assistance, and I’m surprised how many sysadmins have never heard of it.

It’s called Quick Assist.

This came up during a call with a customer who uses Intune and they asked about the TeamViewer feature. Like other customers I’ve spoken with, they had the impression that there’s really no other alternative. But there is (are), and even if you opt out of using Quick Assist, there are other free tools available which may do what you need. This jaw-jacking mind spewage however is focused on Quick Assist, so let’s get in and go for a ride.

Quick Assist is not a “headless”, or unattended, remote connection solution. It requires the end user to be at their computer and logged on. It’s also fairly simple to use, and only requires the following conditions in order to use it:

  • Both the end user and the IT technician need machines with an Internet connection.
  • The end user needs to be present (and logged on) to the target computer.

Connecting

The process goes like this:

  1. The technician opens Quick Assist (Win key + “Quick” and select the app)
  2. Technician clicks “Assist another person”
  3. Technician provides credentials to authenticate to the organization
  4. Technician provides the one-time access code to the user: By voice (over the phone), by email, Twitter DM, Facebook Messenger, SMS text, carrier pidgeon, or crackheads on stolen bicycles who need to earn some extra cash.
  5. The end user enters the one-time access code, and clicks “Share Screen”
  6. The Technician is prompted to choose either “View Screen” or “Take Full Control”
  7. The end user reads the long, boring warning message that the remote user could be ISIS or Putin, and then clicks “Allow” to grant the Technician permissions, with finger’s crossed and eyes closed.
  8. The Screen Sharing session begins.
  9. The Technician begins extracting personal information from the end user computer, assumes their identity and drains their bank accounts within 10 minutes, while calmly repeating “almost done, just a few more clicks“. Just kidding. We’re all honest here, right? See? This is why I don’t work at crisis call centers.

From here, the Technician can perform any task they would if logged onto the physical workstation (or virtual machine). Remember when Remote Assistance would blank-out the Technician screen if a UAC prompt was triggered on the end user desktop? Not with Quick Assist.

That’s right, the Technician is free to roam around and deploy whatever diabolically-destructive dastardly deviations they desire, and blame it all on the poor end user. That is, of course, unless the end user agrees to whatever terms the Technician demands of them.

Oh wait, that’s the wrong movie plot. Uhhh. I mean, from here, the Technician can troubleshoot anything related to Windows, applications, management tools, like MECM or Intune, Group Policy, or good old fashioned disk space vaporization from truckloads of vacation photos and video clips. But let’s see some juicy screen shots…

Oooh! Screen shots (high tech)

Step 1 – Technician Desktop

Step 2 – Technician Desktop

Step 3 – Technician Desktop

Step 4 – Technician Desktop (note the countdown timer)

Step 5 – End User Desktop

Step 6 – End User Desktop

Step 7 – Technician Desktop

Step 8 – End User Desktop (panel minimizes to top of screen)

Step 9 – Technician Desktop

The menu bar links are normally shown as icons without labels, unless you click the “. . .” link to the far-right (Details). This will toggle the text labels on and off. “Details” seems weird. Why not “Show Labels” like 99.999999999% of other apps do? Because…

From left to right:

  • Select Monitor – For toggling between multiple remote monitors (end user machine)
  • Annotate – This is supposed to allow the Technician to “draw” on the end user’s desktop to help guide them where to look or click on things. However, in my tests it was hit or miss (mostly miss). Your mileage may vary.
  • Actual Size – Zoom the Technician display to 100% , which may cut-off and add scroll bars to the window frame
  • Toggle Instruction Channel – Opens the super-cheap chat tool (see rant below)
  • Restart – Restart the remote (end user) computer
  • Task Manager – Opens the Windows Task Manager on the remote computer
  • Pause – Pauses the Technician screen session, blanks it out (black) with a message “You have paused screen sharing” / also Toggles “Pause” to “Resume”
  • End – Ends the screen sharing session
  • Details – Toggles the menu bar labels on or off

Technical Stuff

  • While Quick Assist is active, the application process will show as “quickassist.exe”, running from %windir%\system32.
  • You can effectively block Quick Assist using Group Policy or 3rd Party products using the filename and path. But why would you want to?

Some Caveats

  • Quick Assist won’t help you if the remote machine isn’t turned on, not connected to the Internet (or your WAN or LAN), or when the remote machine won’t boot at all, for whatever reason, like being left in the trunk of a car or on the driveway in the rain.
  • The end user may not see chat messages sent by the Technician via the “Toggle Instruction Channel” feature, because the little clipboard icon on the end user machine will only show a red dot. The minimized panel also doesn’t stay on “top” (z-order) of other application windows, so the end user may need some coaching to find it.
  • The “Toggle Instruction Channel” is some sort of weird, bastardized, Tide pod ingesting, hallucinogenic imploded derivative of a chat tool, but without any chat history. So each message is the only one visible, on either end. Which is weird. I mean, this is 2020, and attorneys still play golf with money billed from customers who couldn’t find records of conversations to prove their actions in court, which is kind of why chat history is important. But whatever.
  • The Task Manager button is nice, but why not provide a CTRL+ALT+DEL button, or buttons for Computer Management, Regedit and Explorer?
  • It’s obvious, to me anyway, that Microsoft could easily bundle a really robust remote troubleshooting tool into Windows, but probably doesn’t want to start a bar room fight with partners. Not yet anyway.

What’s really amazing is that I could only find five (5) things to bitch about regarding Quick Assist. I’m sure there’s more, but the bullets above are enough for me. Aside from that stupid rant above, the rest of Quick Assist is pretty cool, and it could quite possibly bail your ass out in a pinch.

Cheers!