How to get a detailed list of all your Configuration Manager Deployments using Powershell

Shane Jackson IT Pro

In this blog I will show you step by step how to quickly get a detailed list of all your Configuration Manager deployments, output in Excel table format, including the following details

  • Application Name
  • Assignment ID
  • CI_ID
  • Collection
  • Collection Name
  • DeploymentID
  • Creation Time
  • Deployment Time
  • Enforcement Deadline
  • PackageID

You can also download this guide from the Technet Gallery here

First, open the System Center Configuration Manager Console


Click on the down arrow in the upper right hand corner and choose “Connect via Windows Powershell”.  This opens PowerShell with the Configuration Manager module loaded.  Type the following command (use whatever path you want to save the output)

Get-CMDeployment | Export-csv -NoTypeInformation c:\temp\Deployments.CSV



Next, open the Deployments.CSV file using Microsoft Excel.

Highlight / select all the rows and columns with data, select the “Insert” Tab, click on “Table”


Make sure to tick “My table has headers”, the click OK


You now…

View original post 34 more words

The Perfect PowerShell Script


  1. Set-UserCredentials
  2. Connect-VMHost
  3. Create-VMGroup
  4. Configure-ActiveDirectory
  5. Leave-ObsoleteADObjectsIntactForWayTooLong
  6. Add-TooManyUsersToAdminSecurityGroups
  7. Create-WayTooManyGPOsWithOverlappingSettings
  8. Create-CustomServiceAndUserAccountsAndGroups
  9. Install-CMPrerequisites
  10. Install-SQLServerUsingImproperDriveAllocations
  11. Ignore-SQLServerSettings
  12. Install-CMPrimarySite
  13. Install-CMSecondarySiteWhenDPorMPwouldWorkJustFine
  14. Configure-CMSiteHierarchy
  15. Configure-CMDiscoverySettings
  16. Configure-CMSiteBoundariesAndBoundaryGroups
  17. Configure-CMClientSettings
  18. Deploy-CMClientSettings
  19. Deploy-CMClients
  20. Ignore-CMStatusLogs
  21. Ignore-CMMaintenanceTasks
  22. Ignore-SQLServerMaintenancePlanBenefits
  23. Wait-ForCMCrash
  24. Listen-ToTechsBitching
  25. Escalate-ToManagement
  26. Allow-ManagementToIgnoreComplaints
  27. Compress-ITRolesAndSalaries
  28. Get-NothingDone
  29. Move-OperationsToTheCloud
  30. Empty-DataCenterFloorSpace
  31. Consume-PrescriptionMedsWithColdCoffee
  32. Remove-PersonalLife
  33. Remove-Sleep
  34. Find-AnotherJob

Turn SCCM SQL Queries into Web Reports

The goal of this exercise is to add custom reports to your Configuration Manager site so you can dazzle executives while you sneak out the door.  If you’re smart, you’ll make some that include pie and bar charts with lots of spiffy colors and formatting.  That would give you enough time to get to Starbucks and back before they even look back in your direction.

Ok, serious stuff now.

I’m using (long inhale….) Microsoft System Center Configuration Manager (aka SCCM) “Current Branch” build 1606, with the latest hotfixes, running on Windows Server 2012 R2 and SQL Server 2014 SP1.  In addition, I’ve installed SQL Report Builder 3.0 for SQL Server 2008 R2 **.

If you read any of my posts on SQL queries for SCCM, you can follow the same basic approach for the query building part.  It’s much easier/better/smoother to do that in SQL Management Studio than in the Report Builder.

** If you download SQL Report Builder for SQL 2016, it will look prettier, but the reports won’t import or run in SCCM 1606. It is partly to do with the schema level buried inside the RDL report file.  I’m hoping this gets addressed soon, since SQL Server 2016 is the cat’s meow already.  Anyhow, the SQL Report Builder is installed with the default settings.

Step 1 – Build a SQL query in SSMS

  • (we already did this in previous posts here and here and a few others)
  • Test the query until you get the results you want
  • Copy the SQL statement to the clipboard and proceed

