Get Installed Software Remotely

Much like my 80 year old Grandmother, there is a problem I regularly run into. Fortunately in my case it doesn’t involve a car and a garage door. It’s quickly getting a list of software from a remote computer. Also in my case, running into this issue for the third time didn’t result in my driver license being revoked. But, just like my Grandmother, I thought it wise to change my approach nonetheless 🙂

Throughout my career there are times when I want to get a list of software from a computer, or number of computers. This could be due to an audit, or pure curiosity as to what that awful piece of software one of my colleagues was using. (“Rhianna’s Music Quiz”… Nice one Mitch). As always, I’m following best practice and using a non administrative account so want to also use alternative credentials when querying the remote computer.

At first glance, the best approach here I’ve determined is WMI. On initial investigation the Win32_Product class seems like the perfect fit, running it locally I get a list of software, and it seems to give me a number of useful variables. Unfortunately, it takes a long time to run.. well 30 seconds, and that’s an age for us impatient Gen Y’s. Running it remotely this can blow out to minutes. So if you want to run a command like this across multiple computers… You’ll be waiting a while, and I’ve got important things to do! (like sending my mates a Snapchat of me as a Lemur). Apparently, the issue has something to do with the Win32_Product query performing a validation on all of your software, it get’s quite technical, a zoned out reading the reasons, drooled a bit and moved on.

So enter the registry. If you query HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall you can see all of your installed software there. However, only the 64bit software. The 32bit software is buried under the Wow6432Node of the registry, and most of the subkeys have lovely GUID’s so it’s quite the job working through that manually. But, after figuring out the logic and putting a script together, it would run in seconds. A win!

The best way to query the registry in my opinion, is to use WMI as you can then specify alternate credentials. If you are on the edge of your seat reading this then treat yourself to an earlier blog covering off just this thing. It’s bloody good I tell ya…

Anyway, after some coffee, some script munging, and a lot of trial and error, I produced the below script. It will accept a ComputerName and Credentials parameters then return all 32 and 64bit software from the remote box. It also includes the UninstallString so you can easily uninstall remote software, I’ll cover that off in another post, so you can actually get rid of that annoying software from your colleagues computer…

And here is an example of it in action!

 

 

 

Get Unique Permissions on a shared Folder

I was assisting a wise bearded, bespectacled, beer brewing, bugger the other day (lets call him Burko) with a common IT problem of identifying unique permissions on a shared folder. Much like Burko himself, the folder appeared to be in a dodgy state, most likely due to cancel being clicked when permissions were being propagated, because it takes so long. If it takes longer than 2 minutes, it’s just not worth doing, pretty sure I saw that on a motivational poster somewhere.

Given the ridiculous amount of sub-folders from years of uncontrolled sprawl, I was tempted to have Burko do it all manually just to test his sanity. But being the top guy I am, I got stuck into a bit of PowerShell to better manage the process more accurately. That and I couldn’t help myself.

So without further ado (did you know that comes from Shakespeare, man I am so cultured!), here it is!

An example of this running is as follows:

Hopefully this is of use to someone!

Questions, comments, abuse are always welcome. Don’t ask about beer brewing though, I’ve been asking for years to no avail.

Azure Automation – Running an on-premises PowerShell Script

Ramblings

So I thought it was about time to get stuck into Azure Automation. I am a fan of the product in theory, but have yet to delve into the details and figure it all out. Currently we run a number of on-premises powershell scripts on a number of servers performing various tasks. They get the job done, but in a decentralized way with limited visibility.

Having a product like Azure Automation storing, versioning, reporting, and of course, executing scripts would definitely add value, and it’s about time I get out of my old habits.

It’s not like me to start a blog straight into techie stuff, so here is my favourite joke…

I went to a Zoo and all they had was a dog.. it was a Shitzu

Ok that works better when spoken than written down… Now into it!

Setting  up Azure Automation

  1. First up log on to your Azure Portal
  2. Click Add –> Monitoring + Management –> Automation
  3. Then set up an Automation Account. It needs some basic stuff, that is a name, subscription, resource group, and location.
  4. Click Create!
create-automation-account
Create the Automation Account

 

Create Operational Management Suite Workspace

If you don’t already have an Operational Management Suite account, create one as follows

  1. https://www.mms.microsoft.com/Workspace
  2. Complete the registration details to create an account
  3. Associate this with your Azure Active Directory

