Tables Description

This document describes KeyServer's tables, as well as the fields of each table, and how to use them.

These tables can be accessed via ODBC using our driver, ksODBC. ksODBC is installed by default by the ksp-admin installer. You can install just ksODBC without KeyConfigure if that is all you need on a particular computer. You can also configure KeyServer to export its internal data into tables that use the same structure — see Database Export for more on this. Note however that unless you have a compelling reason to make a second copy of all this data in an external database, it may be simpler and more efficient to use ksODBC to access the internal data directly.

Following all the table descriptions, there are some Example Queries.

Finally, if you are upgrading from a prior major release, there is information on adding new fields to your export database.

List of Tables

• Audit data which tracks what programs were discovered on each computer.

• Audit data which tracks "Packages" installed on each computer. This is meant primarily for KeyServer's internal use in determining what Products are installed.

• Audit data which tracks what products are on each computer, based on an examination of the programs discovered on each computer (KSAudits). This table is generated nightly according to the settings in KeyConfigure's General Settings dialog.

• KSComputerDevices is a cross table to associate computers and devices.
• New in KeyServer 7.8.

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

• Tags which have been applied to Computers.

• Contracts referenced by purchases and policies.

• This table holds displays, storage, printers, and other devices. Some of these can be populated directly by the KeyAccess client (displays, storage), while others can be imported using various integrations, or created manually.
• New in KeyServer 7.8 - replaces the KSPrinters table and now includes more types of devices and more properties.

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

• This table tracks what managed 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.

• Admin Journal entries which are currently displayed in the Admin Alerts Window (have not yet been acknowledged and move to the Admin Journal WIndow).

• Admin Journal entries which are currently displayed in the Admin Journal Window.

• Admin Journal Topics are the different types of entries that can appear in the Alerts and Journal. There can be many specific Journal entries within each Topic.

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

• KSLicensedUsers is a cross table to associate Users and Policies.
• It keeps track of each User who is licensed for a User based Policy.
• For other policy metrics it is empty.

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

• Packages. This table has entries corresponding to Packages, which help support Product Recognition.

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

• Tags which have been applied to Policies.

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

• Associates products with programs.

• Associates product families with individual product editions.

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

• Associates products with packages.

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

• Tags which have been applied to Products.

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

• Owner information for purchase codes

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

• Tags which have been applied to Purchases.

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

• Section data which aggregates Computer Divisions into Sections, and allow distinct KeyConfigure Administrators to manage different sets of Computers.

• 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 KSDevices.deviceStatus constants.

• Provides long and short string representations of the KSDevices.deviceCategory constants.

• Provides long and short string representations of the KSDevices.deviceType 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 KSComputers.computerLifeStage constants.

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

• Provides long and short string representations of the KSComputers.computerOSFamily 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 KSProducts.productStatus 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 entries for the User folders which can be defined in KeyConfigure.
• Note that a User can only belong to a single folder.

• This table holds information about KeyServer Users (The same ones that are shown in the Users Window of KeyConfigure).

• Tags which have been applied to Users.

• This table holds the currently logged in 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.

• This table does not exist internally in KeyServer.
• It is only used in exported databases.
• It keeps track of the progress made in an ongoing export.

Columns

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

KSAuditPackages

KSAuditProducts

KSComputerDevices

KSComputerDivisions

KSComputerGroupMembers

KSComputerGroups

KSComputers

KSComputerTags

KSContracts

KSDevices

KSHotfixes

KSInUse

KSJournal / KSJournalHistory

Both the KSJournal and KSJournalHistory tables hold Journal entries - either entries currently displayed in the Admin Alerts window or the Admin Journal window. Both tables contain the same fields as described below. The KSJournal table is not exported, since the entries are not yet resolved - they are expected to change and move over into the KSJournalHistory table, which is exported.

KSJournalTopics

KSLicensedComputers

KSLicensedUsers

KSLocations

KSPackages

0    type unknown
1    ISO 19770-2 software tag
2    MSI package ID
3    MSI or other installer package ID
4    OS X package "receipt" ID
5    package ID based on Add/Remove Programs string
6    IBM Software Delivery Platform package ID

KSPolicies

KSPolicyFolders

KSPolicyPools

KSPolicyProducts

KSPolicyTags

KSPortablesInUse

KSProductComponents

KSProductFamilies

KSProductFolders

KSProductPackages

KSProducts

KSProductTags

KSProgramFolders

KSPrograms

KSPurchaseAllocations

KSPurchaseCodes

KSPurchaseCodeOwners

KSPurchaseDocuments