Step 2 – Create a SQL Report

  1. IMPORTANT: Make sure you log onto the server using an account with permissions to create and view reports
  2. Open SQL Report Builder.
  3. Select “New Report”
  4. Choose the desired wizard (I’m using Table or Matrix Wizard for this example)


  1. Select “Create a dataset” at the bottom of the New Table or Matrix form, and click Next.
  2. Select the “New” button.
  3. Enter a Name for the data source.  For this example, I used the name of the SCCM database, which is “CM_PS1”.  Leave the other settings as-is, and select the “Build…” button.
  4. When the Connection Properties form opens, TYPE IN the SCCM SQL Server host name (do not click the drop-down or you will grow old waiting for it to browse your network).  I prefer the FQDN, but the NetBIOS name will also work (usually).
  5. Select the SCCM database which is “CM_PS1” in my example. cmrep2
  6. Click Test Connection to verify it works, then click OK
  7. Back in the Data Source Properties form, click OK.  (Note that the 2 radio button options will be disabled while creating the Data Source, but afterwards, if you click Edit, they are enabled.  You just can’t do much with them yet.  So just leave it so the connection is embedded in the report for now)cmrep3
  8. Back in the “New Table or Matrix” wizard, you should now see one Data Source Connection (e.g. “CM_PS1”) with a sub-title “(in this Report)”.  Click Next.
  9. You should now be in the Design a Query form.  Click to expand the ‘dbo‘ schema in the left-hand Database View panel.
  10. Expand Views and scroll to see how all of the SCCM database views are visible here (or should be).  Now click on the “Edit as Text” link at top-left.
  11. Paste your SQL query statement into the top edit box and click the “!” (exclamation) link to run the query.  When it looks right, click Next.
  12. The next form is where you arrange the column structure for the table layout.  In this example, drag the ResourceID field into the “Row Groups” box.  Then select all of the fields except ResourceID and drag them into the Values box.  Click Next.
  13. Since this example isn’t using grouping or other aggregate / scalar functionality, it just a raw table output, in the next form (Choose the Layout), I unchecked both options at left, and click Next.
  14. In the “Choose a Style” form, you can change the CSS theme if you like, or leave the default as “Ocean”, and click Finish.
  15. In the Report Designer form, click on the “Click to add title” text box and change the name to something relevant.  In this case, I entered “Physical Computers”.
  16. You can do much more from here, but for now, I’m just saving the RDL file so I can import it into the SCCM reports library.  It’s recommended that you prepare or identify a common folder to save your custom report files.  It will make it easier to manage and protect them.  Once you’ve saved the report, you can close the SQL Report Builder if you like.

Step 3 – Import the Custom Report into SCCM

  1. Open the SCCM Report web site in Internet Explorer.
  2. Once you’re at the SQL Server Reporting Services home page, click “ConfigMgr_PS1” (where “PS1” is the site code, so yours may be different).
  3. If you’re like me, which I sure hope not, but in this case, maybe it’s okay, you hate the default folder organization, so I click “Details View” at the top right.
  4. If you prefer placing your custom reports into a special folder.  If so, click “New Folder” in the top horizontal menu bar, and give it a cool name.  Then open that folder (or whatever folder you wish to add your custom report into).cmrep11
  5. Click the “Upload File” link in the top horizontal menu bar.  Click Browse to locate your RDL file, select the desired file and click OK. (Note: If you go through multiple import cycles while refining the report, you may need to click the Overwrite option to avoid errors).
  6. Click OK.
  7. Your report is now ready to run!

If your report doesn’t work when you run it, you may need to update the connection assignment.  To do this, click the small down-arrow next to the report, and choose “Manage



From here, click “Data Sources” and update the connection settings as needed.

Note that this is VERY common when importing reports created by others (or downloaded from the web).  Since their connection settings aren’t portable, you will often need to change them using the above procedure, before you can run them.

Bonus – Queries the Hard Way

PS – If you really want to explore building SQL queries entirely within the SQL Report Builder, it is indeed possible.  Here’s how…

  1. When you get to the “Design a Query” form, select the fields from the tables or views you wish to merge.  In this example, I included “v_R_System“, “v_GS_COMPUTER_SYSTEM” and “v_GS_SYSTEM_ENCLOSURE” in order to pull the fields ResourceID, Name0, AD_Site_Name0, Model0, TotalPhysicalMemory0, and SerialNumber0.
  2. This is where it gets funky: In the center of the form, you will see “Auto Detect” highlighted.  Click on it to disable that link, and the itty-bitty, teeny-weeny itsy-bitsy icon to the right of “Edit Fields” becomes active.  If you move the mouse pointer over it, the tool tip will show “Add Relationship“, but if you click, you may not see anything happen.  It did something, but hid it from you.
    (NOTE: This form was developed by blind people in a cave at night.  There is no Min/Max options on the frame heading at top-right.  Just a typical red “X” to close it.  However, you can resize the form).
  3. Click the tiny-little double down arrows directly to the right of that icon.  That will expand a hidden panel for “Relationships” (you might call this an Easter Egg).
  4. Click in the empty gray box under “Left Table“.  A weird looking popup listbox will appear.  For this SQL join, the left table will be v_R_System.
  5. The box under “Right Table” should now become highlighted.  Click in that box to select “v_GS_COMPUTER_SYSTEM
  6. Double-click in the “Join Fields” box and select the little icon at top-right that looks like microscopic, hot-water-shrunk database tables.  Click on it and it adds a row in the table grid below.  The “Left Join Field” and “Right Join Field” values will be empty.
  7. Click in the “Left Join Field“.  It will display a list of the fields (columns) in the left table (e.g. v_R_System).  Select ResourceID.  Repeat this for the “Right Join Field” and select ResourceID as well (from v_GS_COMPUTER_SYSTEM).  Click OK
  8. Change the Join Type from “Inner” to “Left Outer“.
  9. Because we’re joining three (3) tables/views, we need to add another join statement, so click the microscopic icon again and assign the Left Table to v_R_System again, but select v_GS_SYSTEM_ENCLOSURE for the Right Table.
  10. Repeat the same process to select ResourceID for the left and right join fields in the popup form.  Make sure the Join Type is also “Left Outer” as was the first join.
  11. Click “Run Query” to test and verify the results.