Deploy the Automation Solution in your OMS Workspace

  1. Log into your Operational Management Suite portal (Created above)
  2. Click Solutions –> Automation & Control to deploy the Automation Solution,
deploy-automation-solution
Add Automation and Control
  1. Click Add to then deploy the solution.
  2. Alternatively,  If a configure workspace button is only visible, click this first and associate it with your Resource Group and Automation Account created earlier. After which you can then Add the solution

Install Microsoft Monitoring Agent

  1. On an on premises machine you want to use as a Hybrid runbook worker, install the Microsoft Management Agent
  2. Login to your OMS Workspace https://yourworkspace.portal.mms.microsoft.com
  3. Click on Settings –>Connected Sources–>Windows Servers
  4. Download the appropriate Windows Agent.

    Download 32 or 64 bit Agent. Take note of Workspace ID and Primary Key for later use
  5. Kick off the install of the agent on your designated server.
  6. Ensure you check the box “Connect the agent to Azure Log Analytics (OMS)”
  7. Enter the Workspace ID and Workspace Primary Key specified in your OMS portal (see above screenshot).
  8. Complete the installation.
  9. Go to Control Panel –> Microsoft Monitoring Agent
  10. Click on the Azure Log Analytics (OMS) tab and verify connection is succesful
    microsoft-monitoring-agent-properties
    Verify agent has successfully connected to OMS

     

Create Hybrid Worker Group and connect agent to group

  1. Head back into your Azure portal
  2. Find your Automation Account created earlier (cloudy in my case)
  3. Click on the Keys Icon under essentials and take note of the Primary Access Key and URL
keys
Azure Automation Account Keys
  1. Jump back on the server with the Monitoring Agent installed that you wish to use as your Hybrid Worker
  2. Start and Administrative Powershell Session and enter the following cmdlets to connect to Azure Automation

  3. Use the URL and Primary Access Key from your Automation Account
  4. For Group Name, enter any name you wish. This is the Hybrid Worker Group that will exist in your Azure Portal. If the group doesn’t exist it will be created.
  5. You should now be able to see the new group in the Azure portal under your Azure Automation Account!
Finally, the Hybrid Worker Group is created!
Finally, the Hybrid Worker Group is created!

Create a Powershell Script already!!!

  1. Now we are good to go! Jump back in your Azure Portal
  2. on the Automation Account Click Runbooks –> Add a runbook –> Create a new runbook
  3. Call it RestartWorker with the type set to PowerShell
  4. Click Create
add-runbook
The all powerful new script!
  1. Wait while it’s created, then once completed enter type the cmdlet:

  1. Click Save
  2. Click Test pane
  3. Select to run on a Hybrid Worker
  4. Select the Hybrid Worker group we created earlier
  5. Click Start
  6. All going to plan, your server has just restarted!
runbook-results
Runbook Results

And that’s it!

I know… thats an awful amount of work to restart a computer 🙂 But now we have the base down to do some more advanced scripting shenanigans! I hope this is of use to someone.

Gaining insight from consolidating and summarising Windows Event Logs.

Trawling event logs, man I’m excited. This is the kind of stuff I dream about and it’s what gets me going in the morning. I can’t wait to get stuck into millions of lines of events with the same error innocuous occurring every hundreds of times a second. With that one critical alert buried in all the noise.

I love it as much as I love grating my knuckles on a cheese grater.. Wait a minute, that sounds awful, hang on reviewing event logs is equally awful!

Now that I’ve wasted your time with my pointless preamble, here is how I’ve made things better. In my years reviewing event logs, I’ve taken a few learnings.

  1. It’s tedious
  2. It’s really tedious
  3. I want the information summarized
  4. I want to know if the error is new, or re-occuring.

Yes I can do all the above stuff manually, but why on earth would anyone want to! So I used powershell to improve things.

Let me set the scene. You’ve just patched a critical server and it’s been rebooted. Some of the first things you should review (yes really you should) is the windows system and application event log. So we RDP, open the event log, clikety clickety click and all is well. Sweet, move on to another 50 servers… Well that sucks. So here is a better way.

