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 K2Admin 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 Export Configuration 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.

List of Tables

KSAudits KSAuditPackages KSAuditProducts KSComputerDivisions KSComputerGroupMembers KSComputerGroups KSComputers KSComputerTags (new in KeyServer 7.4) KSContracts KSHotfixes KSInUse KSJournal (new in KeyServer 7.4) KSJournalHistory (new in KeyServer 7.4) KSJournalTopics (new in KeyServer 7.4) KSLicensedComputers KSLicensedUsers KSLocations KSPackages KSPolicies KSPolicyFolders KSPolicyPools KSPolicyProducts KSPolicyTags (new in KeyServer 7.4) KSPortablesInUse KSProductComponents KSProductFolders KSProducts KSProductTags (new in KeyServer 7.4) KSProgramFolders KSPrograms KSPurchaseAllocations KSPurchaseCodes KSPurchaseDocuments KSPurchaseFolders KSPurchaseItems KSPurchaseOrders KSPurchaseSupport KSServers KSSections KSTermAllowed KSTermCPUType KSTermCurrencyType KSTermEntitlementType KSTermEvent KSTermLifeStage KSTermMetric KSTermOSFamily KSTermOSType KSTermPlatform KSTermPolicyAction KSTermProductStatus KSTermProtocol KSTermPurchaseType KSTermRAMType KSTermReason KSTermStatus KSUsage KSUserFolders KSUsers KSUserTags (new in KeyServer 7.4) KSSessions KSDbex KSDbexStatus

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

auditID VARCHAR(208) Unique ID for this audit entry.
auditServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
auditComputerID VARCHAR(64) Part of the auditID. The computer which this audit came from. References the computerID field of KSComputers, and can be used to join the two tables.
auditProgramID VARCHAR(80) Part of the auditID. The ID of the program for this entry. References the programID field of KSPrograms, and can be used to join the two tables.
auditSize INT Size of the program on the client computer. Note that if on a single computer there are 3 copies of the same version of the same program, this field shows the size of one of them - the different files could have different sizes but only one size is recorded in KSAudits.
auditCount INT The number of copies of this program that were found on the computer.
auditFirstSeen DATETIME Date when the program was first seen on this computer.
auditLastSeen DATETIME Date when the program was reported as no longer present on this computer. This is only filled in if auditCount is zero. Otherwise it is null.
auditLastUsed DATETIME Date when the program was last used on this computer.
auditSerialNumber VARCHAR(256) The installed serial number of this program, if KeyAccess knows how to find it.
auditPath VARCHAR(256) In 6.2, this field now contains a path for this program on the local computer. Note that if there are multiple copies of the exact same version of a program installed, there will only be one row in the KSAudits table, so there can only be one path recorded.

KSAuditPackages

audpkgID VARCHAR(264) Unique ID for this audit products entry.
audpkgServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
audpkgComputerID VARCHAR(64) Part of the audpkgID. The computer which this audit came from. References the computerID field of KSComputers, and can be used to join the two tables.
audpkgPackageID VARCHAR(64) Part of the audpkgID. The ID of the package for this entry. References the packageID field of KSPackages, and can be used to join the two tables.
audpkgFirstSeen DATETIME Date when the package was first seen on this computer.
audpkgLastSeen DATETIME Date when the package was reported as no longer present on this computer. This is only filled in if the package is no longer present. Otherwise it is null.
audpkgLastScan DATETIME Date when a scan last saw that this package was still present.
audpkgSerialNumber VARCHAR(256) The installed serial number of this package, if KeyAccess knows how to find it.

KSAuditProducts

audprodID VARCHAR(168) Unique ID for this audit products entry.
audprodServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
audprodComputerID VARCHAR(64) Part of the audprodID. The computer which this audit came from. References the computerID field of KSComputers, and can be used to join the two tables.
audprodProductID VARCHAR(40) Part of the audprodID. The ID of the product for this entry. References the productID field of KSProducts, and can be used to join the two tables. See also audprodAltProductID below.
audprodAltProductID VARCHAR(40) The ID of the alternative product for this entry. References the productID field of KSProducts, and can be used to join the two tables. If this field is non-NULL, the product given in audprodProductID is a better fit for this computer given the program usage recorded for that computer. When querying for products that are installed (without considering usage), the query should include a WHERE clause like: (audprodAltProductID IS NULL)
audprodFirstSeen DATETIME Date when the product was first seen on this computer.
audprodLastUsed DATETIME Date when a program within this product was last used on this computer.
audprodSerialNumber VARCHAR(256) The installed serial number of this product, if KeyAccess knows how to find it.
audprodFlags INT Ignore.

KSComputerDivisions

divisionID INT Unique ID for this division.
divisionServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
divisionName VARCHAR(64) The name of the division.
divisionColor INT The color which KeyConfigure uses to display the division. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
divisionSectionID INT ID of the Section associated with this division. References the sectionID field of KSSections, and can be used to join the two tables.
divisionNotes VARCHAR(256) Notes for the division. Can't be set in KeyConfigure.
divisionFlags INT Ignore.

KSComputerGroupMembers

memberID VARCHAR(136) Unique ID for this association between one computer and one group.
memberServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
memberComputerID VARCHAR(64) Part of the memberID. References the computerID field of KSComputers, and can be used to join the two tables.
memberGroupID VARCHAR(64) Part of the memberID. References the groupID field of KSComputerGroups, and can be used to join the two tables.
memberAcknowledged INT Ignore.
memberLastUsed DATETIME The last time the computer used membership in the group in order to use a policy.

KSComputerGroups

groupID VARCHAR(64) Unique ID for this computer group.
groupServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
groupNumMembers INT Number of members currently in the group.
groupMaxMembers INT Ignore.
groupNotes VARCHAR(256) Notes for the group, as defined in KeyConfigure.
groupFlags INT Ignore.

KSComputers

