Hi, I have a simple SCCM query like the one below, and I would like to customize the result column headers. Oddly enough, the column headers are "System Resource.Resource ID", "System Resource.Name" and "User Device Affinities.User Name", so there is already some name translation going on, but I would just like to have "ID", "Device" and "User" as column headers.

SMS_R_System.ResourceID,
SMS_R_System.Name,
SMS_UserMachineRelationship.UniqueUserName
from SMS_R_System
inner join SMS_UserMachineRelationship on SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceID

What is the correct syntax for this ? If I add for example SMS_R_System.ResourceID as 'ID' I get a syntax error.

St

Similar discussion here (although for SCCM 2007) https://social.technet.microsoft.com/forums/systemcenter/en-US/40fdfc05-d2d3-4ca3-8224-54d9f1f60783/custom-query-alias-for-column-name  

Seems that it is not possible. I don't think anything has changed in SCCM 2012.

I saw that thread, I was hoping it might have been possible in SCCM 2012R2. Can anybody confirm for sure ?

Thanks for your time and effort Peteris,

Stephane

Use SQL instead. It performs much better when it comes to a large amount of data returned and you can customize column names.

It not supported and I'm not sure why you would want to do it in WQL anyways.

Why change the column names? Why not use Reporting?

I find the queries to be much lighter and easier to export than going to reports, selecting (or searching for) a report, opening the report, entering the query values, view the report, export to excel, open the file in excel.

In the query I can just double-click the query, and Ctrl+A Ctrl+C the results and paste in Excel. I don't even need to save if it's just for some simple copy paste into a ticket or document.

I have not noticed a considerable performance difference between the two regarding the execution.

I'm still learning what works best, so thanks for your input.

I would definitely want to propose this as a feature request...

Stephane

By the way, there is a default report for device affinity, but it's horrible to work with as is, which is why I tried to find something lighter in the first place. You can export the report to excel, but there are multiple lines per item, so if you want a clean list, you have to save as CSV and then manipulate/delete columns in excel.

Stephane

Did you know that you can query CM12 directly from Excel?

There is more overhead using WQL vs SQL. But I would be surprise if most people would ever notice it.

SSMS is your friend. If you are creating a one off report it will be fast and generally easier to use it then the WQL interface.

BTW, I will be presenting at IT/Dev Connection on this topic next month