10 Replies Latest reply on Jul 5, 2016 2:43 AM by J.Guenther

    Custom Report LDMS 9 SP2 Licensed Software

    Rookie

      Guys,

       

      Does anybody know how to create a custom report that show like this from landesk management version 9 SP2  (this is just a sample excel report)

      Report.jpg

      I need these columns ( 'Application', 'Installed', 'License Quantity', 'Delta') to take from table SQL directly especially that related with SLM. For Example : if the admin wants to monitor some application and add license information from SLM, and the admin need the report, this custom report could provide those information.

      I just think that this custom report could be using the 'matrix' (correct me if i wrong).

       

      Thanks,

      Aldi

        • 1. Re: Custom Report LDMS 9 SP2 Licensed Software
          Simon Ma Employee

          Hello Aldi,

           

          I assumed that all the license information, their association with monitored products, quantity, etc. have been entered in the SLM module.

           

          So you have several methods to create this report.

           

          The first one, and probably the easiest way, is to go in SLM console, in the licenses tab/view.

          In the "Product compliance" panel, you have a “View as report” button on the right.

          The generated report will be exactly what can see in the console. And then, you can save it as an Excel sheet.

          The result is not exactly what you are looking for, but pretty close : it will just missing the total of installed application and the Delta.

           

          The second method is to create a custom report based on a SQL query to get exactly what you want.

          Here is the query you can use :

           

          ----------------------

          select    SL.Name as Application,

                          SL.Quantity,

                          COUNT(PUR.Computer_Idn) as Installed,

                          (SL.Quantity - COUNT(PUR.Computer_Idn)) as Delta

          from SLM_SoftwareLicense SL

          inner join SLM_ProductUsageResult PUR on PUR.Product_Idn = SL.PriProdAssociation OR PUR.Product_Idn = SL.SecProdAssociation

          group by SL.Name, SL.Quantity

          -----------------------

           

          Here some explanation on this query.

          This query is based on 2 tables :

          -          SLM_SoftwareLicense : table that contain all your declared license in SLM

          -          SLM_ProductUsageResult : table that contain all the application usage information

          Concerning the count of installed application, you may know that, in SLM we can associate a license to 1 or 2 monitored software. (I don’t consider the license associate to a group of application in this query...)

          So the result of the count of installed application, is the number of device where the application have been discovered for the 2 applications.

           

          I’ve also attach the custom report based on this query.

           

          Hope that what you are looking for or at least my answer can help you...

           

          Simon

          1 of 1 people found this helpful
          • 2. Re: Custom Report LDMS 9 SP2 Licensed Software
            Rookie

            Okay Simon, i'll try it...thanks for your answer

            • 3. Re: Custom Report LDMS 9 SP2 Licensed Software
              Rookie

              Hello Simon,

               

              Based on your query on SQL, it's really help me for the software license report. Thank you very much. It's actually pretty close of what my boss requested.

              However, this query didn't work if i have a license that covered 2 Products.

              For example, I have an Office 2010 Standard License that has 50 quantities and it is installed 30 so the delta is 20. Then i want this quantity to cover my office 2007 in order to compliance.

              I'm still confuse how to query this report.

              • 4. Re: Custom Report LDMS 9 SP2 Licensed Software
                Simon Ma Employee

                Hello,

                 

                Indeed I don't consider in my previous query the case when 1 license is associate to 2 products.

                That's why I've create a new one, a little more complexe (see attach file), which takes this case in count.

                 

                The enclosed report is a remake of an old report existing in LDMS 8.8 called Compliance of Software License Monitoring

                 

                As you can see in the screenshot I take (see attach file), for MICROSOFT OFFICE for instance, all 'Not Deploy' License for the primary product are available for the secondary product.

                Some addditional information are added suc as 'Loaned' and 'Borrowed' licenses.

                 

                Hope that can help you...

                 

                Simon

                 

                 

                • 5. Re: Custom Report LDMS 9 SP2 Licensed Software
                  Rookie

                  Hi!

                  How to make a similar report for the SLM LDMS 9.5 SP2, to take account of computer groups (selection may be made of the group), normalized product and also automatically discovered product?

                   

                  This report say many error in SQL and not worked in SLM:

                  Msg 512, Level 16, State 1, Line 33

                  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

                  Msg 512, Level 16, State 1, Line 58

                  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

                  • 6. Re: Custom Report LDMS 9 SP2 Licensed Software
                    Simon Ma Employee

                    Hello,

                     

                    This custom report is indeed not compatible with SLM in LDMS 9.5 SP2.

                     

                    It's because that in SLM LDMS 9.0, we were only able to associate a license to 2 products max.

                    Now, with SLM LDMS 9.5 SP2, we are able to associate a license to as many product as you want.

                    By consequence, the table in the database has change and some new ones has been added.

                    That one of the reason why you will have errors when you try to run the SQL query in it.

                     

                    But in LDMS 9.5 SP2, you have by default a report closed of what you looking for (I guess) :

                    In Standard reports | Software license monitoring | Compliance, the Compliance details report will show you the details of your software compliance for all licenses and associated products. And you can filter the result by the group you've declare in SLM.

                     

                    Simon

                    • 7. Re: Custom Report LDMS 9 SP2 Licensed Software
                      Rookie

                      This report has not approached, it duplicates the data, said is not correct....

                       

                      I would report as - "Products - Monitored - Actions - Printable report of search results"

                      Here one column is not enough, Delta ... The number of available licenses

                      https://CORE/landesk/sam/samwebconsole/Reporting/GetReport.aspx?viewAs=Web&reportType=InstalledProductsSummary

                      how to edit it? its be in X:\Program Files (x86)\LANDesk\ManagementSuite\landesk\sam\samwebconsole\reporting\reports\InstalledProductSummary.rdlx

                      Where is it located? in DB i have not found...

                      <ReportItems>

                        <Table Name="ProductSummary">

                        <DataSetName>InstalledProductSummary</DataSetName>

                       

                      <DataSources>

                        <DataSource Name="WebServices">

                        <ConnectionProperties>

                        <DataProvider>OBJECT</DataProvider>

                        </ConnectionProperties>

                        </DataSource>

                        </DataSources>

                        <DataSets>

                        <DataSet Name="InstalledProductSummary">

                       

                       

                      Really need help ...

                      • 8. Re: Custom Report LDMS 9 SP2 Licensed Software
                        Simon Ma Employee

                        Hello,

                         

                        I've created a new report which show the number of purchase license, installation, unlicensed, used and borrow license.

                        Unfortunately, it doesn't take in count the group of device, but you can use the SQL query in the report to do it.

                         

                        Preview :

                        New License Compliance report.png

                        Simon

                        • 9. Re: Custom Report LDMS 9 SP2 Licensed Software
                          Rookie

                          This report does not take into account computer group, only the total number of installed software.

                          On my server about 15 organizations - groups of Computer, they each with its own accounting licenses.

                           

                          Need the same thing, but not grouped by product - and grouping by computer group

                          • 10. Re: Custom Report LDMS 9 SP2 Licensed Software
                            Rookie

                            Hi,

                             

                            the above SLM report by Simon looks great.

                            But the LDMS file is for version 9.5. Has someone an similar report for Version 9.6?

                             

                            Thanks

                            Jonny