computerID VARCHAR(64) Unique ID for this computer. Note that if the computerID starts with 'W', 'L', 'T', or 'K', the ID is what is shown in KeyConfigure's Computers window, instead of the name.
computerServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
computerName VARCHAR(64) Name of the computer. Note that if the computerID starts with 'W', 'L', 'T', or 'K', the ID is what is shown in KeyConfigure's Computers window, instead of the name.
computerUserName VARCHAR(32) Name of the user most recently logged into the computer.
computerPlatform INT A code which represents the platform type of the computer. Use KSTermPlatform to convert these constants to string representations.
computerProtocol INT Specifies how the computer connects to KeyServer. Use the KSTermProtocol table to look up these constants.
computerAddress VARCHAR(32) The IP address of the computer, as a string.
computerDomain VARCHAR(32) The domain that the computer belongs to.
computerDescription VARCHAR(64) Description of the computer - currently Windows only.
computerVirtualHost VARCHAR(64) If KeyAccess version 6.2.0.6 or higher is running in Virtual PC, this field contains the name of the "real" host computer where Virtual PC is running.
computerGMTOffset INT Offset from GMT in seconds.
computerOSFamily INT Code which specifies the OS family. Use KSTermOSFamily to convert these constants to string representations.
computerOSType INT Code which specifies the OS type. Use KSTermOSType to convert these constants to string representations.
computerOSVersion INT Number which specifies the OS version. first byte is major version, second is minor version, last two bytes is build number.
  • 10.6.8 would be 0x0a060008 in hex (or 168165384 in decimal)
  • 5.1 build 26000 would be 0x05010A28 in hex (or 83954216 in decimal).
computerOSRelease INT Number which specifies the release number of the OS.
  • On Macintosh, this field stores the revision version of the OS. Usually this field will be either 0 or hex 0x01008000 (decimal 16809984), which means revision 1.
  • On Windows, this field stores the SP number in the third byte. For example, SP2 would be 0x00020000 in hex (or 131072 in decimal).
computerOSInstallDate DATETIME Original Install Date of the current OS.
computerOS64Bit INT Whether or not the OS is 64 bit.
computerCPUType INT Code which specifies the type of CPU in the computer. Use KSTermCPUType to convert these constants to string representations.
computerCPUCount INT How many CPU cores the computer has. Computers with KeyAccess 6.0.2.2 or earlier will always show 1 core. Computers with KeyAccess 6.0.2.3 or better will show the correct number of cores.
computerCPUSockets INT (new in KeyServer 7.4.0.2)
How many separate CPUs (sockets) the computer has. Computers with KeyAccess 7.4.0.1 or earlier will always show 1 socket. Computers with KeyAccess 7.4.0.2 or better will show the correct number of sockets.
computerCPUClock INT Clock speed of the CPU, in Mhz. For machines with multiple CPUs, this is the clock speed of each CPU.
computerCPU64Bit INT Whether or not the CPU is 64 bit.
computerHyperThreading INT Whether or not the CPU supports hyperthreading.
computerMulticore INT Whether or not the CPU is multi core.
computerCapabilities INT Additional CPU capabilities.
computerPVUs INT (new in KeyServer 7.4)
Number of PVUs for this computer.
computerRAMSize INT Kilobytes of RAM on the computer.
computerRAMType INT Code which specifies the type of RAM in the computer. Use KSTermRAMType to convert these constants to string representations.
computerRAMArray VARCHAR(520) An array which records the RAM chips which are present.
computerDiskSize INT Size of all local disks combined, in Kilobytes.
computerFreeSpace INT Kilobytes of free space on disk.
computerDiskManufacturer VARCHAR(32) Manufacturer of the system disk.
computerDiskModel VARCHAR(32) Model of the system disk.
computerDisplayWidth INT Width in pixels of the primary display.
computerDisplayHeight INT Height in pixels of the primary display.
computerDisplayDepth INT Bit depth of the primary display.
computerDisplayManufacturer VARCHAR(32) Manufacturer of the display.
computerDisplayModel VARCHAR(32) Model of the display.
computerDisplaySerial VARCHAR(32) Serial number of the display.
computerVideoManufacturer VARCHAR(32) Manufacturer of the video card.
computerVideoModel VARCHAR(32) Model of the video card.
computerVRAMSize INT Kilobytes of Video RAM on the computer.
computerMACAddress VARCHAR(32) MAC address of the primary NIC.
computerMACManufacturer VARCHAR(32) Manufacturer of the primary NIC.
computerMACModel VARCHAR(32) Model of the primary NIC.
computerWirelessAddress VARCHAR(32) MAC address of the wireless interface, if there is one.
computerWirelessManufacturer VARCHAR(32) Manufacturer of the wireless interface, if there is one.
computerWirelessModel VARCHAR(32) Model of the wireless interface, if there is one.
computerMACArray VARCHAR(520) An array which records MAC addresses for all interfaces.
computerDepartment VARCHAR(64) A value that KeyAccess fills in with whatever value it finds in HKLM\SYSTEM\CurrentControlSet\Services\KeyAccess\Settings\options\site or in the site property of /Library/Preferences/com.sassafras.KeyAccess.plist
computerOEMSerial VARCHAR(32) Manufacturer's serial number.
computerOSSerial VARCHAR(32) Serial number of the OS, if it is available.
computerBaseboardSerial VARCHAR(32) The serial number of the Win32_BaseBoard object. This serial number may also be used as the computerOEMSerial.
computerSystemSerial VARCHAR(32) The serial number of the Win32_SystemEnclosure object. This serial number may also be used as the computerOEMSerial.
computerUUID VARCHAR(64) The UUID of the client computer.
computerSCCMUniqueID VARCHAR(64) The SCCM Unique ID.
computerManufacturer VARCHAR(64) Manufacturer of the computer.
computerModel VARCHAR(64) Model name of the computer.
computerBIOSSerial VARCHAR(32) The serial number of the Win32_BIOS object. This serial number may also be used as the computerOEMSerial.
computerBIOSModel VARCHAR(32) Model of the BIOS.
computerBIOSVersion VARCHAR(32) Version of the BIOS.
computerCDROMPresent INT Whether or not there is a CD-ROM drive.
computerCDROMWritable INT Whether or not there is a writable CD-ROM drive.
computerCDROMManufacturer VARCHAR(32) Manufacturer of the CD-ROM, if there is one.
computerCDROMModel VARCHAR(32) Model of the CD-ROM, if there is one.
computerDVDPresent INT Whether or not there is a DVD drive.
computerDVDWritable INT Whether or not there is a writable DVD drive.
computerSoundManufacturer VARCHAR(32) Manufacturer of the sound card.
computerSoundModel VARCHAR(32) Model of the sound card.
computerLeaseExpiration DATETIME Last date and time at which this computer's KeyServer seat will expire if there is not further activity.
computerLastLogin DATETIME Last date and time at which this computer logged in to the KeyServer.
computerLastAudit DATETIME Last date and time at which this computer was audited.
computerBaseAudit DATETIME Date and time of this computer's Baseline Audit.
computerClientVersion VARCHAR(16) Version number of KeyAccess on the computer, as a string.
computerUserSession INT This refers to the user session number on KeyServer. Essentially useless for reports.
computerAcknowledged INT A boolean representing whether or not the computer is Anchored.
computerAllowed INT What type Login this client uses. Use KSTermAllowed to convert these constants to string representations.
computerAudit INT Boolean which indicates whether this computer will audit automatically.
computerLifeStage INT Which stage of life cycle this computer is in, as configured in KeyConfigure Use KSTermLifeStage to see which number refers to each policy metric.
computerDivisionID INT ID of the division associated with this computer. References the divisionID field of KSComputerDivisions, and can be used to join the two tables.
computerSectionID INT ID of the Section associated with this computer's division. References the sectionID field of KSSections, and can be used to join the two tables.
computerAssetID VARCHAR(32) Asset ID, as defined in KeyConfigure in Asset pane of Computer Details window.
computerLocation VARCHAR(64) Location, as defined in KeyConfigure in Asset pane of Computer Details window.
computerOwner VARCHAR(64) Owner, as defined in KeyConfigure in Asset pane of Computer Details window.
computerConfirmed DATETIME Date when computer was confirmed, as defined in KeyConfigure in Asset pane of Computer Details window.
computerConfirmedBy VARCHAR(32) Who confirmed the computer, as defined in KeyConfigure in Asset pane of Computer Details window.
computerNotes VARCHAR(256) Notes for the computer, as defined in KeyConfigure in Asset pane of Computer Details window.
computerFlags INT Ignore.
computerCustom1 VARCHAR(64) There are 10 custom fields in the Computers table. In order to use these fields, they must first be given names - right-click a column header in the Computers window and select "Define Columns...". This will bring up the Custom Computer Columns dialog, which has 10 text fields. Once values have been entered for these fields, the columns will be available via ksODBC and will be exported. For example, if you type "Segment" in the first field, then the computers table will show a column named "computerSegment". These 10 fields are dynamic and depend on configuring Labels for the custom fields. For more, see the Custom Computer Columns document.

