KeyServer Tables Description

This document describes each table, as well as each field of each table, and how to use them. See Export Configuration for a description of how to configure KeyServer to export these tables to an external database. Following all the table descriptions, there are some Example Queries.

List of Tables

• Audit data which tracks what programs are on each machine.

• Computer Divisions which have been configured in KeyConfigure.
• These are the divisions which appear on the left side of the computers window.
• There is no entry for the Uncategorized division.

• KSComputerGroupMembers is a cross table to associate computers and groups.
• This table only contains entries for computers which have been explicitly added to Computer Groups. If a group has a location defined, but no computers listed explicitly, this table will not have any entries referring to that group.

• Computer Groups which have been configured in KeyConfigure.
• This table does not contain entries for groups which are defined by external authorization methods.

• Computers which have connected to the KeyServer at some point.

• Contracts referenced by purchases and policies.

• Hotfixes (Windows only) which have been seen on client computers.

• This table tracks what controlled programs, products, and policies are currently in use on each computer, by each user.
• Note that it is probably not too useful for reports, since it changes so quickly.
• This table is not exported, since it changes so quickly.

• KSLicensedComputers is a cross table to associate computers and policies.
• It keeps track of each computer which has used a policy.
• Note that if the policy is a Node policy, the number of entries for this policy will be less than or equal to the policy total.
• If the policy is a site wide or concurrent use policy, there can be any number of entries for the policy in this table, since it shows everywhere it has been used.

• Locations. This table has entries corresponding to items in the Locations window in KeyConfigure.

• Policies. Each policy may apply to use of one or more products.

• This table holds entries for the policy folders which can be defined in KeyConfigure.
• Note that a policy can only belong to a single folder.

• All policies have at least one pool
• This is where the group restriction is stored, if there is one
• For custom policies, the rows in this table correspond to the pools in the Policy Details window.

• Associates policies with the products they control.

• Shows what portable licenses are currently checked out.
• This table is not exported.

• Associates products with programs.

• This table holds entries for the product folders which can be defined in KeyConfigure.
• Note that a product can only belong to a single folder.

• Product, as listed in the main Products window of KeyConfigure.

• This table holds entries for the program folders which can be defined in KeyConfigure.
• Note that a program can only belong to a single folder.

• All known programs.

• Allocations of entitlements from one purchase to another - to support dependent purchases.

• Activation codes or Serial Numbers associated with each purchase.

• Documents or links associated with each purchase.

• This table holds entries for the purchase folders which can be defined in KeyConfigure.
• Note that a purchase can only belong to a single folder.

• Each purchase item corresponds to a line in the Purchases window of KeyConfigure.
• A purchase item is part of a purchase order.

• A purchase order contains the common values of a set of related purchase items.
• Purchase orders aggregate related individual purchase items.

• For a dependent purchase, which requires support, this table records which products can be used to support the purchase.

• Table containing one entry for every KeyServer.
• When connecting directly to a KeyServer, this table will have only one entry.
• When exporting data from multiple KeyServers to a single SQL server, this table will have multiple entries.

• Provides long and short string representations of the KSComputers.computerAllowed constants.

• Provides long and short string representations of the KSComputers.computerCPUType constants.

• Provides long and short string representations of the KSPurchaseItems.purchaseCurrency constants.

• Provides long and short string representations of the KSPurchaseItems.entitlementType constants.

• Provides long and short string representations of the KSUsage.usageEvent constants.

• Provides long and short string representations of the KSPolicies.policyMetric constants.

• Provides long and short string representations of the KSComputers.computerOSType constants.

• Provides long and short string representations of the KSComputers.computerPlatform constants.

• Provides long and short string representations of the KSPolicies.policyAction constants.

• Provides long and short string representations of the KSComputers.computerProtocol constants.

• Provides long and short string representations of the KSPurchaseItems.purchaseType constants.

• Provides long and short string representations of the KSComputers.computerRAMType constants.

