Remove the ‘top 1’ at the beginning of the query. You’ll get a bazillion results then. ☺
From: [email protected] [mailto:[email protected]] On Behalf Of Daniel Corkill Sent: Thursday, June 20, 2013 3:33 PM To: [email protected] Subject: RE: [mssms] Last used date for each application report Thanks John. I’m only getting one record returned when I run this? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Marcum, John Sent: Thursday, 20 June 2013 11:24 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] Last used date for each application report We used Stratusphere FIT<http://www.liquidwarelabs.com/products/stratuspherefit.asp> and Stratusphere UX<http://www.liquidwarelabs.com/products/stratusphereux.asp> from LiquidWare labs and Changebase<http://www.quest.com/changebase/> from Dell as part of our assessments. We are doing VDI though, those may or may not be a fit for you. To Garth’s point I actually wrote the report you are looking for this morning. I have BDNA Normalize CM<http://info.bdna.com/NormalizeCMFreeTrial/> which makes such reporting actually usable and it has compatibility info for installed apps. The RAW data returned from Configmgr, for my 1000 clients is 161,000 + rows. The data returned from the normalized data are the 17,975 I actually care about. Here’s the report from CM if you really want it: select top 1 sys.Name0 as 'Computer Name', sys.User_Name0 as 'Computer User Name', TUSER.Full_User_Name0 as 'Full Name', l0 as 'Location', rcnt.CompanyName0 as 'Publisher', rcnt.ProductName0 as 'Product', rcnt.FileDescription0 as 'Description', rcnt.LastUserName0 as 'Last User', rcnt.ExplorerFileName0 as 'File Name', rcnt.ProductVersion0 as 'Version', DATEDIFF(DAY,LastUsedTime0,GETDATE()) as 'days since used' from v_R_System sys LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS rcnt on sys.ResourceID = rcnt.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM os on sys.ResourceID = os.ResourceID LEFT OUTER JOIN v_R_User TUSER ON TUSER.User_Name0 = SUBSTRING(sys.User_Name0, CHARINDEX('\', sys.User_Name0) + 1, LEN(sys.User_Name0)) where LastUsedTime0 is not NULL and DATEDIFF(DAY,LastUsedTime0,GETDATE())<=90 and os.Caption0 not like '%server%' Order by sys.Name0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Thursday, June 20, 2013 6:45 AM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] Last used date for each application report IMO they are going about this all wrong. if they are worry about Windows 7 and SW compatibility then they should get the list of all applications (built-in report) and do a lookup against each of those to see if they are windows 7 compatible. This way the report will be MUCH smaller as the report will start at 1000 lines and increase to say 100,000 lines depending on the size of you environment. I would then export this into excel add a column to state the Win7 compliancy & ”do I care factor” & “what are we going to replace this with”, someone can review it within a couple of days to get 80% of all titles flagged correctly. Then it would be someone call to decide what to do about the remain 20%, but in most cases I would ignore them as they are one off titles or utils. BTW I was the CM07 Architect for a client of mine and they had my cubicle buddy responsible for this exact task! It took him over 4 months to classy every title in the 1 division of the company, there was over 40, 000 titles! He would just love me when I pointed out that there was new SW in the environment. If you have any $$ seriously look at 1e and BDNA for this type of thing. They should be about to give you your Win7 compliancy details in a few days after installing the product. Sent from Windows Mail From: Daniel Corkill Sent: Thursday, June 20, 2013 7:34 AM To: [email protected]<mailto:[email protected]> I’m aware it’s a pretty crazy request. Early stages of a Windows 7 upgrade…they want to see what software out there is actually used so they know what software is in scope for them to care about, i.e. verifying Windows 7 compatibility, etc. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Thursday, 20 June 2013 9:08 PM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] Last used date for each application report First off this report will be huge! You are talking about ~2.5 pages per PC. How does the requestor plan to use this report? Sent from Windows Mail From: Daniel Corkill Sent: Thursday, June 20, 2013 1:28 AM To: [email protected]<mailto:[email protected]> All, I’ve been asked to see if it’s possible to create a web report in ConfigMgr 2007 to list every application, version and the date it was last used. I’m looking at the 07A, 07B and 07C asset intelligence reports and I’m thinking these reports contain all the data I’ll need. I think I need a query that firstly gets every application on every computer. Then, gets the last used date on every computer that has each application and use the latest last used date as the last date the application was used. Does anyone have any ideas? ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