KSComputerTags

tagID VARCHAR(264) Unique ID for this Computer tag.
tagServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
tagComputerID VARCHAR(64) Part of the tagID. The computer which this tag applies to. References the computerID field of KSComputers, and can be used to join the two tables.
tagName VARCHAR(64) The name of the tag.
tagLabel VARCHAR(64) The label of the tag.

KSContracts

contractID INT Unique ID for this row of KSContracts.
contractServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
contractName VARCHAR(64) The name of the contract, as entered in the purchase details or policy details in KeyConfigure.

KSHotfixes

hotfixID VARCHAR(48) Unique ID for this row of KSHotfixes.
hotfixServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
hotfixStamp VARCHAR(17) The character stamp of the hotfix.
hotfixName VARCHAR(64) The name of the hotfix.
hotfixVersion VARCHAR(32) The version number of the hotfix, as text.
hotfixPlatform INT What platform the hotfix is for. Use KSTermPlatform to convert these constants to string representations.
hotfixPublisher VARCHAR(64) The publisher of the hotfix, if that information is known.
hotfixCreateDate DATETIME The creation date of the hotfix.
hotfixUserName VARCHAR(32) The name of the KeyServer user of the machine on which the hotfix was first seen.
hotfixComputerID VARCHAR(64) The ID of the computer on which this hotfix was first seen. References the computerID field of KSComputers, and can be used to join the two tables.
hotfixNotes VARCHAR(256) Notes for the hotfix. Can't be set in KeyConfigure.
hotfixFlags INT Ignore.

KSInUse

inuseID internal only Unique ID for this entry in the KSInUse table.
inuseProgramID internal only The ID of the managed program which is in use. References the programID field of KSPrograms, and can be used to join the two tables.
inusePolicyID internal only The ID of the policy which is in use. References the policyID field of KSPolicies, and can be used to join the two tables.
inuseProductID internal only The ID of the product which is in use. References the productID field of KSProducts, and can be used to join the two tables.
inuseComputerID internal only The ID of the computer which is using the policy. References the computerID field of KSComputers, and can be used to join the two tables.
inuseSessionID internal only The ID of the user who is logged in and using the policy. References the sessionID field of KSSessions, and can be used to join the two tables.
inuseWhen internal only The time at which this program or policy started being used.
inuseTime internal only The length of time which this program or policy has been in use.
inuseExpiration internal only The date when this policy usage will expire (e.g. for a checked out portable license).
inuseInstances internal only The number of copies of a program which are in use simultaneously.
inusePoolID internal only The pool from which this license is being used. References the poolID field of KSPolicyPools, and can be used to join the two tables.
inuseWaiting internal only True if this policy is not yet in use, but the user is queued for the policy.
inuseReserved internal only True if the policy is reserved for the user, but the user has not yet obtained the policy.
inuseOverdue internal only True if this policy expired, but has not yet been returned.
inuseReclaimed internal only True if an administrator has reclaimed the license but it has not yet been returned.
inuseFlags internal only Ignore.

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.

journalID VARCHAR(40) Unique ID for this Journal entry.
journalServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
journalWhat VARCHAR(5) A four character code indicating what general category this entry falls under.
journalCode VARCHAR(5) A four character code indicating what specific type of entry this is (within the journalWhat category).
journalTopicID VARCHAR(40) The topic that this entry belongs to. References the topicID field of KSJournalTopics, and can be used to join the two tables.
journalWhen DATETIME Contains a date that is important to this journal entry. The date could store for example the date when a Purchase will expire. Or, for multiple entries of the same type that get combined, this could be the earliest date at which any of the entries was written.
journalLatest DATETIME Contains the date when this entry was written. For multiple entries of the same type that have been combined, this is the most recent date at which any of the entries was written.
journalCount INT The number of entries represented by this row in the database. This will only be greater than one when entries get combined.
journalSectionID INT The section which this entry is relevant to. References the sectionID field of KSSections, and can be used to join the two tables. This could be set for example when a Policy that belongs to a Section is edited.
journalSubjectType VARCHAR(5) A four character code indicating what type of object is relevant to this Journal Entry (e.g. it applies to a Policy).
journalSubjectPerms INT Ignore.
journalSubjectID VARCHAR(136) The ID of the object that is relevant to this Journal Entry. This field links to the object in its table, but which table is linked to depends on the journalSubjectType.
journalSubjectName VARCHAR(64) The Name of the object that is relevant to this Journal Entry.
journalSubjectAttributes VARCHAR(64) A text field indicating what attributes are relevant to the Journal Entry - e.g. for a Policy change, it is specifically the Name that was changed.
journalAgent VARCHAR(64) A text field indicating what caused the Journal Entry to be created. For example this could be the name of an Administrator who took some action. Or it could be the name of something that happens automatically, like "PRS".
journalAddress VARCHAR(32) The address that is relevant to the Journal Entry. For example the IP address where an Administrator was logged in when they took some action.
journalComments VARCHAR(256) Comments on the Journal Entry. For an entry that is related to a manual action taken by and Administrator, these can be entered when the action is taken, if the relevant Topic is configured for "Ask".
journalResolveTime DATETIME The date when this Journal Entry was resolved (moved from Alerts to Journal).
journalResolveResult INT Ignore.
journalResolveAdmin VARCHAR(64) The Admin who resolved the Journal Entry.
journalResolveNotes VARCHAR(256) A second notes field for the Journal Entry that can only be entered when the entry is Resolved.
journalResolveFlags INT Flags for this entry.