I produced the following script to do the following.

  1. Get all System and Application log events that are not Informational, so thats the Critical, Error, and Warning events
  2. Group them. If a certain event has repeated itself 1000 times, just tell me once and let me know how many times it appears
  3. Based on item 2, let me know the last time it appeared
  4. Was this event present before I rebooted the server (let’s look back 7 days as a line in the sand)
  5. Who on earth wants to log on to a server, let it take a ComputerName parameter instead
  6. Alternate credentials need to be specified as well as hey you’re running a best practice shop right 😉

Ok enough talk here is the script, hopefully it makes some sort of sense:

And the result?

 

powershell-get-event-log-stats-example
A thing of beauty! A summarized at a glance view of the Windows event logs, to drive better insight and agile decision making in your environment. Strategic staircases, disruption, top down bottom up view, managerial speak etc.. etc.. Essentially, it good bro.

And there you go, lives saved! (from boredom that is, I’m not some kind of hero.. or am I??)

Hopefully that is of use to somebody, in a later blog I will talk about how you can display stuff like this in fancy HTML pages styled with bootstrap. I find that stuff so much cooler than grating your knuckles.

Cheers!

Get SharePoint List Contents with Powershell using Client Side Object Model

So I had a situation the other day where I needed to get a SharePoint list exported as a CSV file on a regular occurance.

Some background, I was working through a process identifying critical information in SharePoint we would like on hand in the event all servers were down (i.e. a brown pants moment). While we have this pretty much sorted for various word and excel documents, there were a number of SharePoint lists that would also be invaluable, and I was loathe to turn these into a static excel spreadsheet instead.

Our current process is a manual one where the list is opened in Excel then saved as a CSV. As this requires human effort, humans get complacent, and doing repetative things over time get done poorly, shortcuts taken, shiny things to distract you, etc.. etc, and isn’t that one of the reasons we use computers?? Where was I going with this again? Ohhh look a Blue Car!

Right back on track… So I performed some research, got distracted, got a coffee, then got back to it.  A number of solutions exist which rely on the SharePoint powershell cmdlets that are installed on the SharePoint server, but do not come stand alone for a client. So that discounted them, also in our environment we are are not using PSRemoting. I’m also not a fan of running scheduled scripts on our SharePoint server unless absolutely necessary.

Then I stumbled across the SharePoint Client Side Object Module. Here I could manipulate SharePoint directly from my PC utilizing API calls on HTTPS. It sounded like just the ticket!

I dutily downloaded them: https://www.microsoft.com/en-nz/download/details.aspx?id=35585

Got a little confused reading the basic operations MSDN stuff https://msdn.microsoft.com/en-us/library/office/fp179912.aspx. This was in C# but translates fairly easily to Powershell with a bit of force.

Finally, after a bit of cursing, caffine, and luck I got a result! I wrapped it up in a function, then called it to my hearts content. Essentially it takes any SharePoint list and returns it as a table that I can then export to CSV.

Here is the Powershell:

Hopefully someone finds that useful, enjoy 😀

 

Setting Proxy settings in Powershell and .Net Windows Applications

There is a problem I have come across a number of times in my career that frustrates me. But I’m no closer to getting a full time barrista on tap to serve me coffee any time I need it. FML as the kids say..

There is also another problem, thats maybe more in line with my blogs so I suppose I should write about that instead!

A forward web proxy server, is a common piece of technology in most organizations where I’ve worked. It’s how we control the minions to ensure they only access the content on the internet we deem acceptable, monitor where they are going and how long they can browse a website etc.. Dance my puppets, muhahaha. I’m not going to argue the pros and cons of proxy servers, but needless to say they are prett standard.

What does bug me, is when business applications come along that say sorry our software doesn’t work through a proxy server… Oh nice one, you never though an application used by businesses may have a proxy server in the mix. Winner… Anyway, I’ve come across sites where there are holes open in the firewall for all manor of websites/ip address ranges etc. because its too hard and the easy out is to just poke a hole in the firewall and say “have at it!” Sure it’s a quick win, and gets you back on to more important things, like what the minions are browsing today. But it doesn’t cut the mustard for me (thats an odd saying, where on earth does that come from…quick google… nothing concrete.. oh well back to blog). Unbeknownst to a number of people, and even the “developers” that write these random third party desktop applications. There is a way to work with the proxy server in some scenarios using application settings.

This process has worked for me approximately 75% of the time, essentially it tells the application to use the proxy settings specified by the user. i.e. what has been configured in IE. I’ve tested it with IP, basic, and NTLM authentaction succesfully.

