Oh, you want a users app policy? Should have said so :-)
This is what the MP calls
The dates decide if it is a delta request or a full policy request.
Not sure how to edit out the machine portion of it unless you calculate it
by looking for all the relationship ID's in UserMachineRelation where the
user is part of it.
Most of the below is a wrapper around
MP_GetUserAndUserGroupPolicyAssignments
Declare @UserName nvarchar(50)
Declare @CompGUID Nvarchar(255)
Declare @WeeksAgo SmallDateTime
-- this one?
SET @UserName = 'exterran\themsel01'
Set @CompGUID = (Select SMS_Unique_Identifier0 From V_R_System Where
Netbios_Name0 = 'EXV-9111-4425-4') --EXV-4638-5362-2
Set @WeeksAgo = DateAdd(DD,-90,GetDate())
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and
o.id = object_id(N'tempdb..#UserPolicyAssignments'))
DROP TABLE #UserPolicyAssignments;
CREATE TABLE #UserPolicyAssignments (
PolicyAssignmentID NVarChar(50),
Version nvarchar(10),
LastUpdateTime datetime,
Body nvarchar(max),
IsTombstoned int,
BodySignature nvarchar(max),
HashAlgId int,
HashAlgOID nvarchar(50),
InProcess Bit
)
INSERT INTO #UserPolicyAssignments
EXEC MP_GetUserAndUserGroupPolicyAssignments @userName,'','','','',@CompGUID
,@WeeksAgo
SELECT
'User Policy' As [Source],
CASE
When Apps.DisplayName IS Not Null Then 'Application Policy'
When DT.DisplayName Is Not Null Then 'DeploymentType Policy'
When PolicyID like '%/supersedence' Then 'App Supersedence Policy'
When PolicyID like '%/RequiredApplication%' Then 'Required Application
Policy'
Else 'OTHER'
End As [PolicyType],
CASE
When Apps.DisplayName IS Not Null Then Apps.DisplayName
When DT.DisplayName Is Not Null Then DT.DisplayName
Else ''
End As [PolicyDisplayName],
IsNull(A.AssignmentName,'') AS [AssignmentName],
C.Name AS [CollectionName],
FCM.Name AS [UserMemberOfCollection],
Apps.CreatedBy,
Apps.DateCreated,
Apps.CI_UniqueID,
PA.PADBID,
PA.PolicyID,
UPA.PolicyAssignmentID,
UPA.LastUpdateTime,
UPA.Body,
UPA.IsTombstoned,
UPA.*,
Apps.*
FROM #UserPolicyAssignments UPA
LEFT JOIN PolicyAssignment PA on PA.PolicyAssignmentID = UPA.
PolicyAssignmentID
Left Join fn_ListApplicationCIs(1033) Apps ON PA.PolicyID Like Apps.ModelName
+ '%' AND Apps.IsLatest = 1
Left Join fn_ListDeploymentTypeCIs(1033) DT ON left(DT.CI_Uniqueid,96) =
left(PA.PolicyID,96) AND DT.IsLatest = 1
Left Join CM_CEN.dbo.vCI_AssignmentTargetedCIs ACI on ACI.CI_ID = Apps.CI_ID
Left Join CM_CEN.dbo.vCI_CIAssignments A on A.AssignmentID = ACI.
AssignmentID
Left Join v_Collection C on C.CollID = A.TargetCollectionID
Left Join v_FullCollectionMembership FCM on FCM.CollectionID = C.CollectionID
AND FCM.SMSID = @UserName
--WHERE [AssignmentName] like '%Economic%'
Order By [PolicyType],PolicyDisplayName,[AssignmentName]
On Mon, Dec 29, 2014 at 2:01 PM, Roland Janus <[email protected]>
wrote:
> Uh, that’s pretty neat!
>
>
>
> I’ve looked into a report and changed it a bit to get something similar
> (quick and dirty):
>
>
>
> SELECT ds.SoftwareName AS SoftwareName,
>
> ds.CollectionID,
>
> ds.CollectionName,
>
> ad.MachineName,
>
> ad.UserName,
>
> ds.FeatureType,
>
> cm.siteid
>
> FROM v_CollectionExpandedUserMembers cm
>
> INNER JOIN v_R_User ud ON ud.ResourceID= cm.UserItemKey
>
> INNER JOIN v_DeploymentSummary ds ON ds.CollectionID = cm.SiteID
>
> LEFT JOIN v_AppIntentAssetData ad ON ad.UserName = @userid AND ad.AssignmentID
> = ds.AssignmentID
>
> INNER JOIN v_CIAssignment cia ON cia.AssignmentID = ds.AssignmentID
>
> WHERE ud.Unique_User_Name0 = @userid
>
>
>
>
>
> didn’t check the differences and the details, but I have the feeling yours
> is a better fit
>
>
>
> displayname should be what I need to create the applications variable
> from, to install them during OSD.
>
> Now I just need all the other parts, like a webpage I can request that
> data through, the primary user etc. J
>
>
>
> Thanks!
>
>
>
>
>
> *From:* [email protected] [mailto:
> [email protected]] *On Behalf Of *Todd Hemsell
> *Sent:* Montag, 29. Dezember 2014 20:32
> *To:* [email protected]
> *Subject:* Re: [mssms] get list of applications a user installed
>
>
>
> You want all of them or the ones that require approval?
>
> Here is all of them.
>
>
>
>
>
>
>
> Select
>
> UR.AppID,
>
> UR.CreationTime AS [DateRequested],
>
> VRS.Netbios_Name0 AS [MachineRequestedFrom],
>
> U.Full_User_Name0 AS [RequestingUser],
>
> Apps.DisplayName
>
> From UserAppModelSoftwareRequest UR
>
> Left Join UserMachineRelation UMR on UMR.RelationshipResourceID = UR.
> RelationshipResourceID
>
> Left Join v_R_User U on U.Unique_User_Name0 = UMR.UniqueUserName
>
> Left Join v_R_System VRS on VRS.ResourceID = UMR.MachineResourceID
>
> Left Join fn_ListApplicationCIs(1033) Apps on Apps.ModelName = UR.AppID
>
>
>
> On Fri, Dec 19, 2014 at 6:58 PM, Roland Janus <[email protected]>
> wrote:
>
> Anyone knows of a method to retrieve the list of applications a user
> installed through the catalog?
>
>
>
> -Roland
>
>
>
>
>
>
>
>
>
>
>
>
>
>