KSJournalTopics

topicID VARCHAR(40) Unique ID for this Topic.
topicServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
topicWhat VARCHAR(5) A four character code indicating what general category this entry falls under.
topicCode VARCHAR(5) A four character code indicating what specific type of entry this is (within the topicWhat category).
topicSubjectType VARCHAR(5) A four character code indicating what type of object is relevant to this Topic (e.g. it applies to a Policy).
topicTitle VARCHAR(64) The Title of this Topic.
topicDescription VARCHAR(256) The Description of this Topic.
topicResolveData VARCHAR(256) Ignore.
topicCategory VARCHAR(5) A four character code indicating what general category this Topic is in. Similar to topicWhat.
topicFlags INT Flags for this entry.

KSLicensedComputers

licenseeID VARCHAR(160) Unique ID for this association between one Computer and one Policy.
licenseeServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
licenseeComputerID VARCHAR(64) Part of the licenseeID. References the computerID field of KSComputers, and can be used to join the two tables.
licenseePolicyID VARCHAR(32) Part of the licenseeID. References the policyID field of KSPolicies, and can be used to join the two tables.
licenseeAcknowledged INT Ignore.
licenseeLastUsed DATETIME When the computer last used the policy.
licenseeLeaseDate DATETIME When the lease for this policy was issued to the computer.
licenseeLeaseExpiration DATETIME When the lease for this policy will expire on this computer, unless there is further activity.
licenseeEntitlements INT (new in KeyServer 7.4)
How many entitlements for the Policy are consumed by this computer. Generally this value is 1 but could be higher for Core or PVU Policies.

KSLicensedUsers

licenseeID VARCHAR(160) Unique ID for this association between one User and one Policy.
licenseeServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
licenseeUserID VARCHAR(64) Part of the licenseeID. References the userID field of KSUsers, and can be used to join the two tables.
licenseePolicyID VARCHAR(32) Part of the licenseeID. References the policyID field of KSPolicies, and can be used to join the two tables.
licenseeAcknowledged INT Ignore.
licenseeLastUsed DATETIME When the User last used the policy.
licenseeLeaseDate DATETIME When the lease for this policy was issued to the User.
licenseeLeaseExpiration DATETIME When the lease for this policy will expire for this User, unless there is further activity.

KSLocations

locationID VARCHAR(104) Unique ID for this Location.
locationServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
locationProtocol INT Specifies what type of Location range this is. Use KSTermProtocol to convert these constants to string representations.
locationName VARCHAR(64) Name of this Location.
locationRangeBegin INT Specifies the beginning of the Location range.
locationRangeEnd INT Specifies the end of the Location range.
locationAllowed INT Specifies whether users are allowed to log in from this Location.
locationNotes VARCHAR(256) Notes for this Location.
locationFlags INT Ignore.

KSPackages

packageID VARCHAR(64) Unique ID for this package.
packageServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's policy certificate is filled in when tables are exported to an external database.
packageRefNum INT Used internally in KeyServer
packageName VARCHAR(64) Name of the package.
packageVersion VARCHAR(32) Version of the package.
packagePublisher VARCHAR(64) Publisher of the package.
packageProductID VARCHAR(40) The ID of a product relevant to this package, if any. References the productID field of KSProducts, and can be used to join the two tables.
packageType INT Used internally in KeyServer
packageDiscovered DATETIME (new in KeyServer 7.4.0.2)
The date on which this package was discovered.
packageFlags INT Ignore.

KSPolicies

policyID VARCHAR(32) Unique ID for this policy.
policyServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's policy certificate is filled in when tables are exported to an external database.
policyRefNum INT Used internally in KeyServer
policyName VARCHAR(64) Name of the policy.
policyAction INT Policy Action, as configured in KeyConfigure Use KSTermPolicyAction to see which number refers to each policy action.
policyMetric INT Policy metric, as configured in KeyConfigure Use KSTermMetric to see which number refers to each policy metric.
policyMaximum INT Total number of licenses enabled.
policyAllocated INT An estimate of how many licenses for each policy have been Allocated. KeyServer will calculate this value differently depending on the license Metric for the Policy.
policyLeaseTime INT Lease Duration in seconds.
policyStatus INT Indicates whether the policy is in effect. 1 for active policies, 0 if the policy has been disabled using the checkbox in the Policy Details window, 5 if the policy has expired.
policyExpiration DATETIME Policy Expiration Date.
policyOptions INT Field containing various options (flags) for the policy.
policyFolderID INT Indicates which folder the policy belongs to, if any. References the polfolderID field of KSPolicyFolders, and can be used to join the two tables.
policySectionID INT ID of the Section which this policy applies to. References the sectionID field of KSSections, and can be used to join the two tables.
policyContractID INT ID of the contract associated with this policy. References the contractID field of KSContracts, and can be used to join the two tables.
policyCostCenter VARCHAR(64) Cost Center for this policy.
policyDownloadURL VARCHAR(256) A URL where software that can be used with this Policy can be downloaded.
policyMessage VARCHAR(256) The custom message for the policy, as configured in KeyConfigure.
policyNotes VARCHAR(256) Notes for the policy, as configured in KeyConfigure.
policyFlags INT Ignore.

KSPolicyFolders

polfolderID INT Unique ID for this folder.
polfolderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
polfolderName VARCHAR(64) The name of the folder.
polfolderColor INT The color which KeyConfigure uses to display the folder. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
polfolderNotes VARCHAR(256) Notes for the folder. Can't be set in KeyConfigure.
polfolderFlags INT Ignore.

KSPolicyPools

