PENDING Report WQL

[tezza]

Hi

I don't have access to config mgr for our SCCM CB and would like to know what the WQL is behind the report 'Power Management - Energy Consumption' so I can drop it in SQL and try and understand which tables/views/attributes are used.

Would it be possible for somebody to go in to the properties of this report and copy the WQL code and paste in here?

Thanks in advance.

 

[Garth]

First off the Reports only use SQL, NOT WQL.
It is NOT supported to query the tables themselves.
All supported views are listed here. https://docs.microsoft.com/en-us/me...lviews/sql-server-views-configuration-manager

 

[tezza]

First off the Reports only use SQL, NOT WQL.
It is NOT supported to query the tables themselves.
All supported views are listed here. https://docs.microsoft.com/en-us/me...lviews/sql-server-views-configuration-manager

First off I'm going off my experience with Config Mgr for SCCM 2007 in which you can go into the properties of the report and get the WQL/SQL code.

I have access to the views, what I want is the code that's behind the report, no problem if you can't provide it

 

[Garth]

This blog will show you how to back up a single RDL and once you have it you can open the RDL in notepad to grab the SQL or you can use report Builder to interact and change the report itself. https://www.enhansoft.com/how-do-you-backup-a-single-sccm-report/


As an aside, Check out both https://www.wpninjas.ch/ and https://mmsmoa.com/ they will have sessions on reporting and CM.

 

[tezza]

This blog will show you how to back up a single RDL and once you have it you can open the RDL in notepad to grab the SQL or you can use report Builder to interact and change the report itself. https://www.enhansoft.com/how-do-you-backup-a-single-sccm-report/


As an aside, Check out both https://www.wpninjas.ch/ and https://mmsmoa.com/ they will have sessions on reporting and CM.

Thanks Garth, appreciate the information and the links provided, however the 'download' option isn't available in the menu options for the report, maybe it's been hidden from end users.

Is there any chance that you could grab the SQL from your version and post it here?

Thanks
Stuart

 

[Garth]

I could but remember that it is RBA query and not strait SQL query. So unless you know how to write RBA queries/reports or convert them to non RBA version, it will not be so helpful. Although the names of the RBA functions closely mirror the SQL view names.

Also keep in mind that if you can access the file system for your reporting point the (raw) RDLs are there too.

if you want the RBA version of that report, let me know and I will dig it up.

 

[tezza]

I could but remember that it is RBA query and not strait SQL query. So unless you know how to write RBA queries/reports or convert them to non RBA version, it will not be so helpful. Although the names of the RBA functions closely mirror the SQL view names.

Also keep in mind that if you can access the file system for your reporting point the (raw) RDLs are there too.

if you want the RBA version of that report, let me know and I will dig it up.

Sorry Garth I'm a little confused, I thought you said it's possible to "back up a single RDL and once you have it you can open the RDL in notepad to grab the SQL"? Therefore are you not able to back up the report and grab the SQL as you suggested?

Thanks

 

[Garth]

Sorry Garth I'm a little confused, I thought you said it's possible to "back up a single RDL and once you have it you can open the RDL in notepad to grab the SQL"? Therefore are you not able to back up the report and grab the SQL as you suggested?

Thanks

Yes I can BUT as I said it will NOT do you any good unless you know how to query with RBA. so do you know how to query CM with RBA via SQL?

 

[Garth]

BEGIN
declare @isLaptop as bit


if @deviceType =2
set @isLaptop = 1
else if @deviceType =0
set @isLaptop = null
else
set @isLaptop = 0

EXEC   [SCCM_Rpt].[rptspPowerManagementGetMonthlyReport]
                                @reportStartDate =@reportStartDate ,
                                @reportEndDate = @reportEndDate,
                                @collectionId = @collectionId,
        @computerOnDesktop = @computerOnDesktop,
        @computerOnLaptop =@computerOnLaptop ,
        @computerOffDesktop =@computerOffDesktop,
        @computerOffLaptop = @computerOffLaptop,
        @computerSleepDesktop = @computerSleepDesktop,
        @computerSleepLaptop = @computerSleepLaptop,
        @monitorOnDesktop = @monitorOnDesktop,
        @monitorOnLaptop = @monitorOnLaptop,
                                @isLaptop = @isLaptop
END

 

[Garth]

begin
 if (@filterwildcard = '')
 select v_Collection_Alias.CollectionID, v_Collection_Alias.Name from fn_rbac_Collection(@UserSIDs) v_Collection_Alias
 where v_Collection_Alias.CollectionType = 2
 order by v_Collection_Alias.Name
else
 select v_Collection_Alias.CollectionID, v_Collection_Alias.Name from fn_rbac_Collection(@UserSIDs) v_Collection_Alias
 WHERE v_Collection_Alias.CollectionID like @filterwildcard
               and
               v_Collection_Alias.CollectionType = 2
 order by v_Collection_Alias.Name
end

 

[tezza]

Yes I can BUT as I said it will NOT do you any good unless you know how to query with RBA. so do you know how to query CM with RBA via SQL?

Yes, we have a back up of the SCCM CB database on our SQL server, so I can query the tables/views via SSMS which is exactly what I did with SCCM 2007, copy out the SQL from the report properties, drop them in SSMS query and run/update/amend the query to get the desired result or at least work out what the report is doing.

The Power Management report provides a total monthly kWh figure which I'm struggling to recreate in SSMS without knowing exactly what the SQL code is. Does that make sense?

 

[tezza]

begin
 if (@filterwildcard = '')
 select v_Collection_Alias.CollectionID, v_Collection_Alias.Name from fn_rbac_Collection(@UserSIDs) v_Collection_Alias
 where v_Collection_Alias.CollectionType = 2
 order by v_Collection_Alias.Name
else
 select v_Collection_Alias.CollectionID, v_Collection_Alias.Name from fn_rbac_Collection(@UserSIDs) v_Collection_Alias
 WHERE v_Collection_Alias.CollectionID like @filterwildcard
               and
               v_Collection_Alias.CollectionType = 2
 order by v_Collection_Alias.Name
end

This looks promising, thanks Garth i'll take a look and get back to you.