Tables Description

This document lists KeyServer's data tables, the fields of each table, and details about them.

Typical consumption and interaction with the data is done through KeyConfigure or the Web UI. The tables can be queried in their raw form via the ksql Extra in the Web UI. They can also 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.

At the bottom of this document 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 (ksPrograms) 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, but is useful for things like the HotFixes report.

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

• All Computer records as created by any of the following: Connection from KeyAccess agent, Import from another source by script (e.g. Jamf or InTune), Import from CSV, Manual addition in Web UI.

• KSComputerDevices is a cross table to associate computers and devices.

• Computer Divisions ("folders") which have been created in KeyConfigure or the Web UI or programmatically by Client Authentication.
• These appear on the left side computers window/ page. Often based on Active Directory OUs.
• There is no entry for the Uncategorized division as that is the lack of a 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 (e.g. AD User groups).

• Events showing changes to computer objects over time (e.g. change in Lifecycle Stage).

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

• Events showing changes to device objects over time.

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

• Models. This table has entries corresponding to items in the Models window in KeyConfigure. New in 8.0.

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

• Events showing changes to policy objects over time.

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

• Provides long and short string representations of the KSComputers.ComputerStatus 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 KSPolicies.Enforcement 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 KSDevices.PrinterService 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.

The Type column contains these possible values:

KSAudits

KSAuditPackages

KSAuditProducts

KSComputerDevices

KSComputerDivisions

KSComputerGroupMembers

KSComputerGroups

KSComputers

KSComputerHistory

KSComputerTags

KSContracts

KSDevices

KSDeviceHistory

KSHotfixes

KSInUse

KSJournal

KSJournalTopics

KSLicensedComputers

KSLicensedUsers

KSLocations

KSModels

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

KSPolicyHistory

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

KSDbexStatus

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.9 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 8.0. 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.8 to 7.9.

ALTER TABLE KSComputers ADD ( computerInService DATETIME, computerBaseModelID VARCHAR(32), computerReplacementModelID VARCHAR(32), computerReplacementStatus INT, computerEOLAction INT )

ALTER TABLE KSdevices ADD ( deviceInService DATETIME, deviceBaseModelID VARCHAR(32), deviceReplacementModelID VARCHAR(32), deviceReplacementStatus INT, deviceEOLAction INT )

CREATE TABLE KSModels ( modelID VARCHAR(32), modelServerID INT, modelName VARCHAR(64), modelFolderID INT, modelCategory INT, modelManufacturer VARCHAR(64), modelModelName VARCHAR(64), modelFormFactor INT, modelCPUName VARCHAR(32), modelCPUCount INT, modelCPUClock INT, modelRAMSize INT, modelDiskSize INT, modelGraphicsCard VARCHAR(32), modelReleaseDate DATETIME, modelEndSupportDate DATETIME, modelCost BIGINT, modelSalvageValue BIGINT, modelReplacementModelID VARCHAR(32), modelReseller VARCHAR(32), modelResellerSKU VARCHAR(32), modelPictureURL VARCHAR(64), modelManufacturerURL VARCHAR(64), modelResellerURL VARCHAR(64), modelAdditional VARCHAR(256), modelNotes VARCHAR(256), modelFlags INT )