poolID VARCHAR(32) Unique ID for this pool.
poolServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
poolPolicyID VARCHAR(32) ID of the policy which this pool belongs to. References the policyID field of KSPolicies, and can be used to join the two tables.
poolIndex INT Index of this pool within pools for the policy.
poolGroupID VARCHAR(64) ID of the group associated with this pool. References the groupID field of KSComputerGroups, and can be used to join the two tables.
poolLimit INT License limit for this pool.
poolTimeout INT Timeout for this pool.
poolNetwork INT Corresponds to "Can be used on network" checkbox in KeyConfigure.
poolPortable INT Corresponds to "Can be used as Portable Key" checkbox in KeyConfigure.
poolInUse INT Number of licenses in use from this pool.
internal only
poolFlags INT Ignore.

KSPolicyProducts

polprodID VARCHAR(64) Unique ID for this entry in the KSPolicyProducts table.
polprodServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
polprodPolicyID VARCHAR(32) The ID of the policy relevant to this association. References the policyID field of KSPolicies, and can be used to join the two tables.
polprodProductID VARCHAR(40) The ID of the product relevant to this association. References the productID field of KSProducts, and can be used to join the two tables.
polprodPosition INT Order in which to try to use various policies for a given product. Lower numbers are used first.
polprodFlags INT Ignore.

KSPolicyTags

tagID VARCHAR(158) Unique ID for this Policy tag.
tagServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
tagPolicyID VARCHAR(32) Part of the tagID. The Policy which this tag applies to. References the policyID field of KSPolicies, and can be used to join the two tables.
tagName VARCHAR(64) The name of the tag.
tagLabel VARCHAR(64) The label of the tag.

KSPortablesInUse

portID internal only Unique ID for this entry in the KSInUse table.
portPolicyID internal only The ID of the policy which is checked out. References the policyID field of KSPolicies, and can be used to join the two tables.
portUserName internal only The name of the user who checked the portable out.
portComputerID internal only The ID of the computer which has checked out the license. References the computerID field of KSComputers, and can be used to join the two tables.
portWhen internal only The time at which the portable was checked out.
portExpiration internal only The date when this portable will expire.
portPoolID internal only ID of the pool that this portable was checked out against. References the poolID field of KSPolicyPools, and can be used to join the two tables.
portFlags internal only Ignore.

KSProductComponents

componentID VARCHAR(112) Unique ID for this entry in the KSProductComponents table.
componentServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
componentProductID VARCHAR(40) The ID of the product relevant to this association. References the productID field of KSProducts, and can be used to join the two tables.
componentProgramVariant VARCHAR(80) The variant of the program relevant to this association. References the programVariant field of KSPrograms, and can be used to join the two tables.
componentUtility INT Non-zero if the program is just a utility in the product.
componentPosition INT Order in which to try to use various policies for a given product. Lower numbers are used first.
componentFlags INT Ignore.

KSProductFolders

prodfolderID INT Unique ID for this product folder.
prodfolderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
prodfolderName VARCHAR(64) The name of the folder.
prodfolderColor INT The color which KeyConfigure uses to display the folder. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
prodfolderNotes VARCHAR(256) Notes for the folder. Can't be set in KeyConfigure.
prodfolderFlags INT Ignore.

KSProducts

productID VARCHAR(40) Unique ID for this row of KSProducts.
productServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
productName VARCHAR(64) This name of the product.
productVersion VARCHAR(16) The version of the product, as text.
productPlatform INT What platform the product is for. Use KSTermPlatform to convert these constants to string representations.
productReleaseDate DATETIME The date on which this program was released.
productFolderID INT Indicates which folder the product belongs to, if any. References the prodfoldID field of KSProductFolders, and can be used to join the two tables.
productUpgradeID VARCHAR(40) The ID of the product for which this product is an upgrade.
productStatus INT Status of the product. Use KSTermProductStatus to convert these constants to string representations.
productTracked INT Ignore.
productPublisher VARCHAR(64) The publisher of the product.
productCategory VARCHAR(64) The category of the product.
productContact VARCHAR(64) The contact for the product.
productContactAddress VARCHAR(256) The contact address for the product.
productDefinedBy VARCHAR(32) The person or company who defined this product in KeyConfigure.
productNotes VARCHAR(256) Notes for the product.
productFlags INT Ignore.

KSProductTags

tagID VARCHAR(168) Unique ID for this Product tag.
tagServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
tagProductID VARCHAR(40) Part of the tagID. The Product which this tag applies to. References the productID field of KSProducts, and can be used to join the two tables.
tagName VARCHAR(64) The name of the tag.
tagLabel VARCHAR(64) The label of the tag.

KSProgramFolders

folderID INT Unique ID for this program folder.
folderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
folderName VARCHAR(64) The name of the folder.
folderColor INT The color which KeyConfigure uses to display the folder. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
folderNotes VARCHAR(256) Notes for the folder. Can't be set in KeyConfigure.
folderFlags INT Ignore.

KSPrograms

programID VARCHAR(80) Unique ID for this row of KSPrograms. Note this is different for every version. If there is a single entry in the programs window of KeyConfigure, but there are 3 versions in the expandable name in the Actions pane of the program details window, there are then 3 rows in KSPrograms, each with a different programID, but the same programVariant.
programServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
programVariant VARCHAR(80) This is the programID, masked to some degree. A single value of programVariant corresponds to a single item in KeyConfigure's programs window.
programCharStamp VARCHAR(17) The character stamp of the program.
programName VARCHAR(64) The name of the program.
programVariantName VARCHAR(64) The name of the item in KeyConfigure's programs window under which this specific version of the program appears.
programVariantVersion VARCHAR(32) A string representation of the common part of the version, for all versions which KeyConfigure considers a single "program" (i.e. entry in the programs window).
programAIVersion VARCHAR(32) Alternative format of programVariantVersion.
programVersionMask INT A number representation of the version mask, which has been set in KeyConfigure. This is used to compute the programVariant from the programID.
programVersion VARCHAR(32) The version number of the program, as text.
programPlatform INT What platform the program runs on. Use KSTermPlatform to convert these constants to string representations.
programPublisher VARCHAR(64) The publisher of the program, if that information is known.
programPath VARCHAR(256) The path of the first copy of the program which was ever seen.
programFileName VARCHAR(32) The file name of the first copy of the program which was ever seen.
programKeyed INT Indicates whether this program is keyed.
programStatus INT This corresponds to the status - which relates to Product membership. Use KSTermStatus to convert these constants to string representations.
programAcknowledged INT A boolean representing whether or not the program has been acknowledged in KeyConfigure by an administrator. If this value is false, the program shows up as a pink item in the KeyConfigure programs window.
programAudit INT Whether to include this program in software audits.
programFolderID INT Indicates which folder the program belongs to, if any. References the folderID field of KSProgramFolders, and can be used to join the two tables.
programLaunchSeen INT Non-zero if KeyServer has ever seen a launch of this program, from any client.
programDiscMethod INT Indicates how the program was discovered - either by launch (0), audit (1), keying (2), admin (3), or import(4).
programDiscovered DATETIME The date on which this program was discovered, either by an audit, or by a launch of the program.
programCreateDate DATETIME The creation date of the program.
programUserName VARCHAR(32) The name of the KeyServer user of the machine on which the program was first seen.
programComputerID VARCHAR(64) The ID of the computer on which this program was first seen. References the computerID field of KSComputers, and can be used to join the two tables.
programNotes VARCHAR(256) Notes for the program, if they have been typed in the Notes pane of the program details window of KeyConfigure.
programFlags INT Ignore.

