Finding the right SQL view for your SCCM report

One of the banes of many SCCM admins existence is reporting. Some SCCM people are SQL guns, others, like me, know what they need to to get by, but a strong SQL understanding – that just isn’t me (and i know i’m not alone!).

One of the key things with SCCM reports, and something that some consultancies we deal with ignore is the requirement to use views for your reports, not directly talk to the tables.

The official SCCM doco dances around this and doesn’t explicitly state it (even though it does reference views constantly, it does not specifically state that using tables is unsupported – and it should)

https://docs.microsoft.com/en-us/sccm/core/servers/manage/creating-custom-report-models-in-sql-server-reporting-services

https://docs.microsoft.com/en-us/sccm/develop/core/understand/sqlviews/sql-server-views-configuration-manager

 

These posts are far better at laying it out as it is

https://www.enhansoft.com/what-are-the-supported-sql-server-views-to-use-with-sccm-reporting/

https://www.enhansoft.com/why-is-it-important-to-use-supported-sql-server-views-with-sccm-reporting/

 

The TL;DR version of the ehansoft articles is:

  • The SCCM reporting services account is specified during setup and is granted access to all the views – but not the tables etc
  • The 2nd enhansoft article lays out why views are utilised instead of tables – and the associated benefits
  • You can grant datareader access to accounts over the SCCM database to get around this – but its officially (MS) unsupported, and can cause a range of issues (again, laid out in the 2nd article)
    • There are people out on the web that recommend just allocating permissions – even some MVPs – which doesn’t help
  • My recommendation is to remain in “supported” territory and update the report to use views rather than tables directly (knowing full well that we have all referenced tables directly at some point in our careers!)

 

A couple of things i have found handy with this are:

 

When you know the table you wish to reference, but aren’t sure which views you can use (and you want to do the right thing and use a view)

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION like ‘%tablename%’ OR VIEW_DEFINITION like ‘%tablename%’ OR VIEW_DEFINITION like ‘%tablename%’

 

When you can find what your looking for in the database

https://www.apexsql.com/sql-tools-search.aspx

 

 

Draytek – central AP management – not so great

I grabbed a couple of Draytek Vigor AP902 access points to work in conjunction with my Draytek Vigor 2925 router and Draytek Vigor 910c access point.

I dont mind the Draytek gear in general – for a home setup it is sufficient… however i did find i needed better wireless range thought that 3 AP’s with roaming would be a good fit…. and Drayteks central management swayed me towards getting an all-draytek solution. (as if you can manage 3 AP’s centrally, why wouldn’t you?!)

In short – it was/is a disappointment. The central AP management interface, accessed via the vigor 2925 is quite basic and there is no feedback indicating if applying a configuration to AP’s has been successful or not (for example).

The biggest issue however is that my PSK was applied incorrectly to all access points – this became apparent when all devices lost connectivity, despite the PSK being “the same”

When logging in directly to one of the access points to troubleshoot, i found the PSK in clear text, missing the last 5 characters. This was both good and bad…. bad as it was displayed in clear text, but good as it made the issue very clear.

From there, i simply updated the PSK on each individual AP, each with a 2.4 and 5.0GHz SSID, so 6 updates – and all was OK. Then disabled central management on the 2925.

If someone else is thinking of going for an all draytek solution for the same reasons…. my impression at the moment is – don’t do it.

I’ve emailed draytek support – and will update this post with their reply.

 

*** Update 07/01/2019 ***

I got a reply from Australian Draytek support – initially i got told “it works fine” – when i replied asking if a WPA2 key with certain characters in it, it does look like that a WPA2 key with “&” in it cannot be managed via the Vigor 2925 central management

Microsoft Partner technical support

Its fair to say I’m not a fan of Microsoft support.

Premier support is anything but premier, and partner’s are completely unsupported, in fact, Microsoft products in general fall under “effectively un-contactable” if you don’t have premier, and if you do have premier they still fall into “effectively unsupported”… and if it wasn’t for community support, they would be completely unsupported.

But there are phone lines you can call and forums…. yes… but

<listen to attached audio, its a little soft, so you may have to turn your volume up>

https://mspartner.microsoft.com/he/il/pages/support/partner-technical-services-contact-information.aspx

Keep in mind these are the same technical advisory hours that Microsoft employs a call centre to actively ring partners and query why they are not using them.

This is, unfortunately, pretty standard for Microsoft. Its just so baffling disappointing that they are so focused on sales, that they don’t support their products.

SCCM console cannot connect from Windows 8.1

SCCM admin, console works from all machines….

try a 8.1 machine at a remote site, “Configuration manager cannot connect to the site (FQDN of primary)”

Found this helpful article

https://sccmfaq.wordpress.com/2013/10/22/sccm-2012-r2-console-on-windows-8-1-cant-connect-to-sms-provider/

 

Basically dump these entries into a text file, rename to .reg and import….

 

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\QueryProcessors\WQL]
"Assembly Path"="C:\\Program Files (x86)\\Microsoft Configuration Manager\\AdminConsole\\bin\\AdminUI.WqlQueryEngine.dll"
"Depends On"=""
"Type"="Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlConnectionManager