Author: pelo2014

Refresh scenario and LAPS

When doing reinstall of machines a common issue is that the LAPS password does not get updated due to the fact that the machine thinks the password is current. So to prevent the nice folks from Microsoft wrote a small script to reset the timer and force and update. The original blog post and script can be found here https://blogs.msdn.microsoft.com/laps/2015/05/06/laps-and-machine-reinstalls/

Now my issue with this is there is no log file created and no way for helpdesk to verify it actually happend (besides logging on which I don’t like).

So to solve this I have made an updated version of the script that centralizes logging basone if the script is run as part of MDT, Configuration Manager or Standalone.

image

Script can found on github here https://github.com/LofgrenP/Scripts/tree/master/Clear-xTSPasswordTimeStamp

Advertisements

Updating the MBAM Agent

When upgrading MBAM there are a couple things to note. Number one, if you haven’t already you should make sure you have PowerShell scripts to setup everything on the server side. This will be a nice to have since every servicerelease requires a uninstall/reinstall. And with lots of serviceaccounts, groups and what not, not automating the install will cause you headaches everytime.

The next issue is that the agents needs to be ugpraded everywhere. Now there is no panic to upgrade the agents on the machines as the old version will keep reporting to a newer server release. However to benefit from all the bugs and security fixes the update should absolutley be deployed.

Now the issue, when you run the MSP on an already patches systems (let’s assume you already have a service release installed) nothing happends. Well this is due to the fact that the MSP only upgrades from version 1.0.0.0 as can be viewed in the MSP itself. Below is from the x64 patch of the september release.

image

Now to fix this the easiest way is actually to create a wrapper around the MBAM agent installer and use that to install the agent. That way if there is already and agent installed we can uninstall it and reinstall the patched version. And if there is no version installed we can go ahead and install the version.

All of this can be done using PowerShell and checking the registry for known keys.

image

To save you the time on creating your own script to solve this you can use mine. It can be found on github overe here https://github.com/LofgrenP/Scripts/tree/master/Install-MBAMClient

Now the script requires a simple folder structure looking like this

image

Now in the source folder you place the MbamClientSetup.exe and the patch file from the latest servicingrelease. The script is prepped for the current September 2017 release.

Next up is you run the script as a administrator on a box, or run the script as part of a package in Configuration Manager, an application in MDT or basically however you want.

The logfiles will switch location based on if its run as part of Configuration Manager, MDT or standalone. The end result will always be the same!

Happy upgrading!

/Peter

Redistributing failed content

When working with ConfigMgr you always end up distributing content to several DPs. This normaly goes off without a hitch but from time to time this fails. If you then have several DPs spread across a large geographical area, WAN links may be questionable. So when a package then fails most are not happy to redistribute the content to all DPs again.

image

So to help sort that out the following script can be used. The script will find all failed packages on a specified DP and then redistributed them to that single DP only. All that is needed is to run in on the Primary site server / server with SMS provider and specifiy your SiteCode and DP FQDN.

The script is available on Github over here: Update-ContentForSingleDP

Happy deploying!

/Peter

NIC 2018 – Future Edition

So NIC has come to and end and I was fortunate enought to be selected as a presenter for not one but two session. My first session was about whats changed in Windows 10 in the 2 years since its first release and how to keep up with the changes. The second session was a co-presented session with @nickolajA from SCconfigMgr.com about using community tools during OSD in ConfigMgr and MDT.

I had a blast presenting this and you can now find the presentations from those session (and others) over on github github.com/nordicinfrastructureconference/2018

If you want a some more details about what me and my coworkers did at NIC check out this post http://www.scconfigmgr.com/2018/02/07/nic-2018-future-edition/ from @modaly_it

image

SQL Server Inventory using ConfigMgr

The hardware inventor feature of ConfigMgr helps us discover and track several items. One recurring item is SQL Server and by default there is no nice way of tracking this. Basically the only option out of the box is using the application inventory to find where SQL server is installed. However this usualy results in a large list with mixed servers and clients and you still have no idea if its running SQLExpress, Standard or Enterprise edtion.

