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.
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
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).
Thanks, that's what I was looking for...
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'