Yours returns everything advertised to the user, installed or not. I added this to it to get only software that was actually requested AND installed. (ComplianceState 1 is installed).
AND AD.UserName IS Not Null AND ad.ComplianceState = 1 It also returns duplicate rows if the app is available to them by virtue of multiple deployments (deployed to mope than one collection) Once I added compliancestate = 1 to yours I can see that mine is also inaccurate. :-) Yours is more accurate if you narrow it as I did above. I guess I need to go rewrite some of my stuff :-( SELECT Distinct ds.SoftwareName AS SoftwareName, ad.MachineName, ad.UserName 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 AND AD.UserName IS Not Null AND ad.ComplianceState = 1 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 > > > > > > > > > > > > > >
