1 2 Previous Next 20 Replies Latest reply on Apr 28, 2009 3:27 AM by koenhuntink

    Report to view Products use by computer.

    Rookie

      I would like to run a report that shows what computers have used a specified application withing a variable time period.

       

      I have a standard report that shows "Products Used in the Last N Day(s)" which shows me applications and their last used date.  I would like something similar to this but with a listing of computers and the date that the application was last used.

       

      Ideally, my report would look something like this:

       

       

                                          
         Adobe Acrobat Standard 8.0
            Computer      Last Use
      Computer A3/4/2009 9:52:06 AM
      Computer B1/1/2008 4:00:00 PM


        • 1. Re: Report to view Products use by computer.
          phoffmann SupportEmployee

          That, you'd need to pull straight out of the database, I think ... the SLM reports are hardcoded from what I remember, because of their results going beyond the normal reporting tool's capabilities at the moment.

           

          How comfortable are you with SQL?

           

          Paul Hoffmann

          LANDesk EMEA Technical Lead

          • 2. Re: Report to view Products use by computer.
            Rookie

            I have no experience with SQL at all.

            • 3. Re: Report to view Products use by computer.
              phoffmann SupportEmployee

              Hmm - OK, that's unfortunate and does inconvenience things a bit ....

               

              Do you have a DBA or a DBA-like resource you can work with?

               

              Paul Hoffmann

              LANDesk EMEA Technical Lead

              • 4. Re: Report to view Products use by computer.
                Rookie

                Yes, I do have a DBA that can help me with this.

                • 5. Re: Report to view Products use by computer.
                  phoffmann SupportEmployee

                  asd

                   

                                                                                             
                                                                                                                                      
                              Table Name    
                        
                              Important Row            Explanation
                  COMPUTERCOMPUTER_IDNUnique Identifier of a Computer. Used for references throught the DB.
                  COMPUTERDEVICEIDName of the device
                  PRODUCTPRODUCT_IDNUnique identifier of a Product. Used for references throughout the DB.
                  PRODUCTTITLETitle of the product (so you can filter by it)
                  APPSOFTWARE (View)SCM_DaysSinceLastUsedDays since the file has been last used
                  APPSOFTWARE (View)SCM_LastSessionStartWhen we recorded the last start time of the app

                   

                  I was actually going through giving a bit of a fuller explanation (which would likely be continued tomorrow), but if your report is "all" that you're after, you may actually have all the data you want/need with the APPSOFTWARE view.

                   

                  A view is like a selection of data from different database tables ... creating a "virtual table" so to speak. Have your DBA look at the APPSOFTWARE view with you, and see if that data there is sufficient for you (if it is, it's just a single object to operate on, and thus makes life a LOT easier (also means I don't have to spend an hour writing up the various relationships between the SLM tables, if that's OK with you. I think your DBA would appreciate not having to read it all as well) .

                   

                  See if that's enough - hopefully the "simple solution" will be sufficient for you (seems to be) ... if it is, great.

                   

                  Paul Hoffmann

                  LANDesk EMEA Technical Lead

                  1 of 1 people found this helpful
                  • 6. Re: Report to view Products use by computer.
                    Rookie

                    Paul,

                     

                    Working with appsoftware got me the data that I needed.  Thank you!!

                    • 7. Re: Report to view Products use by computer.
                      phoffmann SupportEmployee

                      My pleasure.

                       

                      Paul Hoffmann

                      LANDesk EMEA Technical Lead

                      • 9. Re: Report to view Products use by computer.
                        Rookie

                        Dear Paul,

                         

                         

                         

                                    Table Name    
                             
                                    Important Row            Explanation
                        COMPUTERCOMPUTER_IDNUnique Identifier of a Computer. Used for references throught the DB.
                        COMPUTERDEVICEIDName of the device
                        PRODUCTPRODUCT_IDNUnique identifier of a Product. Used for references throughout the DB.
                        PRODUCTTITLETitle of the product (so you can filter by it)
                        APPSOFTWARE (View)SCM_DaysSinceLastUsedDays since the file has been last used
                        APPSOFTWARE (View)SCM_LastSessionStartWhen we recorded the last start time of the app

                         

                         

                         

                        I'm trying to do the same thing but I was wondering how the tables are related to one another. The product table isn’t linked to the computer or Appsoftware view. As far as I understand the product table only holds the known products within the organization, but these aren’t linked to the computer devices? However it is possible to link the appsoftware and computer tables to each other. So I can get for instance:

                         

                         

                        Computer_IDN, DeviceID, SCM_LastSessionstart, Appsoftware.Title.

                         

                         

                        This works but the problem here is that the Title in appsoftware isn’t very clear. For instance I can’t find out if it is Word 2003 or 2008. The product title is way better for this. Isn’t it possible to link the computer, product and appsoftware table?

                         

                         

                        An alternative is to use the Suitename from the AppSoftwareSuites table, but this is also not related so showing the last startup time isn’t possible?

                        • 10. Re: Report to view Products use by computer.
                          phoffmann SupportEmployee

                          Here's how the daisy chain of links works. APPSOFTWARE is just a VIEW - it's got "hand picked" data from various tables. If you design the view, you'll see it as much .

                           

                          PRODUCT table "only" lists products -- which files make up a product is defined in the PRODUCTFILE table (it's pretty much a straight link of PRODUCT_IDN to FILEINFO_IDN).

                           

                          The FILEINFO_IDN comes from the FILEINFO table (the "master" table of all file-names).

                           

                          Which devices has which files is handled in the FILEINFOINSTANCE table (this is essentially mapping COMPUTER_IDN's to FILEINFO_IDN's).

                           

                          So - you CAN daisy chain all this up. You may just have make a join on COMPUTER_IDN // FILEINFO_IDN // PRODUCT_IDN, depending on how you want to do it.

                           

                          Make more sense now?

                           

                          [EDIT:]

                          It should be noted that the PRODUCT table also lists whether a product is a custom or an auto-discovered product. Not all Auto-discovered products have files associated with them. Generally, the rules I layed down here apply primarily to CUSTOM products. Auto-discovered products are a very special case and treated seperately.

                           

                          [2nd EDIT:]

                          I've forgotten to mention the PRODUCTCOMPUTER table which linked PRODUCT_IDN's to COMPUTER_IDN's as well (if you want to make your life easier) .

                           

                          Paul Hoffmann

                          LANDesk EMEA Technical Lead

                           

                          Message was edited by: Paul Hoffmann

                          • 11. Re: Report to view Products use by computer.
                            Rookie

                            Dear Paul,

                             

                             

                            Thanks for your quick reply!

                             

                            With your information I managed to build a query that almost shows me all the required information. I'm almost there, but there is still something missing. Can you have a look at my query?

                             

                             

                            Selecting the requierd fields and getting  the latest recorderd session from the logged session in the SCM_LastSessionStart field

                            select computer.Devicename, product.title, max(FileinfoInstance.SCM_LastSessionStart) as 'last executed'

                             

                             

                            from ld88.dbo.computer, ld88.dbo.product, ld88.dbo.productcomputer, ld88.dbo.FileInfoInstance

                             

                            Linking the databases together

                            where

                             

                             

                             

                             

                            computer.computer_Idn = dbo.productcomputer.computer_idn         Linking the computers with the products
                            and product.product_Idn = dbo.productcomputer.product_idn           Linking the computers with the products
                            and computer.computer_Idn = dbo.FileInfoInstance.computer_id      Linking the files with the computer

                             

                            Filtering the required software products

                            and (product.product_Idn = '1741'

                                      or product.product_Idn = '2710')

                             

                             

                             

                            group by computer.Devicename, product.title

                            order

                             

                             

                             

                            by computer.devicename

                             

                            This worked but I noticed that the results were false. I validated that the computers are linked to the products. Also all the files that are associated with a computer are linked together. I think that the problem is that all the files associated with a computer are used to calculate the lastsessionstart. It should use all the files associated to a computer from a certain product, and then calculate the lastsessionstart.

                             

                            Do you know how to make the link between all the files from a product associated to a computer?

                             

                            Thanks in advance and have a good weekend!

                            • 12. Re: Report to view Products use by computer.
                              Rookie

                              Dear paul,

                               

                              Can you have a look at my query?

                               

                              Thanks in advance!

                              • 13. Re: Report to view Products use by computer.
                                phoffmann SupportEmployee

                                Just as a quick comment - I will try.

                                 

                                I'm shooting off for a few weeks on Wendesday, and (oddly enough) have a lot of things I need (or want to) see through to completion before I go. I'll try to get around to this, but I cannot make any promises at this point.

                                 

                                Paul Hoffmann

                                LANDesk EMEA Technical Lead

                                • 14. Re: Report to view Products use by computer.
                                  Rookie

                                  Dear Paul,

                                   

                                  Again thanks for your quick reply. I made a new query, and I think this makes the right relations between the tables. But now I have a different problem.

                                   

                                  First off the new query:

                                   

                                  Selecting the data from the right tables

                                   

                                   

                                  select c

                                   

                                  omputer.devicename, product.title, fileinfoInstance.path, max(FileinfoInstance.SCM_LastSessionStart) as 'last run'

                                  from

                                   

                                  ld88.dbo.computer, ld88.dbo.product, ld88.dbo.fileinfoinstance, ld88.dbo.fileinfo, ld88.dbo.productfile

                                   

                                   

                                   

                                  Linking the tables together:

                                  where

                                   

                                   

                                  computer.computer_idn = fileinfoinstance.computer_idn  Linking the computer with the associated files

                                  and fileinfoinstance.fileinfo_idn = fileinfo.fileinfo_idn  Linking the computer with the associated files 

                                  and fileinfo.fileinfo_idn = productfile.Fileinfo_idn    Linking the products with the associated files

                                  and product.product_idn = productfile.product_idn    Linking the products with the associated files

                                  Some search criteria for the products I want to get the information off:

                                  and (product.product_Idn = '1741'

                                  or product.product_Idn = '2710'

                                  or product.product_Idn = ''873'

                                  or product.product_Idn = '1192'

                                  or product.product_Idn = '1598')

                                  etc

                                   

                                   

                                  Show only applications that actually are executed on an computer

                                  and

                                   

                                  FileinfoInstance.SCM_LastSessionStart not like 'null'

                                   

                                   

                                  Grouping and sorting the information:

                                   

                                  group

                                   

                                  by computer.devicename, product.title, fileinfoInstance.path

                                  order

                                   

                                  by computer.devicename

                                   

                                   

                                  This new query gives me the right information, but now I'm encountering another problem. I looked at the software license monitoring for all (automatic discovered) products. I then filtered this information for the products I needed and looked up their (unique) product_Idn. I use this product_Idn for filtering the returned information from the query. The products that I want information off are the following:

                                   

                                   

                                  Microsoft Office 2000 SR-1 Premium 9,0
                                  Microsoft Office 2000 SR-1 Standard 9,0
                                  Microsoft Office XP 10.0
                                  Microsoft Office XP Professional 10.0
                                  Microsoft Office 2003
                                  Microsoft office Professional Editie 2003
                                  Microsoft office Professional Edition 2003
                                  Microsoft Office Standard Edition 2003
                                  Microsoft Office Standard 2007
                                  Microsoft Office Enterprise 2007
                                  Microsoft Office Ultimate 2007
                                  Microsoft Visio 2000
                                  Microsoft Visio 2000 (IE) 6.0
                                  Microsoft Visio Professional 2002 [NLD] 10.2
                                  Microsoft Office Visio Standard 2003
                                  Microsoft Office Visio Professional 2003
                                  Microsoft Office Visio Professional 2007
                                  Microsoft Project 2000
                                  Microsoft Project Standard 2002
                                  Microsoft office Project Professional 2003
                                  Microsoft office Project Standard 2003
                                  Microsoft office Project Professional 2007

                                   

                                  If I look at the results off the query I notice for example the following:

                                   

                                  A computer uses C:\programfiles\Microsoft office\office11\WINWORD.EXE

                                   

                                   

                                  Landesk sees this as Microsoft Office Standard Edition 2003, Microsoft Office Professional Edition 2003 and Microsoft Office Professional Editie 2003 and shows these 3 products for an computer(with the same last startup time because its the same executable)

                                   

                                   

                                  Another example is C:\programfiles\common files\microsoft shared\dw\dw20.exe

                                   

                                  Landesk sees this as Microsoft office visio professional 2003, Microsoft office visio standard 2003 but also as Microsoft professional Edition 2003.

                                   

                                   

                                  How can I solve this? Obviously its not the fault of landesk but microsoft for not making different executable names  I can make the list of products smaller by deleting the doubles(Microsoft Office Professional Edition 2003 and Microsoft Office Professional Editie 2003) but then I still have the problem that landesk sees an executable both as an standard, professional or another product(visio)

                                   

                                  I was thinking to modify the associated files for an application in the software license monitoring.  for example I can mark dw20.exe as not in product for visio standard 2003 so it looks for visio.exe and then mark the product as visio. is this the only option I have? I don't know if this is an best practice..

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                  1 2 Previous Next