How can I see the MCS profiles that have been applied to a device directly?

Document ID : KB000103158
Last Modified Date : 26/06/2018
Show Technical Document Details
Question:
I am looking for a way to find any profiles ( group ) that have been overridden at the node level.
I am trying to prevent these and need to go find them so that I can move the changes to a new group.
These types of changes don't remain after any changes are made to the profile, so it isn't a good long term solution.
I would also like to find any profiles that are deployed to the individual node.
Essentially I don't want any profiles deployed directly to a node.
I want them all to be performed from a group.
I am trying to clean up existing environment.
I am looking for SQL queries, or global ways to find information.

 
Environment:
UIM 9.0 and earlier
MCS 9.0 and earlier
Answer:
The first is a list of profiles created at the group level and their status.
SELECT        SP.profileId, SP.profileName, ST.templateId, ST.templateName, ST.version, ST.probe, ST.probeversion, SP.cs_id, SP.status, SP.device, SP.parentProfile, SP.group_id, SDG.name AS Groupname, SDG.active,
                         SDG.cm_group_id, SDG.parent_group_id, SDG.status AS groupProfileStatus
FROM            SSRV2Profile AS SP INNER JOIN
                         SSRV2Template AS ST ON SP.template = ST.templateId INNER JOIN
                         SSRV2DeviceGroup AS SDG ON SP.group_id = SDG.id
order by st.templateName



the second is a list of profiles at the device level and their status.
SELECT SP.profileId, SP.profileName, ST.templateId, ST.templateName, ST.version,
ST.probe, ST.probeversion, SP.cs_id, SP.status, SP.device, SP.parentProfile,
sp.ancestorprofile, SP.group_id, CS.name AS CSname
FROM SSRV2Profile AS SP INNER JOIN
SSRV2Template AS ST ON SP.template = ST.templateId INNER JOIN
CM_COMPUTER_SYSTEM AS CS ON SP.cs_id = CS.cs_id
WHERE (SP.group_id IS NULL and sp.ancestorprofile is null)
order by st.templateName

this third query will show you which values have been overwritten at the node level
SELECT        SP.profileId, SP.profileName, ST.templateId, ST.templateName, ST.version, ST.probe, ST.probeversion, SP.cs_id, SP.status, SP.device, SP.parentProfile, SP.ancestorprofile, SP.group_id, CS.name AS CSname, 
                         cv.cfgkey, cv.value, cv.profile, cv.variable, cv.removalId, cv.action, cv.selectableObject
FROM            SSRV2Profile AS SP INNER JOIN
                         SSRV2Template AS ST ON SP.template = ST.templateId INNER JOIN
                         CM_COMPUTER_SYSTEM AS CS ON SP.cs_id = CS.cs_id INNER JOIN
                         SSRV2ConfigValue AS cv ON SP.profileId = cv.profile
WHERE        (SP.ancestorprofile IS NOT NULL)
ORDER BY SP.profileId