How to make windows desktop applications work with a proxy server

  • If it’s a modernish windows application. We can assume it has been built on the .NET Framework.
  • If you can search the internet using Internet Explorer via the proxy (that is, it has proxy settings specified somehwere), then thats another tick
  • Navigate to the program files directory. e.g. c:\program files (x86)\CrapVendor\CrapApp
  • Look for the executable (e.g. crapapp.exe)
  • An application settings file will typically have the same name as the executable but end in .config (e.g. crappapp.exe.config)
  • If one exists, great edit it
  • If one does not exist, then create a new file with that name (crapapp.exe.config)
  • Add the below settings

  • If the file already exists, then you may only need to add the defaultproxy line, just ensure its in the system.net tags
  • Save the file, and fingers crossed it now works
  • Job Done! Grab a coffee.

Check SQL Backups to Disk

Recently, I was tasked with a job to ensure all of our SQL Servers were configured to backup all databases to disk, and that all of these backup files existed in their backup location.

As we had over a dozen SQL server with multiple databases per SQL server this was not a task to be done manually.

So, I created the following script to get the job done. Basically it works as follows:

Check-SqlBackups -ComputerName SQLSERVER -SqlInstance INSTANCE -UserName USER -Password PASS

SQLInstance, Username, and Password are optional parameters.

This will enumerate all databases on SQLSERVER and  for each database tell me the state of the backup. Was it backed up to disk, was a Snapshot taken (e.g. via VSS) etc. An example of the result is as follows

There is still a bit more work I could do this to enumerate all SQL Instances on a SQL Server, fortunately these are few and far between.

Saved a world of pain 🙂 Hopefully it is of use to someone else, if not, it is a least in and handy place for me to get hold of again! 🙂

 

 

Manipulating the registry remotely with alternate Credentials

As an IT pro I frequently need to read and write to registry keys on remote computers, either ad-hoc or via script. Sure I could use Regedit, or RDP to the server in question, but that involves a lot of clicking, and to be honest, moving my right hand to my mouse seems like such hard work 🙂

I  though I’d show you a number of ways of doing this, as well as their limitations, as well as my personal favourite.

Option 1 – Get-ItemProperty

The Powershell cmdlet Get-ItemProperty can be used in conjunction with Invoke-Command to execute a command on a remote computer.

The nice thing about this command is you can also specify alternate credentials. However, it does require that WsMan is correctly configured for powershell remoting to work. Which, 9 times out of 10 in most environments it is not.

Option 2 – The Microsoft.Win32.RegistryKey Class

The Microsoft.Win32.RegistryKey class is another way of accessing registry settings remotely. An example using this method is as follows:

This is more likely to work as it is not reliant on WsMan being configured. However, it does require the RemoteRegistry service to be running on the target computer. Which, by default is not. Also, there is no way to specify alternate credentials, which can present a few problems depending on the computer you are talking to. e.g. non domain-joined machines mounted on the back of a 42″ LED TV mounted 5 meters off the floor… Quite a specific example there.

Option 3 (preferred) – WMI

My final and preferred option is using WMI. They beautiful thing about this method is WMI is typically available (I’m my experience) in most environments, also it accepts alternative credentials and is not reliant on the RemoteRegistry service.

You will be prompted for alternate credentials when running this script, if you wish these can be hardcoded, although I strongly discourage saving passwords as plain text.

You can view all the required methods of the StdRegProv WMI class on MSDN here: https://msdn.microsoft.com/en-us/library/aa393664(v=vs.85).aspx

The only downside to this option was it took me a while to stumble across it!

I hope this is of use , thanks for taking the time to read my blog!

Copying Powershell Output to your Clipboard

Just a real quick tip I’d like to share. When dealing with data I find it really handy to take my PowerShell output and then manipulate this in Excel. Traditionally I’d been using the Export-Csv Cmdlet. While this would work, it would require me to save a CSV file, then open this in Excel. Occasionally this can be a pain when I’m remotely connected to another computer and transferring the CSV file becomes an issue.
The better way!, just use clip.exe. This handy executable will take whatever output you give it and save it to your clipboard. Using it in conjunction with the ConvertTo-CSV Cmdlet, and all you need to do is paste your data straight into Excel. E.g: