Well, if you want everything a user requested on a system, this is where you get it :-) This is where the MP gets it at any rate. You can just call that SP from a webservice and return the results. It tells you what the user requested for that particular device. You could take these results and store it in a DB. Image the system, then get those results and call that fast deploy SP to recreate all of the requests or directly call the application catalog service and recreate them all.
put another way... It persists between images also., So even after you reimage a system it will get the same policy. It just shows as "available" instead of "installed" So you could get the policy of what is available, and then post it to the web service to trigger another fat deploy. Your scenario is much less complicated than mine. On Mon, Dec 29, 2014 at 3:24 PM, Roland Janus <[email protected]> wrote: > Impressive query. What the hell is it doing? J > > 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]> > 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 > > > > > > > > > > > > > > > > > > > >