KSPurchaseAllocations

allocationID VARCHAR(152) Unique ID for this entry in the KSPurchaseAllocations table.
allocationServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
allocationPurchaseID VARCHAR(80) The ID of the purchase from which allocations are being made. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables.
allocationUpgradeID VARCHAR(80) The ID of the purchase to which allocations are being made. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables.
allocationQuantity INT The number of entitlements that are being allocated.
allocationFlags INT Ignore.

KSPurchaseCodes

codeID VARCHAR(208) Unique ID for this entry in the KSPurchaseCodes table.
codeServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
codePurchaseID VARCHAR(80) The ID of the purchase which this code belongs to. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables.
codeValue VARCHAR(64) The value of this code.

KSPurchaseDocuments

documentID VARCHAR(152) Unique ID for this entry in the KSPurchaseDocuments table.
documentServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
documentPurchaseID VARCHAR(80) The ID of the purchase which this document belongs to. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables.
documentName VARCHAR(32) The name of this document.
documentURL VARCHAR(256) The URL of this document.
documentDateAdded DATETIME Date and time when the document was added to the purchase record.

KSPurchaseFolders

purchfolderID INT Unique ID for this folder.
purchfolderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
purchfolderName VARCHAR(64) The name of the folder.
purchfolderColor INT The color which KeyConfigure uses to display the folder. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
purchfolderNotes VARCHAR(256) Notes for the folder. Can't be set in KeyConfigure.
purchfolderFlags INT Ignore.

KSPurchaseItems

purchaseID VARCHAR(80) Unique ID for this entry in the KSPurchaseItems table.
purchaseServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
purchaseOrderID VARCHAR(32) The ID of the purchase order which this item is part of. References the orderID field of KSPurchaseOrders, and can be used to join the two tables.
purchaseItemNumber INT The item number within the purchase order. This value is unique across the various items within an order.
purchaseName VARCHAR(64) The Name of this purchase item.
purchaseStatus INT A number which is 0 for a purchase with the Dormant checkbox enabled, and non-zero for other purchases.
purchaseType INT A number which encodes the purchase type. Use KSTermPurchaseType to convert these constants to string representations.
purchaseEntitlementType INT A number which encodes the entitlement type. Use KSTermEntitlementType to convert these constants to string representations.
purchaseMetric INT A number which encodes the metric type. Use KSTermMetric to convert these constants to string representations.
purchaseFolderID INT Indicates which folder the purchase belongs to, if any. References the purchfolderID field of KSPurchaseFolders, and can be used to join the two tables.
purchaseQuantity INT Number of packages purchased.
purchaseEntitlementsPerPackage INT Number of entitlements in each package.
purchaseStartDate DATETIME The date on which these entitlements become active.
purchaseEndDate DATETIME The date on which these entitlements expire
purchaseRenewDate DATETIME The date until which upgrade rights are included for this purchase.
purchaseCurrency INT A number which encodes the currency for this purchase. Use KSTermCurrencyType to convert these constants to string representations.
purchaseExtendedCost BIGINT The extended price for this purchase.
purchaseConvertedCost BIGINT The converted price for this purchase.
purchaseUnitMSRP BIGINT The MSRP Unit price for this purchase.
purchaseUnitPrice BIGINT The unit price for this purchase.
purchaseUpgradePrice BIGINT The upgrade price for this purchase.
purchaseProductID VARCHAR(40) The ID of the product which was purchased. References the productID field of KSProducts, and can be used to join the two tables.
purchaseEffectiveProductID VARCHAR(40) The ID of the product which this purchase currently entitles. References the productID field of KSProducts, and can be used to join the two tables.
purchaseInvoice VARCHAR(32) Invoice number for this purchase.
purchaseSectionID INT ID of the Section associated with this purchase. References the sectionID field of KSSections, and can be used to join the two tables.
purchaseContractID INT ID of the contract associated with this purchase. References the contractID field of KSContracts, and can be used to join the two tables.
purchaseGroup VARCHAR(64) Group for this purchase.
purchaseDepartment VARCHAR(64) Department for this purchase.
purchaseCostCenter VARCHAR(64) Cost Center for this purchase.
purchaseResellerSKU VARCHAR(32) Reseller SKU for this purchase.
purchaseManufacturerSKU VARCHAR(32) Manufacturer SKU for this purchase.
purchaseExternalID VARCHAR(32) External ID for this purchase. Can be used to link to other data sources.
purchaseLocation VARCHAR(64) Location for this purchase.
purchaseReference VARCHAR(256) Reference data for this purchase.
purchaseConditions VARCHAR(256) Special conditions for this purchase.
purchaseDescription VARCHAR(256) Description for this purchase.
purchaseNotes VARCHAR(256) Notes for this purchase.
purchaseFlags INT Ignore.

KSPurchaseOrders

orderID VARCHAR(32) Unique ID for this entry in the KSPurchaseOrders table. This is the PO number.
orderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
orderDate DATETIME The date of this PO.
orderFolderID INT Ignore.
orderResellerPO VARCHAR(32) The reseller's PO number for this PO, if applicable.
orderReseller VARCHAR(32) The reseller for this PO, if applicable.
orderRecipient VARCHAR(32) The recipient of this PO, if applicable.
orderNotes VARCHAR(256) Notes for this PO.
orderFlags INT Ignore.

KSPurchaseSupport

