Tables Description
This document describes KeyServer's tables, as well as the fields of each table, and how to use them.
These tables can be accessed via ODBC using our driver, ksODBC. ksODBC is installed by default by the ksp-admin installer. You can install just ksODBC without KeyConfigure if that is all you need on a particular computer. You can also configure KeyServer to export its internal data into tables that use the same structure — see
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
- Audit data which tracks what programs were discovered on each computer.
KSAuditPackages
- Audit data which tracks "Packages" installed on each computer. This is meant primarily for KeyServer's internal use in determining what Products are installed.
KSAuditProducts
- Audit data which tracks what products are on each computer, based on an examination of the programs discovered on each computer (KSAudits). This table is generated nightly according to the settings in KeyConfigure's General Settings dialog.
KSComputerDevices
- KSComputerDevices is a cross table to associate computers and devices.
- New in KeyServer 7.8.
KSComputerDivisions
- Computer Divisions which have been configured in KeyConfigure.
- These are the divisions which appear on the left side of the computers window.
- There is no entry for the Uncategorized division.
KSComputerGroupMembers
- 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.
KSComputerGroups
- Computer Groups which have been configured in KeyConfigure.
- This table does not contain entries for groups which are defined by
external authorization methods.
KSComputers
- Computers which have connected to the KeyServer at some point.
KSComputerTags
- Tags which have been applied to Computers.
KSContracts
- Contracts referenced by purchases and policies.
KSDevices
- This table holds displays, storage, printers, and other devices. Some of these can be populated directly by the KeyAccess client (displays, storage), while others can be imported using various integrations, or created manually.
- New in KeyServer 7.8 - replaces the KSPrinters table and now includes more types of devices and more properties.
KSHotfixes
- Hotfixes (Windows only) which have been seen on client computers.
KSInUse
- 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.
KSJournal
- Admin Journal entries which are currently displayed in the Admin Alerts Window (have not yet been acknowledged and move to the Admin Journal WIndow).
KSJournalHistory
- Admin Journal entries which are currently displayed in the Admin Journal Window.
KSJournalTopics
- 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
- 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
- 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.
KSLocations
- Locations. This table has entries corresponding to items in the Locations
window in KeyConfigure.
KSPackages
- Packages. This table has entries corresponding to Packages, which help support Product Recognition.
KSPolicies
- Policies. Each policy may apply to use of one or more products.
KSPolicyFolders
- 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.
KSPolicyPools
- 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.
KSPolicyProducts
- Associates policies with the products they manage.
KSPolicyTags
- Tags which have been applied to Policies.
KSPortablesInUse
- Shows what portable licenses are currently checked out.
- This table is not exported.
KSProductComponents
- Associates products with programs.
KSProductFamilies
- Associates product families with individual product editions.
KSProductFolders
- 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.
KSProductPackages
- Associates products with packages.
KSProducts
- Product, as listed in the main Products window of KeyConfigure.
KSProductTags
- Tags which have been applied to Products.
KSProgramFolders
- 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.
KSPrograms
KSPurchaseAllocations
- Allocations of entitlements from one purchase to another - to support dependent purchases.
KSPurchaseCodes
- Activation codes or Serial Numbers associated with each purchase.
KSPurchaseCodeOwners
- Owner information for purchase codes
KSPurchaseDocuments
- Documents or links associated with each purchase.
KSPurchaseFolders
- 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.
KSPurchaseItems
- Each purchase item corresponds to a line in the Purchases window of KeyConfigure.
- A purchase item is part of a purchase order.
KSPurchaseOrders
- A purchase order contains the common values of a set of related purchase items.
- Purchase orders aggregate related individual purchase items.
KSPurchaseSupport
- For a dependent purchase, which requires support, this table records which products can be used to support the purchase.
KSPurchaseTags
- Tags which have been applied to Purchases.
KSServers
- 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.
KSSections
- Section data which aggregates Computer Divisions into Sections, and allow distinct KeyConfigure Administrators to manage different sets of Computers.
KSTermAllowed
- Provides long and short string representations of the
KSComputers.computerAllowed constants.
KSTermCPUType
- Provides long and short string representations of the
KSComputers.computerCPUType constants.
KSTermCurrencyType
- Provides long and short string representations of the
KSPurchaseItems.purchaseCurrency constants.
KSTermDeviceStatus
- Provides long and short string representations of the
KSDevices.deviceStatus constants.
KSTermDeviceCategory
- Provides long and short string representations of the
KSDevices.deviceCategory constants.
KSTermDeviceType
- Provides long and short string representations of the
KSDevices.deviceType constants.
KSTermEntitlementType
- Provides long and short string representations of the
KSPurchaseItems.entitlementType constants.
KSTermEvent
- Provides long and short string representations of the
KSUsage.usageEvent constants.
KSTermLifeStage
- Provides long and short string representations of the
KSComputers.computerLifeStage constants.
KSTermMetric
- Provides long and short string representations of the
KSPolicies.policyMetric constants.
KSTermOSFamily
- Provides long and short string representations of the
KSComputers.computerOSFamily constants.
KSTermOSType
- Provides long and short string representations of the
KSComputers.computerOSType constants.
KSTermPlatform
- Provides long and short string representations of the
KSComputers.computerPlatform constants.
KSTermPolicyAction
- Provides long and short string representations of the
KSPolicies.policyAction constants.
KSTermProductStatus
- Provides long and short string representations of the
KSProducts.productStatus constants.
KSTermProtocol
- Provides long and short string representations of the
KSComputers.computerProtocol constants.
KSTermPurchaseType
- Provides long and short string representations of the
KSPurchaseItems.purchaseType constants.
KSTermRAMType
- Provides long and short string representations of the
KSComputers.computerRAMType constants.
KSTermReason
- Provides long and short string representations of the
KSUsage.usageReason constants.
KSTermStatus
- Provides long and short string representations of the
KSPrograms.programStatus constants.
KSUsage
- Usage records which track both program and policy usage, as well
as client logins, logouts, audits, etc.
KSUserFolders
- 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.
KSUsers
- This table holds information about KeyServer Users (The same ones that
are shown in the Users Window of KeyConfigure).
KSUserTags
- Tags which have been applied to Users.
KSSessions
- 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.
KSDbex
- 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.
KSDbexStatus
- This table does not exist internally in KeyServer.
- It is only used in exported databases.
- It keeps track of the progress made in an ongoing export.
Columns
KeyServer's internal databases (accessible through ksODBC) contain the same columns as exported data. However, the data types might be slightly different. The types listed below are correct for exported data. If you need to create the tables by hand before exporting data from KeyServer, you should use the types contained in the tables below.
KSAudits
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.
|
KSComputerDevices
compdevID
|
VARCHAR(168) |
Unique ID for this record.
|
compdevServerID
|
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. |
compdevDeviceID
|
VARCHAR(40) |
Part of the compdevID.
References the deviceID field of KSDevices,
and can be used to join the two tables.
|
compdevComputerID
|
VARCHAR(64) |
Part of the compdevID.
References the computerID field of KSComputers,
and can be used to join the two tables.
|
compdevLastSeen
|
DATETIME |
The last time this device was seen attached to this computer. |
compdevFlags
|
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(128) |
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.
|
computerLastStartup
|
DATETIME |
Date when the computer was last started up.
|
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 |
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.
|
computerCPUDescription
|
VARCHAR(64) |
The CPU description as returned by hardware - not normalized.
|
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 |
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.
|
computerDisplayCount
|
INT |
Number of displays connected to this computer.
|
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(64) |
Manufacturer's serial number.
|
computerOSSerial
|
VARCHAR(64) |
Serial number of the OS, if it is available.
|
computerBaseboardSerial
|
VARCHAR(64) |
The serial number of the Win32_BaseBoard object. This serial number may also be used as the computerOEMSerial.
|
computerSystemSerial
|
VARCHAR(64) |
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(64) |
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.
|
computerLastImport
|
DATETIME |
Date and time that this record was last imported from an external source.
(New in KeyServer 7.8)
|
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.
|
computerStatus
|
INT |
Current status of the computer:
4 - on with session, 3 - idle session, 2 - on available, 1 - agent too old to know status, 0 - off
|
computerLatitude
|
INT |
Latitude of this computer represented as millionths. e.g. 43.7022° would be stored as 43702200. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
(New in KeyServer 7.8)
|
computerLongitude
|
INT |
Longitude of this computer represented as millionths. e.g. -72.2896° would be stored as -72289600. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
(New in KeyServer 7.8)
|
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.
|
computerStationID
|
VARCHAR(32) |
Station ID, can be used when replacing hardware.
|
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.
|
computerRegion
|
VARCHAR(64) |
Region of this computer, which can be used as you want. For example this might store City, State. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
(New in KeyServer 7.8)
|
computerBuilding
|
VARCHAR(64) |
Building where this computer is located. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
(New in KeyServer 7.8)
|
computerRoom
|
VARCHAR(64) |
Room where this computer is located. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
(New in KeyServer 7.8)
|
computerOwner
|
VARCHAR(64) |
Owner, as defined in KeyConfigure in Asset pane of Computer
Details window.
|
computerOnLoanTo
|
VARCHAR(64) |
Who the computer is currently loaned to. For most records this filed will be blank.
(New in KeyServer 7.8)
|
computerOnLoanUntil
|
DATETIME |
When the loan for this computer is over - i.e. when it is due back.
(New in KeyServer 7.8)
|
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.
|
computerPurchaseID
|
VARCHAR(80) |
The ID of the purchase linked to this computer.
References the purchaseID field of KSPurchaseItems,
and can be used to join the two tables.
|
computerPurchaseDate
|
DATETIME |
Date when computer was purchased. This date can be populated directly in the computer
record without linking to a purchase.
|
computerLeaseEndDate
|
DATETIME |
Date when the lease of a computer ends. This date can be populated directly in the
computer record without linking to a purchase.
|
computerWarrantyDate
|
DATETIME |
Date when the warranty for a computer expires.
|
computerReplaceDate
|
DATETIME |
Date when this computer is expected to be replaced.
(New in KeyServer 7.8)
|
computerSalvageValue
|
BIGINT |
The salvage value for this computer.
|
computerServiceURL
|
VARCHAR(256) |
A URL that references this same computer in an external service.
(New in KeyServer 7.8)
|
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 defined per Custom Columns. Once defined, the columns will be available via ksODBC and will be exported. For example, if you name the first field "Segment", then the computers table will show a column named "computerSegment". Note when using Filters you use usr0-9 as the field names.
|
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.
|
tagValue
|
VARCHAR(64) |
The value 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.
|
KSDevices
deviceID |
VARCHAR(40) |
Unique ID for this device. |
deviceServerID |
INT |
The ID of the server that created this entry. |
deviceName |
VARCHAR(64) |
Name of the device. If manually set in Device Detail it will not be replaced by device query. |
deviceCategory |
INT |
Main category of the device (e.g. Display, Storage, Printer), stored as a number. Use KSTermDeviceCategory to convert these constants to string representations. |
deviceType |
INT |
The type of device within its main category (e.g. Hard Drive vs Solid State Drive), stored as a number. Use KSTermDeviceType to convert these constants to string representations. |
deviceStatus |
INT |
Status of the device. For example, using PaperCut to query printers, this is routinely polled from the server, and would indicate OK vs paper jam etc. |
deviceLastCheck |
DATETIME |
The last time the status of the device was updated via an integration or import. |
deviceSpec0 |
INT |
One of the main specs that is relevant to this device. |
deviceSpec1 |
INT |
One of the main specs that is relevant to this device. |
deviceSpec2 |
INT |
One of the main specs that is relevant to this device. |
deviceSpec3 |
INT |
One of the main specs that is relevant to this device. |
deviceState0 |
INT |
One of the main current states that is relevant to this device. |
deviceState1 |
INT |
One of the main current states that is relevant to this device. |
deviceState2 |
INT |
One of the main current states that is relevant to this device. |
deviceState3 |
INT |
One of the main current states that is relevant to this device. |
deviceInkLevel |
INT |
Ink level - specific to printers. |
deviceInkLevels |
VARCHAR(20) |
Binary representation of all ink levels - specific to printers. |
deviceLifeStage |
INT |
Which stage of life cycle this device is in, as set in KeyConfigure. Use KSTermLifeStage to see which number refers to each policy metric. |
deviceLatitude
|
INT |
Latitude of this device represented as millionths. e.g. 43.7022° would be stored as 43702200. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
|
deviceLongitude
|
INT |
Longitude of this device represented as millionths. e.g. -72.2896° would be stored as -72289600. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
|
deviceComputerID |
VARCHAR(64) |
ID of the computer associated with this device. References computerID field of KSComputers, and can be used to join the two tables. |
deviceDivisionID |
INT |
ID of the division associated with this device. References the divisionID field of KSComputerDivisions, and can be used to join the two tables. |
deviceSectionID |
INT |
ID of the section associated with this device. References the sectionID field of KSSections, and can be used to join the two tables. |
deviceLastSeen |
DATE |
The last time that this device was reported directly by a KeyAccess client process. |
deviceLastImport |
DATE |
The last time that this device was imported (usually by a script/integration). |
deviceAddress |
VARCHAR(32) |
IP address of the device. |
deviceServiceType |
INT |
Service type for this device, as set in KeyConfigure in the Printer Details. This indicates where the device data came from - e.g. PaperCut. |
deviceServiceID |
VARCHAR(64) |
External service identifier for this device. |
deviceServiceURL |
VARCHAR(128) |
External service URL for this device, e.g. a server that has provided data for multiple devices. |
deviceExternalURL |
VARCHAR(256) |
URL relevant to this device, e.g. for this device's information in another system. |
deviceDescription |
VARCHAR(256) |
Description of the device, as set in KeyConfigure in the Device Details. |
deviceDepartment |
VARCHAR(64) |
Department of the device, as defined in KeyConfigure in Asset pane of Device Details window. |
deviceSerialNumber |
VARCHAR(64) |
Serial number of the device. |
deviceManufacturer |
VARCHAR(64) |
Manufacturer of the device, if it is available or has been set manually. |
deviceModel |
VARCHAR(64) |
Model name of the device, if it is available or has been set manually. |
deviceUUID |
VARCHAR(64) |
UUID of the device. |
deviceFirmware |
VARCHAR(64) |
Firmware version that is installed on the device. |
deviceAssetID |
VARCHAR(64) |
Asset ID, as defined in KeyConfigure in Asset pane of Device Details window. |
deviceLocation |
VARCHAR(64) |
Location of the device, as defined in KeyConfigure in Asset pane of Device Details window. |
deviceRegion
|
VARCHAR(64) |
Region of this device, which can be used as you want. For example this might store City, State. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
|
deviceBuilding
|
VARCHAR(64) |
Building where this device is located. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
|
deviceRoom
|
VARCHAR(64) |
Room where this device is located. Currently not populated directly by the KeyServer Platform, but available for imports or integrations.
|
deviceOwner |
VARCHAR(64) |
Owner, as defined in KeyConfigure in Asset pane of Device Details window. |
deviceOnLoanTo
|
VARCHAR(64) |
Who the device is currently loaned to. For most records this filed will be blank.
|
deviceOnLoanUntil
|
DATETIME |
When the loan for this device is over - i.e. when it is due back.
|
deviceConfirmed |
DATETIME |
Date when device was confirmed, as stamped in KeyConfigure in Asset pane of Device Details window when using the Confirm button. |
deviceConfirmedBy |
VARCHAR(32) |
Who confirmed the device, as stamped in KeyConfigure in Asset pane of Device Details window when using the Confirm button. |
devicePurchaseID |
VARCHAR(80) |
The ID of the purchase linked to this device. References the purchaseID field of KSPurchaseItems, and can be used to join the two tables. |
devicePurchaseDate |
DATETIME |
Date when device was purchased. This date can be populated directly in the device record without linking to a purchase. |
deviceLeaseEndDate |
DATETIME |
Date when device lease ends. This date can be populated directly in the device record without linking to a purchase. |
deviceWarrantyDate |
DATETIME |
Date when device warranty expires. This date can be populated directly in the device record without linking to a purchase. |
deviceReplaceDate
|
DATETIME |
Date when this device is expected to be replaced.
|
deviceSalvageValue |
BIGINT |
Salvage value for this device. |
deviceNotes |
VARCHAR(256) |
Notes for the device, as defined in KeyConfigure in Notes pane of Device Details window. |
deviceFlags |
INT |
Ignore. |
deviceCustom1
|
VARCHAR(64) |
There are 10 custom fields in the Devices table. In order to use these fields, they must first be defined per Custom Columns. Once defined, the columns will be available via ksODBC and will be exported. For example, if you name the first field "Segment", then the computers table will show a column named "deviceSegment". Note when using Filters you use usr0-9 as the field names.
|
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 |
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
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
|
packageDiscovered
|
DATETIME |
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.
|
tagValue
|
VARCHAR(64) |
The value 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.
|
KSProductFamilies
familyID
|
VARCHAR(72) |
Unique ID for this entry in the KSProductFamilies table.
|
familyServerID
|
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. |
familyProductID
|
VARCHAR(40) |
The ID of the family product relevant to this association.
References the productID field of KSProducts,
and can be used to join the two tables.
|
familyEditionID
|
VARCHAR(40) |
The ID of the edition product relevant to this association.
References the productID field of KSProducts,
and can be used to join the two tables.
|
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.
|
KSProductPackages
prodpkgID
|
VARCHAR(168) |
Unique ID for this entry in the KSProductPackages table.
|
prodpkgServerID
|
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. |
prodpkgProductID
|
VARCHAR(40) |
The ID of the family product relevant to this association.
References the productID field of KSProducts,
and can be used to join the two tables.
|
prodpkgPackageID
|
VARCHAR(64) |
The ID of the package relevant to this association.
References the packageID field of KSPackages,
and can be used to join the two tables.
|
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 product was released.
|
productSupportDate |
DATETIME |
The date on which support for this product ends. |
productEndLifeDate |
DATETIME |
The end of life date for this product. |
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. Pre-populated by PRS
|
productCategory
|
VARCHAR(64) |
The category of the product. Pre-populated by PRS
|
productContact
|
VARCHAR(64) |
The contact for the product.
|
productContactAddress
|
VARCHAR(256) |
The URL for the product website. Pre-populated by PRS
|
productDefinedBy
|
VARCHAR(32) |
The person or company who defined this product in KeyConfigure.
|
productExternalID
|
VARCHAR(64) |
Custom field, open use
|
productExternalURL
|
VARCHAR(256) |
Custom field, open use
|
productDescription
|
VARCHAR(256) |
Description of product provided by PRS
|
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.
|
tagValue
|
VARCHAR(64) |
The value 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 Name given to this code entry in the KeyConfigure UI
|
codeAssignCount
|
INT |
Number of copies assigned for this entry. Totaled automatically based on Assignments.
|
codeAssignLimit
|
INT |
Number of copies available for this entry, set by Maximum field in UI.
|
codeProductID
|
VARCHAR(40) |
References the productID field of KSProducts, and can be used to join the two tables. See also audprodAltProductID below.
|
codeNotes
|
VARCHAR(256) |
Open notes field
|
codeFlags
|
INT |
Ignore this field, internal flag use. Stores platform info.
|
KSPurchaseCodeOwners
ownerID
|
VARCHAR(308) |
Unique ID for this entry in the table
|
ownerServerID
|
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.
|
ownerCodeID
|
VARCHAR(240) |
An internal index field.
|
ownerPurchaseID
|
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.
|
ownerCode
|
VARCHAR(80) |
Link to the PurchaseCode via the codeValue field
|
ownerName
|
VARCHAR(32) |
Text or link for the Name field (see ownerKind)
|
ownerProductID
|
VARCHAR(40) |
Product for which this code is relevant. References the productID field of KSproducts, and can be used to join the two tables.
|
ownerKind
|
INT |
0 if a link to a Computer (drag and drop from Computers Window), 1 if anything else
|
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(128) |
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.
|
purchaseEmail
|
VARCHAR(64) |
Email associated with this purchase, which will be used to send renewal reminders.
(New in KeyServer 7.8)
|
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.
|
purchaseCustom1
|
VARCHAR(64) |
There are 10 custom fields in the Purchases table. In order to use these fields, they must first be defined per Custom Columns. Once defined, the columns will be available via ksODBC and will be exported. For example, if you name the first field "Segment", then the computers table will show a column named "purchaseSegment". Note when using Filters you use usr0-9 as the field names.
|
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.
|
KSPurchaseTags
tagID
|
VARCHAR(208) |
Unique ID for this Purchase 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. |
tagPurchaseID
|
VARCHAR(80) |
Part of the tagID.
The Purchase which this tag applies to.
References the purchaseID field of KSPurchaseItems,
and can be used to join the two tables.
|
tagName
|
VARCHAR(64) |
The name of the tag.
|
tagValue
|
VARCHAR(64) |
The value of the tag.
|
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.
|
usageForegroundTime
|
INT |
The amount of time the program(s) ran as the frontmost window. For Product and Policy events, this is the sum of the foreground time for all associated programs.
|
usageGMTOffset
|
INT |
GMT Offset in seconds of timezone where this usage event occured.
|
usageCount
|
INT |
Reserved for future use - ignore.
|
usageFamilyProductID
|
VARCHAR(40) |
ID of Family Product for usage event.
|
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.
|
userDepartment
|
VARCHAR(64) |
The department of this user.
(New in KeyServer 7.8)
|
userDomain
|
VARCHAR(32) |
The domain that this user is part of.
(New in KeyServer 7.8)
|
userPhone
|
VARCHAR(32) |
Phone number of the user.
(New in KeyServer 7.8)
|
userEmail
|
VARCHAR(64) |
Email address of the user.
(New in KeyServer 7.8)
|
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.
|
tagValue
|
VARCHAR(64) |
The value 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 at a query to show specific computers within a certain division:
SELECT computerName,computerUserSession,computerUserName
FROM
KSComputers
WHERE
computerDivisionID=1234234
ORDER BY
computerName
1234234 gets replaced with the appropriate divisionID, which was selected in the summary query above.
The first column is the name of each computer within the division (this may not quite match what
KeyConfigure displays in the Computers window - see the notes about the KSComputer fields for more).
The second column is NULL if no one is currently logged in to the computer, and non NULL if someone
is logged in to the computer. The third column is the name of the last user who logged in to the
computer (which is the currently logged in user if there is someone logged in).
Next, here is an example that is somewhat like what the Usage (PRGM x user) report might do:
SELECT MAX(programVariantName),MAX(programVariantVersion),MAX(usageUser),
COUNT(*),SUM(usageTime),MAX(usageWhen)
FROM
{oj KSUsage INNER JOIN KSPrograms ON KSUsage.usageProgramID=KSPrograms.programID
AND KSUsage.usageServerID=KSPrograms.programServerID }
WHERE
(usageEvent=4 OR usageEvent=9 OR usageEvent=11 OR usageEvent=13) AND programStatus > 1
AND usageWhen >= {ts '2017-12-01 00:00:00'} AND usageOtherTime < {ts '2018-01-01 00:00:00'}
GROUP BY
programVariantName,programVariant,usageUser
First of all, notice the join between KSUsage and KSPrograms. Technically, the usageServerID to
programServerID should be included as a condition, but it is really only important if you have
multiple KeyServers exporting to the same database. This is very similar to how you would do joins
between KSUsage and KSComputers or KSPolicies. Next, look at the WHERE clause. It
selects all the various types of program quit events (you can find the event codes by looking at the
KSTermEvent table). Additionally, it only selects events for programs which are currently a
component of a product (look at KSTermStatus). Finally, it selects events for which at least part
of the usage was in December of 2007 - the quit was after December 1 and the corresponding
launch was before January 1. With these various events, it groups by programVariantName and
programVariant (since multiple programs may have the same Variant name), then by user. Finally,
the fields it displays show summarization of usage. The first three fields identify which program
and user this line is for. Next, the total number of quits, duration of usage, and most recent quit
are selected. The actual KeyConfigure report is quite a bit more complicated than this. Most
importantly, it selects every event and then processes and summarizes them within the report module,
instead of having the SQL query doing the grouping and summarization. This allows it only count
usage time that overlaps the selected time range. For example, if a program were launched on November
25 and left running until Dec 1, the SQL query above would count a whole week of usage, but the
internal report would calculate the overlap and only count 1 day of usage. The internal report is
also able to show a line summarizing usage for each program as a whole, as well as the details of
each program/user combination. It also has a few more fields than what is shown above, and formats
usage durations as hours:minutes instead of a raw number of seconds.
Note also that the syntax for the join and for date stamps has to be
adjusted depending on what ODBC driver is being used. The syntax shown above is the correct syntax for ksODBC.
Updating Exported databases
If you have a 7.7 or earlier KeyServer configured to export data to an external database, you should update the table structure of your external tables to match the new structure of internal tables in KeyServer 7.8. There is no requirement that you do this - if you keep the same structure as before, KeyServer will continue to add data to any columns that exist in the external database, but data for the new columns will be dropped. KeyServer will attempt to create the tables that are new, which will work as long as the account used for export has CREATE TABLE privileges.
In order to manually update the external database, here are the commands we recommend using. As long as the account used for export has CREATE TABLE privileges, you can leave out any of the CREATE TABLE commands that appear below. Note that if you are upgrading from an earlier version you should also look at the commands for upgrading from that version. See also: changes from 7.6 to 7.7.
DROP TABLE KSPrinters
ALTER TABLE KSComputers ADD ( computerLastImport DATETIME,
computerLatitude INT, computerLongitude INT,
computerRegion VARCHAR(64), computerBuilding VARCHAR(64),
computerRoom VARCHAR(64), computerOnLoanTo VARCHAR(64),
computerOnLoanUntil DATETIME, computerReplaceDate DATETIME,
computerServiceURL VARCHAR(256) )
ALTER TABLE KSPurchaseItems ALTER COLUMN purchaseName VARCHAR(128)
ALTER TABLE KSPurchaseItems ADD ( purchaseEmail VARCHAR(64) )
ALTER TABLE KSPurchaseCodeOwners ADD ( ownerCodeID VARCHAR(240), ownerProductID VARCHAR(40) )
ALTER TABLE KSUsers ADD ( userDepartment VARCHAR(64), userDomain VARCHAR(32),
userPhone VARCHAR(32), userEmail VARCHAR(64) )
CREATE TABLE KSDevices ( deviceID VARCHAR(40), deviceServerID INT, deviceName VARCHAR(64),
deviceCategory INT, deviceType INT, deviceStatus INT,
deviceLastCheck DATETIME, deviceSpec0 INT, deviceSpec1 INT,
deviceSpec2 INT, deviceSpec3 INT, deviceState0 INT,
deviceState1 INT, deviceState2 INT, deviceState3 INT,
deviceInkLevel INT, deviceInkLevels VARCHAR(20), deviceLifeStage INT,
deviceLatitude INT, deviceLongitude INT, deviceComputerID VARCHAR(64),
deviceDivisionID INT, deviceSectionID INT, deviceLastSeen DATE,
deviceLastImport DATE, deviceAddress VARCHAR(32), deviceServiceType INT,
deviceServiceID VARCHAR(64), deviceServiceURL VARCHAR(128),
deviceExternalURL VARCHAR(256), deviceDescription VARCHAR(256),
deviceDepartment VARCHAR(256), deviceSerialNumber VARCHAR(64),
deviceManufacturer VARCHAR(64), deviceModel VARCHAR(64),
deviceUUID VARCHAR(64), deviceFirmware VARCHAR(64),
deviceAssetID VARCHAR(64), deviceLocation VARCHAR(64),
deviceRegion VARCHAR(64), deviceBuilding VARCHAR(64),
deviceRoom VARCHAR(64), deviceOwner VARCHAR(64),
deviceOnLoanTo VARCHAR(64), deviceOnLoanUntil DATETIME,
deviceConfirmed DATETIME, deviceConfirmedBy VARCHAR(32),
devicePurchaseID VARCHAR(80), devicePurchaseDate DATETIME,
deviceLeaseEndDate DATETIME, deviceWarrantyDate DATETIME,
deviceReplaceDate DATETIME, deviceSalvageValue BIGINT,
deviceNotes VARCHAR(256), deviceFlags INT,
PRIMARY KEY ( deviceID, deviceServerID ) )
CREATE TABLE KSComputerDevices ( compdevID VARCHAR(168), compdevServerID INT,
compdevDeviceID VARCHAR(40), compdevComputerID VARCHAR(64),
compdevLastSeen DATETIME, compdevFlags INT,
PRIMARY KEY ( compdevID, compdevServerID ) )