And now you should see why it’s easier to build queries in SQL Server Management Studio.


A Retarded Bedtime Story


Once upon a time, in a land not so very far away, there lived a young man named, uhhh, Bob.  Bob was a software engineer who worked with custom CAD/CAM applications for a particular, and very large, well-respected contractor to the U.S. Navy.

One day, someone in the Navy contacted Bob and asked if he would like to demo his software to them.  Bob was given air fare and booked at a hotel, and thus, he made the journey across the continent to demo his software.  The people in the Navy smiled, shook his hands and said “we’d like to contract your company to provide this as our standard toolset. okay?”

Bob smiled, and said, “okay!”.  Bob then traveled back to his humble town.  The next day at work, Bob approached his manager about the news.  Bob’s manager called the division manager at his company and shared the good news.

The division manager replied “We’re not a software development company.  No. And you can’t do that on your own either since we own it.”

The end.


OP-ED: Why PowerShell on Linux is good for EVERYONE and how to begin


Sounds impossible, huh?

Ever since the beginning of the Monad project, in which Microsoft attempted to bring the same rich toolbox of Piped commands that Linux has enjoyed for ages, Microsoft enthusiasts have been clamoring for confirmation of PowerShell on Linux.  But it forever seemed a pipedream.

Then, in February of 2015, Microsoft announced that the CORE CLR (Common Language Runtime) was made open source and available on Linux.  As the Core CLR is the “the .NET execution engine in .NET Core, performing functions such as garbage collection and compilation to machine code”, this seemed to imply that PowerShell might be possible on Linux someday.

To further fan the fires of  everyone’s excitement, the creator of PowerShell, Jeffrey Snover–a self-proclaimed fan of the Bash shell experience in Linux– has been dropping hints of a unified management experience ALL OVER THE PLACE in the last year too.

And now today with…

View original post 900 more words

Inflection Reflection Time


It’s always interesting (to me, anyway) when a young person asks me, “Should I get into the IT field?”  Even my own kids have asked me that question.  Every time, I do the same thing:  pause, stare off into the distance, take a long inhale, and rub my chin.  Then slowly bring it back to Earth and exhale, and respond:

“It depends.”

My next step, if they don’t cut me off mid-sentence, is to ask what things they’re interested in.  That helps me associate their personae to areas of the “IT field” which might be a “best fit” for them.  For example, creative with an art perspective, or creative from a mechanical/process/formulaic aspect?

One might be better suited for applications development or UI/UX work, or possibly CAD/CAM/3D-printing/gaming, or maybe more in the infrastructure/network/infosec side.  I often generalize like that, because I’ve seen it demonstrated hundreds of times in my 30+ years in this field.  There are exceptions, of course.

Some are happier in large corporate jungles, buried in a nest of cubicals and employee handbooks.  Some are happier in a roach-infested studio in a cool part of town, near the best pizza joints and pubs.  Some are happier working from home.  Some happier being on travel most of the time.  Like any career aspect: it’s heavily influenced by personality types.

My chin-rubbing gives me time to compare, contrast and evaluate the relative pros/cons of “today vs. yesterday” characteristics.   The industry is always moving in different directions.  The place to be investing your time and energy now, won’t be the same as it will be in a year from now. Entire career paths exist today which didn’t even have a name ten years ago.

What’s Better?

Some things are much better off today than 10 or 20 years ago.  Not just the technology either.

  • IT and Business processes have matured – even if they’re still miles apart in most environments.
  • Training is more easily obtained: videos, blogs, free courses, trialware, personal virtualization, cheaper hardware, etc.  Public schools offer more as well.
  • The IT job market is more spread out than it once was.
  • There’s room for more niches within the “IT field” as well

What’s Worse?

  • IT role compression is accelerating, which has devastating impact on proactive efforts in IT.
  • The certification market is more profit focused than it once was
  • The opportunity window for learning/mastering and leveraging a new technology (from a job opportunity aspect) is becoming shorter each year
  • The “work/life balance” phrase is complete bullshit – IF – you intend to make a high income.

In general, it’s easier to obtain the tools and knowledge than it was 20 years ago.  However, business management has also used that time to impose ever-greater micro-management metrics mojo on the whole operation.  Even the app-devs (the last hold-outs from metrics-hell) are slowly being assimilated.

That said, the range of job skills will vary by geographic location and industry, which is odd that in 2016 we’re supposed to be (essentially) virtual creatures in a social media macrocosm.  Yet, the gaming and VR jobs are easier to find in the Bay area than in Kentucky, but there’s always the mobile app option.  There are other niche directions like research (medical, environmental, industrial, defense), non-profit (good for the soul, sometimes), and so on.

Is it a good idea to jump into the IT field after high school?

It depends.

(am I full of shit?  let me know – post a comment)