• Provides long and short string representations of the KSUsage.usageReason constants.

• Provides long and short string representations of the KSPrograms.programStatus constants.

• Usage records which track both program and policy usage, as well as client logins, logouts, audits, etc.

• This table holds the current users of KeyServer (The same ones that are shown in the Connected Clients Window of KeyConfigure).
• Note that it is probably not too useful for reports, since it changes so quickly.
• This table is not exported, since it changes so quickly.

• This table does not exist internally in KeyServer.
• It is only used in exported databases.
• It keeps track of the last time that each table was exported, so that exports can be incremental.

The following tables describe the columns contained in each table. KeyServer's internal databases (accessible through ksODBC) contain the same columns as exported data. However, the data types might be slightly different. The types listed below are correct for exported data. If you need to create the tables by hand before exporting data from KeyServer, you should use the types contained in the tables below.

KSAudits

KSComputerDivisions

KSComputerGroupMembers

KSComputerGroups

KSComputers

KSContracts

KSHotfixes

KSInUse

KSLicensedComputers

KSLocations

KSPolicies (new in KeyServer 7.0)

KSPolicyFolders

KSPolicyPools (new in KeyServer 7.0)

KSPolicyProducts

KSPortablesInUse

KSProductComponents

KSProductFolders

KSProducts

KSProgramFolders

KSPrograms

KSPurchaseAllocations

KSPurchaseCodes

KSPurchaseDocuments

KSPurchaseFolders

KSPurchaseItems

KSPurchaseOrders

KSPurchaseSupport

KSServers

KSTerm*

KSUsage

KSUsers

KSDbex (external only)

Example Queries

To give a better idea of how these tables can be used, here are some example queries. First, let's look at how one of the Online Reports in KeyReporter generates results. The query for showing an overview of all Divisions is:

	SELECT MAX(divisionName),MAX(divisionID),COUNT(computerUserSession),
				COUNT(computerID)-COUNT(computerUserSession),COUNT(computerID),
				(100*COUNT(computerUserSession))/COUNT(computerID),
				100-((100*COUNT(computerUserSession))/COUNT(computerID))
			FROM
				{oj KSComputers LEFT OUTER JOIN KSComputerDivisions ON computerDivisionID=divisionID
					AND computerServerID=divisionServerID}
			WHERE
				(divisionName IS NOT NULL)
			GROUP BY
				divisionName,divisionID

Because of the GROUP BY, this query returns one row for each Division. It selects the division name, division id (which can then be used to restrict a query to just that division), number of computers in the division with someone logged in, number of computers in the division without someone logged in, total number of computers, percent logged in, and percent not logged in. KeyReporter does not display the division ID, but uses it if one of the divisions is clicked on. It uses the last two columns to generate the graphical representation of how full the division is. Next let's look at the query it uses once a specific division is clicked:

	SELECT computerName,computerUserSession,computerUserName
			FROM
				KSComputers
			WHERE
				computerDivisionID=1234234
			ORDER BY
				computerName

1234234 gets replaced with the appropriate divisionID, which was selected in the summary query above. The first column is the name of each computer within the division (this may not quite match what KeyConfigure displays in the Computers window - see the notes about the KSComputer fields for more). The second column is NULL if no one is currently logged in to the computer, and non NULL if someone is logged in to the computer. The third column is the name of the last user who logged in to the computer (which is the currently logged in user if there is someone logged in).

Next, here is an example that is somewhat like what the Usage (PROG x user) report might do:

	SELECT MAX(programVariantName),MAX(programVariantVersion),MAX(usageUser),
				COUNT(*),SUM(usageTime),MAX(usageWhen)
			FROM
				{oj KSUsage INNER JOIN KSPrograms ON KSUsage.usageProgramID=KSPrograms.programID
					AND KSUsage.usageServerID=KSPrograms.programServerID }
			WHERE
				(usageEvent=4 OR usageEvent=9 OR usageEvent=11 OR usageEvent=13) AND programStatus > 1
				AND usageWhen >= '2007-12-01 00:00:00' AND usageOtherTime < '2008-01-01 00:00:00'
			GROUP BY
				programVariantName,programVariant,usageUser

