SCCM 2012 There is no Data in my - Add_Remove_Programs_DATA- Table

[dwhite@citadelitg.com]

I am trying to run the report "Computers with Specific software registered in Add Remove Programs" to get an accurate count of what workstations have what software installed. I try to run the report and I get nothing back, I look at the Table and the View, and receive nothing back.

I was wondering if anyone could help. I built a report that works, But it would be nicer to get the Add Remove list to work.

 

Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID = "COL0000F''
ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion

 

attached are my client settings

[GarthMJ]

Use this blog post to troubleshoot HW inv.

http://be.enhansoft.com/post/2013/07/25/Troubleshooting-Inventory-Flow.aspx

[dwhite@citadelitg.com]

ok, I followed your procedure, and this is the error I received in the dataldr.log file

Processing Inventory for Machine: HQ-IT-DWHITE Version 1.244 Generated: 06/18/2014 12:48:11 SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:20 PM 24032 (0x5DE0)
Loading architecture System from the database. SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:20 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pWorkstationStatus_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:20 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSYSTEM_CONSOLE_USER_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:20 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSYSTEM_CONSOLE_USER_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:20 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSYSTEM_CONSOLE_USAGE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_EXECUTABLE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pINSTALLED_SOFTWARE_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pAdd_Remove_Programs_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pBROWSER_HELPER_OBJECT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pBROWSER_HELPER_OBJECT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSystem_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
CGroup::AddGroup - couldn't get procedure pSOFTWARE_SHORTCUT_DATA SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
Signaled to abort. SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)
Done: Machine=HQ-IT-DWHITE(GUID:131F743B-F112-4BEC-A85F-B06A3C3D240D) code=0 (0 stored procs in XHYQNJY1D.MIF) SMS_INVENTORY_DATA_LOADER 6/18/2014 12:49:21 PM 24032 (0x5DE0)

[GarthMJ]

It look like you are having MAJOR SQL problems and therefore you should contact Microsoft Support (CSS) ASAP. This is NOT something that can be handle within the forums.

[dwhite@citadelitg.com]

Could it be a security issue? What does the server need in order to write to a SQL server on another box?

[GarthMJ]

Absolutely, it can be security. CM server itself requires DBO to the db and administer on the SQL server. No exceptions.

 

If you think it is permission, then it is likely someone play with the permission on the SQL server and they has messed up your CM db. This is one of the main reason why I will not recommend having SQL remote from CM itself.

 

IMO you still need to call CCS this is NOT something that anyone can help you with as editing the db is unsupported.

[dwhite@citadelitg.com]

DO you have any Idea how to put my default user back into SQL?

[GarthMJ]

Without knowing what got changed, there is no supported way to fix this, you need to contact CSS for support or re-build your whole site.

[jhfry]

I realize that this is an old thread, but I just spent 4 days working through the exact issue with Premiere support, who was mostly worthless, and finally discovered the cause and a solution. This was the only place I found a similar issue posted, so I wanted to add an answer in case someone else runs into it.

 

In our case, we observed the problem a few days after upgrading SCCM to 2012 R2 SP1. First we noticed that all of the stored procedures starting with 'p' and 'd' were missing from the database, we were even able to confirm they were deleted by the setup by querying dbo.dbschema (I think thats the table).

 

We had one other, seemingly minor, error in our log. It was a unique key violation trying to add some bitlocker data to the dbo.GroupMap table. Even Microsoft didn't think it was worth looking into until we got the stored procedures back. This error turned out to be the key to all of our problems.

It turns out that upgrades delete these stored procedures out of the database, and all of the hardware inventory procedures are created dynamically when the inventory data loader starts for the first time. But in our case, this wasn't happening.

 

To make a very long story short, the issue was caused by a custom inventory class we created over a year ago to collect bitlocker details with hardware inventory. Appearently, when we upgraded, we now had two classes with identical names "Bitlocker.Details.Data", and it would fail every time it tried to do the second one (due to the unique key violation), and never made it far enough to create any of the stored procedures.

 

We simply deleted the custom class from our hardware inventory configuration under client settings, and the next time it tried, it created everything.

 

If anyone ever sees this, I highly recommend looking at any custom inventory classes you have. Export them and delete them and see if it fixes your issue, you can always restore them.