KSPurchaseFolders

KSPurchaseItems

KSPurchaseOrders

KSPurchaseSupport

KSPurchaseTags

KSServers

KSSections

KSTerm*

KSUsage

KSUserFolders

KSUsers

KSUserTags

KSSessions

KSDbex (external only)

KSDbexStatus (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 we could produce an overview of how many computers within each Division have someone actively logged in:

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. Next let's look at a query to show specific computers within a certain division:

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 (PRGM 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 >= {ts '2017-12-01 00:00:00'} AND usageOtherTime < {ts '2018-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. 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. The syntax shown above is the correct syntax for ksODBC.

Updating Exported databases

If you have a 7.7 or earlier 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.8. 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. KeyServer will attempt to create the tables that are new, which will work as long as the account used for export has CREATE TABLE privileges.

In order to manually update the external database, here are the commands we recommend using. As long as the account used for export has CREATE TABLE privileges, you can leave out any of the CREATE TABLE commands that appear below. Note that if you are upgrading from an earlier version you should also look at the commands for upgrading from that version. See also: changes from 7.6 to 7.7.

DROP TABLE KSPrinters

ALTER TABLE KSComputers ADD ( computerLastImport DATETIME,
                    computerLatitude INT, computerLongitude INT,
                    computerRegion VARCHAR(64), computerBuilding VARCHAR(64),
                    computerRoom VARCHAR(64), computerOnLoanTo VARCHAR(64),
                    computerOnLoanUntil DATETIME, computerReplaceDate DATETIME,
                    computerServiceURL VARCHAR(256) )
ALTER TABLE KSPurchaseItems ALTER COLUMN purchaseName VARCHAR(128)
ALTER TABLE KSPurchaseItems ADD ( purchaseEmail VARCHAR(64) )
ALTER TABLE KSPurchaseCodeOwners ADD ( ownerCodeID VARCHAR(240), ownerProductID VARCHAR(40) )
ALTER TABLE KSUsers ADD ( userDepartment VARCHAR(64), userDomain VARCHAR(32),
                    userPhone VARCHAR(32), userEmail VARCHAR(64) )

CREATE TABLE KSDevices ( deviceID VARCHAR(40), deviceServerID INT, deviceName VARCHAR(64),
                    deviceCategory INT, deviceType INT, deviceStatus INT,
                    deviceLastCheck DATETIME, deviceSpec0 INT, deviceSpec1 INT,
                    deviceSpec2 INT, deviceSpec3 INT, deviceState0 INT,
                    deviceState1 INT, deviceState2 INT, deviceState3 INT,
                    deviceInkLevel INT, deviceInkLevels VARCHAR(20), deviceLifeStage INT,
                    deviceLatitude INT, deviceLongitude INT, deviceComputerID VARCHAR(64),
                    deviceDivisionID INT, deviceSectionID INT, deviceLastSeen DATE,
                    deviceLastImport DATE, deviceAddress VARCHAR(32), deviceServiceType INT,
                    deviceServiceID VARCHAR(64), deviceServiceURL VARCHAR(128),
                    deviceExternalURL VARCHAR(256), deviceDescription VARCHAR(256),
                    deviceDepartment VARCHAR(256), deviceSerialNumber VARCHAR(64),
                    deviceManufacturer VARCHAR(64), deviceModel VARCHAR(64),
                    deviceUUID VARCHAR(64), deviceFirmware VARCHAR(64),
                    deviceAssetID VARCHAR(64), deviceLocation VARCHAR(64),
                    deviceRegion VARCHAR(64), deviceBuilding VARCHAR(64),
                    deviceRoom VARCHAR(64), deviceOwner VARCHAR(64),
                    deviceOnLoanTo VARCHAR(64), deviceOnLoanUntil DATETIME,
                    deviceConfirmed DATETIME, deviceConfirmedBy VARCHAR(32),
                    devicePurchaseID VARCHAR(80), devicePurchaseDate DATETIME,
                    deviceLeaseEndDate DATETIME, deviceWarrantyDate DATETIME,
                    deviceReplaceDate DATETIME, deviceSalvageValue BIGINT,
                    deviceNotes VARCHAR(256), deviceFlags INT,
                    PRIMARY KEY ( deviceID, deviceServerID ) )

CREATE TABLE KSComputerDevices ( compdevID VARCHAR(168), compdevServerID INT,
                    compdevDeviceID VARCHAR(40), compdevComputerID VARCHAR(64),
                    compdevLastSeen DATETIME, compdevFlags INT,
                    PRIMARY KEY ( compdevID, compdevServerID ) )