Reply
Occasional Visitor
Posts: 1
Registered: ‎07-06-2017

Adding a column to the SCCM Solution Template

There is a column in the source SCCM database that I'd like to pull over into the Azure database so that I can use in the Power BI Template. It's a releasedate0 column under the dbo.v_GS_PC_BIOS view. 

I've read the section toward the bottom of this link about adding to the template, but I can't seem to get the data to pull over:

https://github.com/Microsoft/Business-platform-solution-templates/tree/master/Template/Microsoft-SCC...

 

Would anyone be able to provide any information to me about how to get this to work?

Visitor
Posts: 1
Registered: ‎11-20-2017

Re: Adding a column to the SCCM Solution Template

[ Edited ]

I am also having a similiar issue, I have followed the steps in the documentation on Github but no data is appearing in the tables in Azure.

Frequent Visitor
Posts: 2
Registered: ‎12-15-2017

Re: Adding a column to the SCCM Solution Template

Are you trying to bring that in to an existing query that's already a part of the Power BI model?

 

There are a few steps that you'll need to take, assuming that you're just adding a column to one of the views used in the .pbix file from your datamart:

  • Modify the underlying SQL query file that's being used to populate the staging table in the datamart to include your new column
  • Modify the related staging table and core tables in the datamart to include the new column
  • Modify the stored procedure that loads the core table from the staging table to include the new column
  • Modify the SQL view to include the new column that's in the database.

 

If you are wanting to create a new query entirely, that's a bit different. Similar as above, but you have to create the tables, stored procedure, view, and map the newly created staging table to the new .SQL file in the dataload powershell scripts. This is the script that tells uses your SQL queries to populate the related staging tables in the datamart. 

Frequent Visitor
Posts: 6
Registered: ‎06-25-2016

Re: Adding a column to the SCCM Solution Template

I've been able to add the extra columns, but my query isn't pulling any data. Here are the steps I've taken:

-Opened the existing query UserComputer.sql and appended it with the additional columns from multiple tables:

 

SET NOCOUNT ON;

SELECT S.ItemKey AS machineid,
S.user_name0 AS username,
S.user_domain0 + '\' + user_name0 AS [full name],
S.User_Name0 AS LastLoggedOnUser,
S.User_Domain0 AS LastLoggedOnUserDomain,
S.Last_Logon_Timestamp0 AS LastLoggedOnTimeStamp,
O.LastBootUpTime0 AS LastBootUpTime,
N.IPAddress0 AS IPAddress,
A.Name0 AS NICName,
A.MacAddress0 AS MACAddress

FROM dbo.vSMS_R_System S
JOIN v_GS_OPERATING_SYSTEM O ON O.ResourceID = S.ResourceID
JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION N ON N.ResourceID = S.ResourceID
JOIN v_GS_NETWORK_ADAPTER A ON A.ResourceID = S.ResourceID

WHERE s.user_domain0 IS NOT NULL AND
s.user_name0 IS NOT NULL AND
s.decommissioned0=0 AND
s.obsolete0=0 AND
A.MACAddress0 IS NOT NULL AND
N.IPAddress0 IS NOT NULL;

 

-When I run the above query against my CM DB, it returns the data in the columns that I'm looking for.

-I've created the extra columns under the pbist_sccm.usercomputer & pbist_sccm.usercomputer_staging tables. Saved.

-I've added the extra columns under the pbist_sccm.vw_usercomputer view. Saved

-I've re-ran the Scheduled Task that kicks off the dataloader.ps1 to pull data from my CM DB into this SolutionTemplate DB.

-I've refreshed my data sources in PowerBI and I see the new columns loaded.

Problem: There is not data in those new columns. The existing columns (the ones I didn't add) still contain data. All of the new columns just contain NULL data. When I look at the data contained in the pbsit_sccm.usercomputer table or view, there is no data in the new columns. It's almost like the query isn't pulling the data successfully when run from the scheduled Task but it works when directly querying the CM DB.

 

any ideas as to why my query isn't populating data in the new columns when run via the dataloader.ps1?

Any help is appreciated!

 

 

 

appending to an existing table/view (pbist_sccm.

Highlighted
Frequent Visitor
Posts: 6
Registered: ‎06-25-2016

Re: Adding a column to the SCCM Solution Template

Nevermind, figured it out.

I had to put a check box in the 'Allow Nulls' column when adding columns.