First of all, notice the join between KSUsage and KSPrograms. Technically, the usageServerID to programServerID should be included as a condition, but it is really only important if you have multiple KeyServers exporting to the same database. This is very similar to how you would do joins between KSUsage and KSComputers or KSPolicies. Next, look at the WHERE clause. First of all, it selects all the various types of program quit events (you can find the event codes by looking at the KSTermEvent table). Additionally, it only selects events for programs which are currently a component of a product (look at KSTermStatus). Finally, it selects events for which at least part of the usage was in December of 2007 - the quit was after December 1 and the corresponding launch was before January 1. With these various events, it groups by programVariantName and programVariant (since multiple programs may have the same Variant name), then by user. Finally, the fields it displays show summarization of usage. The first three fields identify which program and user this line is for. Next, the total number of quits, duration of usage, and most recent quit are selected. The actual KeyConfigure report is quite a bit more complicated than this. Most importantly, it selects every event and then processes and summarizes them within the report module, instead of having the SQL query doing the grouping and summarization. This allows it only count usage time that overlaps the selected time range. For example, if a program were launched on November 25 and left running until Dec 1, the SQL query above would count a whole week of usage, but the internal report would calculate the overlap and only count 1 day of usage. The internal report is also able to show a line summarizing usage for each program as a whole, as well as the details of each program/user combination. It also has a few more fields than what is shown above, and formats usage durations as hours:minutes instead of a raw number of seconds.

Note also that the syntax for the join and for date stamps has to be adjusted depending on what ODBC driver is being used.

Updating Exported databases for new 7.0 fields

If you have a 6.2 KeyServer configured to export data to an external database, you should update the table structure of your external tables to match the new structure of internal tables in KeyServer 7.0. In many cases the easiest way to do this is to simply delete external tables and allow them to be recreated. Note that if your exported tables have data that no longer exists in KeyServer, dropping the tables will permanently remove this data. If you have such data you probably want to alter your existing tables instead of dropping and recreating them. If you keep the same structure as before, KeyServer will continue to add data to any columns that exist in the external database, but data for the new columns will be dropped. Internal reports in KeyConfigure 7.0 may not work against external data until tables are updated.

If you do wish to manually update the external database, here are the commands we recommend using. This should be done at any point after upgrading KeyServer.

DROP TABLE KSComputers
DROP TABLE KSLicensedComputers
DROP TABLE KSLicenses
DROP TABLE KSPools
DROP TABLE KSProgramFolders
DROP TABLE KSProgramLicenses
DROP TABLE KSPrograms
DROP TABLE KSTermAction
DROP TABLE KSTermLicType

DELETE FROM KSdbex WHERE dbexTable='KSComputers'
DELETE FROM KSdbex WHERE dbexTable='KSLicensedComputers'
DELETE FROM KSdbex WHERE dbexTable='KSLicenses'
DELETE FROM KSdbex WHERE dbexTable='KSPools'
DELETE FROM KSdbex WHERE dbexTable='KSProgramFolders'
DELETE FROM KSdbex WHERE dbexTable='KSProgramLicenses'
DELETE FROM KSdbex WHERE dbexTable='KSPrograms'

ALTER TABLE KSAudits DROP COLUMN auditLicenseID
ALTER TABLE KSUsage ADD ( usageProductID VARCHAR(40) )
ALTER TABLE KSUsage ADD ( usagePolicyID VARCHAR(32) )
UPDATE KSUsage SET usagePolicyID=usageLicenseID
ALTER TABLE KSUsage DROP COLUMN usageLicenseID

Also, if you are converting from 6.1 to 7.0 you should also look at the table changes from 6.1 to 6.2, which can be found here.