supportID VARCHAR(120) Unique ID for this entry in the KSPurchaseSupport table.
supportServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
supportPurchaseID VARCHAR(80) The ID of the purchase which is relevant to this entry. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables.
supportProductID VARCHAR(40) The ID of the product relevant to this association. References the productID field of KSProducts, and can be used to join the two tables.

KSServers

serverID INT Unique ID for this entry in KSServers. When connected directly to a KeyServer, the one entry in this table will have serverID 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
serverType INT 0 for a server, 1 for a shadow.
serverName VARCHAR(32) The AppleTalk/IPX name of the server.
serverComputer VARCHAR(64) The name of the computer on which the KeyServer is running.
serverSerialNumber VARCHAR(32) The displayable serial number.
serverVersion VARCHAR(16) The version of the server.
serverStartTime DATETIME Date and time when the server was started up.
serverGMTOffset INT Offset from GMT in seconds.
serverTimeZone VARCHAR(16) Name of the timezone where the server is.
serverSeats INT Number of clients supported.
serverFullClients INT Current number of clients.
serverActiveSessions INT Current number of Active sessions.
serverFloatingSessions INT Ignore.
serverFloatingRatio INT Ignore.
serverLicensesInUse INT Current number of Licenses in use by KeyServer clients.
internal only
serverLicensesInQueue INT Current number of Licenses Queued for KeyServer clients.
internal only

KSSections

sectionID INT Unique ID for this section.
sectionServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
sectionName VARCHAR(64) The name of the section.
sectionColor INT Ignore.
sectionNotes VARCHAR(256) Notes for the section. Can't be set in KeyConfigure.
sectionFlags INT Ignore.

KSTerm*

Every term table has the following three fields:

termID INT The unique ID for this term entry. This is the field to link to from whatever field needs to be interpreted.
termValue VARCHAR(256) A long string describing the code.
termAbbreviation VARCHAR(32) A short string describing the code.

KSUsage

usageID VARCHAR(104) Unique ID for this entry in KSUsage.
usageWhen DATETIME Date and time when this usage event occured.
usageEnum INT This distinguishes between two or more usage events during the same second.
usageServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
usageServerType INT Whether this event was logged by the KeyServer (value is 0), or by a shadow (value is 1).
usageProgramID VARCHAR(80) If this event involved a program, the ID for the program. References the programID field of KSPrograms, and can be used to join the two tables.
usageProductID VARCHAR(40) Program launch and quit events get put in the Usage table because they belong to a product. Managed program events contain the relevant product ID in this field. References the productID field of KSProducts, and can be used to join the two tables.
usagePolicyID VARCHAR(32) If this event involved a policy, the ID of the policy. References the policyID field of KSPolicies, and can be used to join the two tables.
usageComputerID VARCHAR(64) The ID of the computer which generated this usage event. References the computerID field of KSComputers, and can be used to join the two tables.
usageDivisionID INT ID of the division which was associated with the computer with id usageComputerID at the time of the usage event. References the divisionID field of KSComputerDivisions, and can be used to join the two tables.
usageExpansion VARCHAR(32) Reserved for future use - ignore.
usageUser VARCHAR(32) The name of the user on the computer which generated this usage event.
usageAddress VARCHAR(32) The IP address of the machine at the time of this usage action. Note this could change from event to event for the same computer if you use DHCP.
usageGroup VARCHAR(64) Name of the group which allowed the policy to be used. This may be equal to a value of KSComputerGroups.groupID, but it may not.
usageEvent INT What type of event occured. Use KSTermEvent to convert these constants to string representations. For more about each event type, see the events document.
usageReason INT Extra information on what caused the event. Use KSTermReason to convert these constants to string representations.
usageTime INT How long a certain state was in affect for. Only meaningful for "end" events, like policy return, program quit, user removed from wait queue, etc.
usageOtherTime DATETIME For "end" type events (program quit, policy return, user logoff, server shutdown), this field contains the date and time when the corresponding "start" event occured. Equal to usageWhen - usageTime for these events.
usageGMTOffset INT GMT Offset in seconds of timezone where this usage event occured.
usageCount INT Reserved for future use - ignore.
usageFlags INT Ignore.

KSUserFolders

usrfolderID INT Unique ID for this folder.
usrfolderServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
usrfolderName VARCHAR(64) The name of the folder.
usrfolderColor INT The color which KeyConfigure uses to display the folder. When displayed as hex, the first two digits are the Blue value, the next two are the Green value, the final two are the Red value.
usrfolderNotes VARCHAR(256) Notes for the folder. Can't be set in KeyConfigure.
usrfolderFlags INT Ignore.

KSUsers

userID VARCHAR(64) Unique ID for this user.
userServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
userLastLogin DATETIME Last date and time at which this user logged in to the KeyServer.
userComputerID VARCHAR(64) The computer that the user last logged in on. References the computerID field of KSComputers, and can be used to join the two tables.
userFolderID INT Indicates which folder the user belongs to, if any. References the folderID field of KSUserFolders, and can be used to join the two tables.
userExternalID VARCHAR(32) External ID for this user. Can be used to link to other data sources.
userNotes VARCHAR(256) Notes for the user.
userFlags INT Ignore.

KSUserTags

tagID VARCHAR(264) Unique ID for this User tag.
tagServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database.
tagUserID VARCHAR(64) Part of the tagID. The User which this tag applies to. References the userID field of KSUsers, and can be used to join the two tables.
tagName VARCHAR(64) The name of the tag.
tagLabel VARCHAR(64) The label of the tag.

KSSessions

sessionID internal only Unique ID for this user. (unique forever, only valid as long as user is logged in)
sessionSession internal only Attribute which is used internally by KeyServer. It is essentially useless for reports.
sessionName internal only The name of the user who is logged in in this session.
sessionComputerID internal only The computer that the session is active on. References the computerID field of KSComputers, and can be used to join the two tables.
sessionComputerName internal only The name of the computer that the session is active on.
sessionClientVersion internal only The KeyAccess client version which this session is connected with.
sessionProtocol internal only Specifies the protocol used for this session with KeyServer. Use KSTermProtocol to convert these constants to string representations.
sessionAddress internal only TCP address where this session is active.
sessionLicenseCount internal only The number of licenses in use in this session.
sessionReservationCount internal only The number of licenses reserved for this session.
sessionWaitingID internal only Attribute which is used internally by KeyServer. It is essentially useless for reports.

KSDbex (external only)

