5 Replies Latest reply on Jul 1, 2016 7:02 AM by phoffmann

    How can I get the license expiration date querying LANDESK database

    Rookie

      Good Day Community.

       

      I have a demand to proactive monitor the license expiratin date, based on that I was wondering if there a place in the database, registry or where else I can get this information.

       

      Thanks in advance

       

      Paulo

        • 1. Re: How can I get the license expiration date querying LANDESK database
          phoffmann SupportEmployee

          I'm assuming you're not talking about the LANDesk License here - but some other product licenses as part of SLM (Software License Monitoring)?

           

          Assuming you've filled out the relevant product license information (so - assigned a renewal date & so on) to products you're monitoring, you can just run a normal existing report for that & don't need to dive into the database.

           

          Check the following location for the normal report in the REPORTS tool:

          REPORTS >> STANDARD REPORTS >> SOFTWARE LICENSE MONITORING

           

          You'll find the following useful reports:

          - AUDITS\Monitored Products without licenses ==> (so you know what needs to be populated)

          - LICENSE OPTIMIZATION\Licenses without expiration date ==> (so you know what needs to be populated)

          - LICENSE OPTIMIZATION\Licenses with expiration date renewal cost ==> this will be the most useful thing for you, if the given info is filled out.

           

          CAVEAT - obviously (as I indicated) this requires you to have assigned those licenses against the relevant products within SLM ... if you haven't, then LANDesk won't know about them.

           

          Does that help you as a started? No problem in pointing at the DB if need be (it's not super-secret stuff) - I just figured whether the regular stuff might actually help / be enough first?

           

          Oh and the license expiration stuff (along with all the other license stuff) is in 1 or 2 tables (if memory serves), so it's not hard to access in principle.

          • 2. Re: How can I get the license expiration date querying LANDESK database
            Rookie

            Thanks Phoffmann,

             

            Your answer gave me a goos direction where to look at. My only homework now, as I'm running a full infrastructure health check using powershell scripts, will be find thos report information in the SQL database. At least, as far as I know, report get their information queriyng the database.

             

            Anyway, your direction gave me a good start point.

             

            Thanks in advance

            • 3. Re: How can I get the license expiration date querying LANDESK database
              phoffmann SupportEmployee

              The database side stuff is pretty straight forward.

               

              The individually entered licenses themselves are kept in the SLM_SoftwareLicense table (that's the single, most straight forward place to pull them).

               

              There's a bunch more to be said here (links to products from PRODUCT table) via PRODUCT_IDN in the SLM_LicenseProductREF table ..... that should be enough to get you a leg up on the SQL side of things. The DB-side stuff is pretty straight forward (as long as you're just looking to pull data out / report on).

              • 4. Re: How can I get the license expiration date querying LANDESK database
                Rookie

                Great...

                 

                Thanks, that's what I was looking for...

                 

                Cheers

                • 5. Re: How can I get the license expiration date querying LANDESK database
                  phoffmann SupportEmployee

                  Happy to help.

                   

                  • As a general point - most of the DB is pretty intuitive (and decently named), so while there's a lot of data in it, it's not that hard to figure stuff out.
                  • Remember you can copy & edit existing reports to see what we do from a SQL point of view / data sources (if you're any good with reporting editors). Depending on your comfort levels / areas of expertise, that may be easier than trawling through SQL.

                   

                  Here's a SQL script that's also been "worth its weight in gold" over the years that adds a "SearchAllTables" function for a string.

                   

                  CREATE PROC SearchAllTables
                  (
                  @SearchStr nvarchar(100)
                  )
                  AS
                  BEGIN
                  
                  -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
                  -- Purpose: To search all columns of all tables for a given search string
                  -- Written by: Narayana Vyas Kondreddi
                  -- Site: http://vyaskn.tripod.com
                  -- Tested on: SQL Server 7.0 and SQL Server 2000
                  -- Date modified: 28th July 2002 22:50 GMT
                  
                  
                  CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
                  
                  SET NOCOUNT ON
                  
                  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
                  SET @TableName = ''
                  SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
                  
                  WHILE @TableName IS NOT NULL
                  BEGIN
                  SET @ColumnName = ''
                  SET @TableName =
                  (
                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                  FROM INFORMATION_SCHEMA.TABLES
                  WHERE TABLE_TYPE = 'BASE TABLE'
                  AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                  AND OBJECTPROPERTY(
                  OBJECT_ID(
                  QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                  ), 'IsMSShipped'
                  ) = 0
                  )
                  
                  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
                  BEGIN
                  SET @ColumnName =
                  (
                  SELECT MIN(QUOTENAME(COLUMN_NAME))
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                  AND TABLE_NAME = PARSENAME(@TableName, 1)
                  AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                  AND QUOTENAME(COLUMN_NAME) > @ColumnName
                  )
                  
                  IF @ColumnName IS NOT NULL
                  BEGIN
                  INSERT INTO #Results
                  EXEC
                  (
                  'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                  FROM ' + @TableName + ' (NOLOCK) ' +
                  ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                  )
                  END
                  END
                  END
                  
                  SELECT ColumnName, ColumnValue FROM #Results
                  END
                  
                  -- How to run the stored procedure.
                  -- Execute the following in SQL Query Analyzer:
                  -- EXEC SearchAllTables 'String'
                  
                  

                   

                  ... that may make life much easier for you going forward if need be .

                   

                  Usage is like this:

                  exec SearchAllTables 'MyString'