So what can we do to fix this besides buy a third party solution. Well the good thing is that SQL stores all the information into WMI, this means we can track editions, installations and versions with a bit of trickery with the hardware inventory engine.

There are number of steps included here at it is important to follow all of them for a successful result.

Step 1 – Update Configuration.mof

The first part of this is updateing configuration.mof. This file will control classes that inventoried and then index into the database.

The file can be a bit tricky to find if you have never done this before. You need to go to this location <Your ConfigMgr Install folder>\Inboxes\clifiles.src\hinv

In here is the configuration.mof file, now first make a backup copy of the file in case you want to revert the changes later and don’t want edit the file again.

At the very end of the file there is a section for adding custom extensions.

//========================

// Added extensions start

//========================

//========================

// Added extensions end

//========================

In between these block we need to add the following

//———————————————

// SQL 2017 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement14”}, dynamic,
Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql17

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2016 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement13”}, dynamic,
Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql16

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2014 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement12”}, dynamic,
Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql14

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2012 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement11”}, dynamic,
Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql12

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2008 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement10”}, dynamic,
Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql08

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2000/2005 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement”}, dynamic,Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql2kand05

{
[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;
[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;
[PropertySources{“PropertyName”},key  ] string PropertyName;
[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;
[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;
[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;
[PropertySources{“ServiceName”},key   ] string ServiceName;
[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

Save the configuration.mof file

Done! On to step two.

Step 2 – Import hardware inventory information

The next step is done in the console and you need some nice permissions to do this. You will need to edit the default client settings object.

First thing is first. So we need to create a custom mof file to import. To do this, copy the following and save it as SQLProperties.mof (pick a nice location, like the desktop).

//=================SQL 2017 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL17 Property”),

SMS_Class_ID(“CUSTOM|SQL17_Property|1.0”)]

class cm_sql17 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2016 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL16 Property”),

SMS_Class_ID(“CUSTOM|SQL16_Property|1.0”)]

class cm_sql16 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2014 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL14 Property”),

SMS_Class_ID(“CUSTOM|SQL14_Property|1.0”)]

class cm_sql14 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2012 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL12 Property”),

SMS_Class_ID(“CUSTOM|SQL12_Property|1.0”)]

class cm_sql12 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2008 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL Property”),

SMS_Class_ID(“CUSTOM|SQL_Property|2.0”)]

class cm_sql08 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//==================SQL Information 2000 and 2005

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL Property Legacy”),

SMS_Class_ID(“CUSTOM|SQL_Property_Legacy|2.0”)]

class cm_sql2kand05 : SMS_Class_Template