dbexTable VARCHAR(32) Name of one of the other tables (e.g. "KSPrograms"). This is part of the key for this table.
dbexServerID INT The ID of the server which created this entry. If you are querying data directly from KeyServer, this will be 0. The "feature.server" attribute from the KeyServer's license certificate is filled in when tables are exported to an external database. This is part of the key for this table.
dbexLast INT Number which represents the last time the table specified by dbexTable was exported by the server of ID dbexServerID. This number is updated after each export.

KSDbexStatus (external only)

dbexstatusServerID INT The ID of the server which created this entry. This is the key for this table.
dbexstatusCurrentTable VARCHAR(32) The name of the table that is currently being exported
dbexstatusProgress INT A number that represents the progress in exporting the current table.
dbexstatusStart VARCHAR(32) The date and time at which the current export started.
dbexstatusComplete VARCHAR(32) The date and time that the export completed.

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 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 >= '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. 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.3 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.4. 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.2 to 7.3.

DROP TABLE KSComputers
DROP TABLE KSLicensedComputers

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

CREATE TABLE KSComputers ( computerID VARCHAR(64) NOT NULL, computerServerID INT NOT NULL,
				computerName VARCHAR(64), computerUserName VARCHAR(32), computerPlatform INT,
				computerProtocol INT, computerAddress VARCHAR(32), computerDomain VARCHAR(32),
				computerDescription VARCHAR(64), computerVirtualHost VARCHAR(64),
				computerGMTOffset INT, computerOSFamily INT, computerOSType INT,
				computerOSVersion INT, computerOSRelease INT, computerOSInstallDate DATETIME,
				computerOS64Bit INT, computerCPUType INT, computerCPUCount INT, computerCPUSockets INT, computerCPUClock INT,
				computerCPU64Bit INT, computerHyperThreading INT, computerMulticore INT,
				computerCapabilities INT, computerPVUs INT, computerRAMSize INT, computerRAMType INT,
				computerRAMArray VARCHAR(520), 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(520), computerDepartment VARCHAR(64),
				computerOEMSerial VARCHAR(64), computerOSSerial VARCHAR(64),
				computerBaseboardSerial VARCHAR(32), computerSystemSerial VARCHAR(32),
				computerUUID VARCHAR(64), computerSCCMUniqueID VARCHAR(64),
				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,
				computerLifeStage INT, computerDivisionID INT, computerSectionID INT,
				computerAssetID VARCHAR(64), computerLocation VARCHAR(64),
				computerOwner VARCHAR(64), computerConfirmed DATETIME,
				computerConfirmedBy VARCHAR(64), computerNotes VARCHAR(256), computerFlags INT,
					PRIMARY KEY ( computerID, computerServerID ) )

CREATE TABLE KSComputerTags ( tagID VARCHAR(264) NOT NULL, tagServerID INT NOT NULL,
				tagComputerID VARCHAR(64), tagName VARCHAR(64), tagLabel VARCHAR(64),
					PRIMARY KEY (tagID, tagServerID ) )

CREATE TABLE KSJournal ( journalID VARCHAR(40) NOT NULL, journalServerID INT NOT NULL,
				journalWhat VARCHAR(5), journalCode VARCHAR(5), journalTopicID VARCHAR(40),
				journalWhen DATETIME, journalLatest DATETIME, journalCount INT,
				journalSectionID INT, journalSubjectType VARCHAR(5),
				journalSubjectPerms INT, journalSubjectID VARCHAR(136),
				journalSubjectName VARCHAR(64), journalSubjectAttributes VARCHAR(64),
				journalAgent VARCHAR(64), journalAddress VARCHAR(32),
				journalComments VARCHAR(256), journalResolveTime DATETIME,
				journalResolveResult INT, journalResolveAdmin VARCHAR(64),
				journalResolveNotes VARCHAR(256), journalResolveFlags INT,
					PRIMARY KEY (journalID, journalServerID ) )

CREATE TABLE KSJournalHistory ( journalID VARCHAR(40) NOT NULL, journalServerID INT NOT NULL,
				journalWhat VARCHAR(5), journalCode VARCHAR(5), journalTopicID VARCHAR(40),
				journalWhen DATETIME, journalLatest DATETIME, journalCount INT,
				journalSectionID INT, journalSubjectType VARCHAR(5),
				journalSubjectPerms INT, journalSubjectID VARCHAR(136),
				journalSubjectName VARCHAR(64), journalSubjectAttributes VARCHAR(64),
				journalAgent VARCHAR(64), journalAddress VARCHAR(32),
				journalComments VARCHAR(256), journalResolveTime DATETIME,
				journalResolveResult INT, journalResolveAdmin VARCHAR(64),
				journalResolveNotes VARCHAR(256), journalResolveFlags INT,
					PRIMARY KEY (journalID, journalServerID ) )

CREATE TABLE KSJournalTopics ( topicID VARCHAR(40) NOT NULL, topicServerID INT NOT NULL,
				topicWhat VARCHAR(5), topicCode VARCHAR(5), topicSubjectType VARCHAR(5),
				topicTitle VARCHAR(64), topicDescription VARCHAR(256),
				topicResolveData VARCHAR(256), topicCategory VARCHAR(5), topicFlags INT,
					PRIMARY KEY (topicID, topicServerID ) )

CREATE TABLE KSLicensedComputers ( licenseeID VARCHAR(160) NOT NULL, licenseeServerID INT NOT NULL,
				licenseeComputerID VARCHAR(64), licenseePolicyID VARCHAR(32),
				licenseeAcknowledged INT, licenseeLastUsed DATETIME,
				licenseeLeaseDate DATETIME, licenseeLeaseExpiration DATETIME,
				licenseeEntitlements INT,
					PRIMARY KEY ( licenseeID, licenseeServerID ) )

CREATE TABLE KSPolicyTags ( tagID VARCHAR(158) NOT NULL, tagServerID INT NOT NULL,
				tagPolicyID VARCHAR(32), tagName VARCHAR(64), tagLabel VARCHAR(64),
					PRIMARY KEY (tagID, tagServerID ) )

CREATE TABLE KSProductTags ( tagID VARCHAR(168) NOT NULL, tagServerID INT NOT NULL,
				tagProductID VARCHAR(40), tagName VARCHAR(64), tagLabel VARCHAR(64),
					PRIMARY KEY (tagID, tagServerID ) )

CREATE TABLE KSUserTags ( tagID VARCHAR(264) NOT NULL, tagServerID INT NOT NULL,
				tagUserID VARCHAR(64), tagName VARCHAR(64), tagLabel VARCHAR(64),
					PRIMARY KEY (tagID, tagServerID ) )

If possible, it might be easier to let KeyServer create the new tables, then in the front end for your database, manually change existing tables as needed.