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.

• This table tracks what controlled programs and licenses 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 licenses.
• It keeps track of each computer which has used a license.
• Note that if the license is node locked, the number of entries for this license will be less than or equal to the license total.
• If the license is a site wide or concurrent use license, there can be any number of entries for the license in this table, since it shows everywhere it has been used.

• Licenses. Each license is a policy controlling use of one or more programs.

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

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

• Shows what portable licenses are currently checked out.

• 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.

• Associates licenses with the programs they control.

• All known programs.

• 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 KSPrograms.programAction constants.

• 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 KSUsage.usageEvent constants.

• Provides long and short string representations of the KSLicenses.licenseType 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 KSComputers.computerProtocol constants.

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

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

• Usage records which track both program and license 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 users 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

KSInUse

KSLicensedComputers

KSLicenses

KSLocations

KSPools

KSPortablesInUse

KSProgramFolders

KSProgramLicenses

KSPrograms

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 programAction > 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 KSLicenses. 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 set to logged or controlled (look at KSTermAction). 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 6.2 fields

If you have a 6.1 KeyServer configured to export data to an external database, you may wish to update the table structure of your external tables to match the new structure of internal tables in KeyServer 6.2. There is no requirement that you do this - 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.

If you do wish to update the external database, here are the commands we recommend using:

DROP TABLE KSComputers
DROP TABLE KSLicenses
DROP TABLE KSLicensedComputers

DELETE FROM KSdbex WHERE dbexTable='KSComputers'
DELETE FROM KSdbex WHERE dbexTable='KSLicensedComputers'
DELETE FROM KSdbex WHERE dbexTable='KSLicenses'

CREATE TABLE KSComputers ( computerID VARCHAR(64) NOT NULL, computerServerID INT NOT NULL, 
		computerName VARCHAR(64), computerUserName VARCHAR(64), computerPlatform INT,
		computerProtocol INT, computerAddress VARCHAR(32), computerDomain VARCHAR(32),
		computerVirtualHost VARCHAR(64), computerGMTOffset INT, computerOSType INT,
		computerOSVersion INT, computerOSRelease INT, computerOS64Bit INT, computerCPUType INT,
		computerCPUCount INT, computerCPUClock INT, computerCPU64Bit INT, computerHyperThreading INT,
		computerMulticore INT, computerCapabilities INT, computerRAMSize INT, computerRAMType INT,
		computerRAMArray VARCHAR(200), computerDiskSize INT, computerFreeSpace INT,
		computerDiskManufacturer VARCHAR(32), computerDiskModel VARCHAR(32), computerDisplayWidth INT,
		computerDisplayHeight INT, computerDisplayDepth INT, computerDisplayManufacturer VARCHAR(32),
		computerDisplayModel VARCHAR(32), computerDisplaySerial VARCHAR(32),
		computerVideoManufacturer VARCHAR(32), computerVideoModel VARCHAR(32), computerVRAMSize INT,
		computerMACAddress VARCHAR(64), computerMACManufacturer VARCHAR(32),
		computerMACModel VARCHAR(32), computerWirelessAddress VARCHAR(32),
		computerWirelessManufacturer VARCHAR(32), computerWirelessModel VARCHAR(32),
		computerMACArray VARCHAR(250), computerSite VARCHAR(64), computerOEMSerial VARCHAR(64),
		computerOSSerial VARCHAR(64), computerBaseboardSerial VARCHAR(32),
		computerSystemSerial VARCHAR(32), computerManufacturer VARCHAR(64), computerModel VARCHAR(64),
		computerBIOSSerial VARCHAR(32), computerBIOSModel VARCHAR(32), computerBIOSVersion VARCHAR(32),
		computerCDROMPresent INT, computerCDROMWritable INT, computerCDROMManufacturer VARCHAR(32),
		computerCDROMModel VARCHAR(32), computerDVDPresent INT, computerDVDWritable INT,
		computerSoundManufacturer VARCHAR(32), computerSoundModel VARCHAR(32),
		computerLeaseExpiration DATETIME, computerLastLogin DATETIME, computerLastAudit DATETIME,
		computerBaseAudit DATETIME, computerClientVersion VARCHAR(16), computerUserSession INT,
		computerAcknowledged INT, computerAllowed INT, computerAudit INT, computerDivisionID INT,
		computerAssetID VARCHAR(64), computerLocation VARCHAR(64), computerOwner VARCHAR(64),
		computerConfirmed DATETIME, computerConfirmedBy VARCHAR(64), computerNotes VARCHAR(255),
		PRIMARY KEY (computerID,computerServerID) )
CREATE TABLE KSLicensedComputers ( licenseeID VARCHAR(96) NOT NULL, licenseeServerID INT NOT NULL,
		licenseeComputerID VARCHAR(64), licenseeLicenseID VARCHAR(32), licenseeAcknowledged INT,
		licenseeLastUsed DATETIME, licenseeLeaseDate DATETIME, licenseeLeaseExpiration DATETIME,
		PRIMARY KEY (licenseeID,licenseeServerID) )
CREATE TABLE KSLicenses ( licenseID VARCHAR(32) NOT NULL, licenseServerID INT NOT NULL,
		licenseName VARCHAR(64), licenseTotal INT, licenseType INT, licenseNodeLock INT,
		licenseLeaseTime INT, licenseExpiration DATETIME, licenseCurrUsers INT,
		licenseCurrWaiting INT, licenseNotes VARCHAR(255), licenseServiceFlags INT,
		licenseFlags INT, PRIMARY KEY (licenseID,licenseServerID) )
CREATE TABLE KSTermRAMType ( termID INT NOT NULL, termValue VARCHAR(255),
		termAbbreviation VARCHAR(32), PRIMARY KEY (termID) )

ALTER TABLE KSAudits ADD ( auditSerialNumber VARCHAR(255) )
ALTER TABLE KSUsage ADD ( usageFlags INT )

Related Topics