{
[SMS_Report(TRUE)    ]  boolean IsReadOnly;
[SMS_Report(TRUE),key]  uint32 PropertyIndex;
[SMS_Report(TRUE),key]  string PropertyName;
[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;
[SMS_Report(TRUE)    ]  string PropertyStrValue;
[SMS_Report(TRUE)    ]  uint32 PropertyValueType;
[SMS_Report(TRUE),key]  string ServiceName;
[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

Next, head on over to the Administration workspace in the ConfigMgr console and find the Client Settings in the menu to the left. Open up the Default Client Settings and check the Hardware Inventory tab.

There is a nice little button named Set Classes, hit that and then hit import. Browse to the SQLProperties.mof file and hit Open.

You will be greated by this screen where you need to make sure the Import both hardware inventory classes and hardware inventory class settings is selected

sqlinv02

If you are like me and don’t want this inventory to hit each and every device you have deselect the following classes in your default settings. If you are only using the default client settings object, please skip to the next step.

sqlinv01

If you are using custom client settings, please add the above classes to be included in hardware inventory under the same tabe as we did the import.

This means SQL information will now be imorted into the database! We can now create collections based on this, please do leave enough time for hardware inventory to run on each client before you find data in the database.

Step 3 – The Report

For me the last piece of the pussel is to create a custom report to easily show me the information needed. I will not cover the details of how to create a custom reports as the options are plenty. However I will share the query used to get a nice detailed view with the server and edition info.

Note that this is a simple report and can be extended in a lot of ways and if you are already using PowerBI you can extract the same data that way.

Query used:

— SQL 2017

select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql6.PropertyName0 when ‘VERSION’ then
(case
when sql6.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql6.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql6.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql6.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql6.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql6.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql6.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql6.PropertyName0 when ‘SKUName’ then
sql6.PropertySTRValue0 end)  as [SQL Type]

,max(Case sql6.PropertyName0 when ‘SPLEVEL’ then
sql6.PropertyNUMValue0 end)  as [SQL Service Pack]

,max(Case sql6.PropertyName0 when ‘VERSION’ then
sql6.PropertySTRValue0 end) as [SQL Version]

,max(Case sql6.PropertyName0 when ‘FILEVERSION’ then
sql6.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_SQL17_Property0 sql6 on sys1.ResourceID=sql6.ResourceID

where

sql6.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

union

— SQL 2016

select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql5.PropertyName0 when ‘VERSION’ then
(case
when sql5.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql5.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql5.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql5.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql5.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql5.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql5.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql5.PropertyName0 when ‘SKUName’ then
sql5.PropertySTRValue0 end)  as [SQL Type]

,max(Case sql5.PropertyName0 when ‘SPLEVEL’ then
sql5.PropertyNUMValue0 end)  as [SQL Service Pack]

,max(Case sql5.PropertyName0 when ‘VERSION’ then
sql5.PropertySTRValue0 end) as [SQL Version]

,max(Case sql5.PropertyName0 when ‘FILEVERSION’ then
sql5.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_SQL16_Property0 sql5 on sys1.ResourceID=sql5.ResourceID

where

sql5.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

union

— SQL 2014

select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql4.PropertyName0 when ‘VERSION’ then
(case
when sql4.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql4.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql4.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql4.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql4.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql4.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql4.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql4.PropertyName0 when ‘SKUName’ then
sql4.PropertySTRValue0 end)  as [SQL Type]

,max(Case sql4.PropertyName0 when ‘SPLEVEL’ then
sql4.PropertyNUMValue0 end)  as [SQL Service Pack]

,max(Case sql4.PropertyName0 when ‘VERSION’ then
sql4.PropertySTRValue0 end) as [SQL Version]

,max(Case sql4.PropertyName0 when ‘FILEVERSION’ then
sql4.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_SQL14_Property0 sql4 on sys1.ResourceID=sql4.ResourceID

where

sql4.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

union

— SQL 2012

select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql3.PropertyName0 when ‘VERSION’ then
(case
when sql3.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql3.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql3.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql3.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql3.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql3.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql3.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql3.PropertyName0 when ‘SKUName’ then
sql3.PropertySTRValue0 end)  as [SQL Type]

,max(Case sql3.PropertyName0 when ‘SPLEVEL’ then
sql3.PropertyNUMValue0 end)  as [SQL Service Pack]

,max(Case sql3.PropertyName0 when ‘VERSION’ then
sql3.PropertySTRValue0 end) as [SQL Version]

,max(Case sql3.PropertyName0 when ‘FILEVERSION’ then
sql3.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_SQL12_Property0 sql3 on sys1.ResourceID=sql3.ResourceID

where

sql3.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

union

— SQL 2008

Select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql2.PropertyName0 when ‘VERSION’ then
(case
when sql2.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql2.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql2.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql2.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql2.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql2.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql2.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql2.PropertyName0 when ‘SKUName’ then
sql2.PropertySTRValue0 end) as [SQL Type]

,max(Case sql2.PropertyName0 when ‘SPLEVEL’ then
sql2.PropertyNUMValue0 end) as [SQL Service Pack]

,max(Case sql2.PropertyName0 when ‘VERSION’ then
sql2.PropertySTRValue0 end) as [SQL Version]

,max(Case sql2.PropertyName0 when ‘FILEVERSION’ then
sql2.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_sql_property_2_00 sql2 on sys1.resourceid=sql2.ResourceID

where

sql2.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

union

— SQL 2005

Select

sys1.Netbios_name0 as [Computer Name]

,max(Case sql1.PropertyName0 when ‘VERSION’ then
(case
when sql1.PropertySTRValue0 like ‘9.%’ then ‘SQL 2005’
when sql1.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql1.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql1.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql1.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql1.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql1.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]

,max(Case sql1.PropertyName0 when ‘SKUName’ then
sql1.PropertySTRValue0 end) as [SQL Type]

,max(Case sql1.PropertyName0 when ‘SPLEVEL’ then
sql1.PropertyNUMValue0 end) as [SQL Service Pack]

,max(Case sql1.PropertyName0 when ‘VERSION’ then
sql1.PropertySTRValue0 end) as [SQL Version]

,max(Case sql1.PropertyName0 when ‘FILEVERSION’ then
sql1.PropertySTRValue0 end) as [SQL CU Version]

from v_r_system sys1

left join v_gs_custom_sql_property_legacy_2_00 sql1 on sys1.ResourceID=sql1.ResourceID

where sql1.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)

group by sys1.Netbios_name0

This might not be the prettiest query every created or the smartest way (there are alot smarter people out there) so don’t go flaming it’s not optimized. It does the job : )

/Peter

PowerShell remoting – source vs destination

Powershell remoting, the ability to remotley manage and influence devices is a key part of PowerShell. By default PowerShell remoting is enabled on servers and one of the lazy “security” features you can use is to only allow connections from specified networks.

In a domain network this is very easy to do, all you need is a small group policy that sets the networks. There is one thing you need to note, that is not really documented.

The Group Policy

The group policy is a regular computer policy setting found under “Computer\Policies\Administrative Templates\Windows Components\Windows Remote Management (WinRM)\WinRM Service” and the actual poliy item is named “Allow remote server management through WinRM”

When you enable this policy you can enter networks the server will consider trusted.  So lets say you have your server on the network 192.168.1.0, that would mean you set the filder to 192.168.1.1-.192.168.1.254.

The issue

What you need to consider is the following. Say you have servers on two seperate networks and one is considered management network, lets say it uses 172.16.1.0/24. This means you now specify the management network as approved. But the servers you are accessing is on your primary server network 192.168.1.0/24. You have to specifiy both source and destination networks for this to work as this also controls the actual listners and if you only specify the 172.16.1.0/24 network the listners on the servers on network 192.168.1.0/24 will not be enabled.

The solution

For the scenario above make sure to specify both networks in your GPO. So the filter will be set to 192.168.1.1-192.168.1.254,172.16.1.1-172.16.1.254

winrm

/Peter

Strange Case of the failed Remote Desktop Gateway

I have been having a kind of a strange issue for a longe while and finally got around to troubleshooting and figuring out why so here is a breakdown of the strange case of the failed remote desktop gateway connection.

The setup is as follow, using diffrent machines Windows 10, 8.1 and 7 to connect to diffrent remote desktop gateway solutions and for some reason the connection failed with wrong username and password to a couple of the RDGWs.

So after som digging and alot of help from my good friend over at isloation.se we found the issue and how to check if you are also running into this issue.

The check

On the RDGW check under incomming connections/monitoring and if the connections display as RPC-HTTP. This is the clue that something is not all right.

rdgw1

The cause

So over the years the has been alot of fixes and version of the RDP client for each version of Windows. In the later release support was introduced for RDP over HTTP with UDP as a performance booster. However due to bugs and other things happening there have been posts and information about setting a registry value to make RDP work again. The value is called RDGClientTransport. It is a DWORD and you set it to 1 to get everyting working again.

However what this does is force the RDP client to fall back to RPC-HTTP connections instead of HTTP/UDP. This in turn can cause connection issues if you have modern solutions.

The solution

Depsite several blogs and forum posts saying this the value should be 1 you really should change the default value of 0.

So here is the full path to set the value.

HKEY_CURRENT_USER\Software\Microsoft\Terminal Server Client\RDPClientTransport

As you probably noted it is a current user setting and since I am very lazy. Most of my configuration is done through either Configuration Manager or GPO so in this case it was a GPO setting the value to 1 for all my users. Hence the problem was only on my devices and not my coworkers devices.

rdgw2

/Peter