Impressive query. What the hell is it doing? :) But I think I wouldn’t need that, right?
-R From: [email protected] [mailto:[email protected]] On Behalf Of Todd Hemsell Sent: Montag, 29. Dezember 2014 21:20 To: [email protected] Subject: Re: [mssms] get list of applications a user installed 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] <mailto:[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. :) Thanks! From: [email protected] <mailto:[email protected]> [mailto:[email protected] <mailto:[email protected]> ] On Behalf Of Todd Hemsell Sent: Montag, 29. Dezember 2014 20:32 To: [email protected] <mailto:[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] <mailto:[email protected]> > wrote: Anyone knows of a method to retrieve the list of applications a user installed through the catalog? -Roland
