Data in KeyServer's tables can be accessed via SQL queries, either directly through ksODBC, or to exported data via the appropriate ODBC driver. The samples below demonstrate some common queries.
This query will retrieve a list of all computers that have a license to log into KeyServer — computers in the Dedicated or Leased categories. The results include the computer name, the computer's division (retrieved via a Join), the OS family (via another Join), and a few other fields.
SELECT computerName, divisionName, KSTermOSFamily.termValue, computerOSInstallDate, computerOSSerial, computerBIOSSerial, computerLastAudit FROM {oj (KSComputers LEFT OUTER JOIN KSComputerDivisions ON KSComputers.computerDivisionID=KSComputerDivisions.divisionID AND KSComputers.computerServerID=KSComputerDivisions.divisionServerID ) LEFT OUTER JOIN KSTermOSFamily ON KSComputers.computerOSFamily=KSTermOSFamily.termID } WHERE (computerAllowed>1)
This query will retrieve a list of products and their component programs. The results include the product name and version, and the program name and version. Since there can be multiple versions of a program within a single variant the query is grouped so that only on entry appears for each variant. The query is also ordered so all components of a product are listed together.
SELECT productName, productVersion, MAX(programVariantName), MAX(programVariantVersion) FROM {oj (KSProductComponents INNER JOIN KSProducts ON KSProductComponents.componentProductID=KSProducts.productID AND KSProductComponents.componentServerID=KSProducts.productServerID ) INNER JOIN KSPrograms ON KSProductComponents.componentProgramVariant=KSPrograms.programVariant AND KSProductComponents.componentServerID=KSPrograms.programServerID } GROUP BY productName, productVersion, programVariant ORDER BY productName, productVersion
This query will retrieve a list of products installed on each computer. The results include the computer and product names along with some information about the computer and the product. The query is also ordered by computer name so all products on a computer are listed together. This query also includes a condition in the WHERE clause that retrieves only those rows that correspond to an installed product, since the KSAuditProducts table includes some other information used for reporting.
NOTE: This query requires that Audit Products table generation is enabled (in Config Menu, General Settings).
SELECT computerName, productName, productVersion, computerLastAudit, audprodFirstSeen, audprodLastUsed, computerBIOSSerial FROM {oj (KSAuditProducts INNER JOIN KSProducts ON KSAuditProducts.audprodProductID=KSProducts.productID AND KSAuditProducts.audprodServerID=KSProducts.productServerID ) INNER JOIN KSComputers ON KSAuditProducts.audprodComputerID=KSComputers.computerID AND KSAuditProducts.audprodServerID=KSComputers.computerServerID } WHERE (audprodAltProductID IS NULL) ORDER BY computerName, productName, productVersion
This query will summarize product usage for one month (February 2015 in the example). The results include the computer and product names along with the first and last times seen within the time range. The query is grouped so all usage of a product by a computer appears on one line.
SELECT MAX(productName), MAX(productVersion), MAX(computerName), MIN(usageOtherTime), MAX(usageWhen) FROM {oj (KSUsage INNER JOIN KSProducts ON KSUsage.usageProductID=KSProducts.productID AND KSUsage.usageServerID=KSProducts.productServerID ) LEFT OUTER JOIN KSComputers ON KSUsage.usageComputerID=KSComputers.computerID AND KSUsage.usageServerID=KSComputers.computerServerID } WHERE (usageEvent=4 OR usageEvent=9 OR usageEvent=11 OR usageEvent=13) AND usageWhen >= '2015-02-01 00:00:00' AND usageOtherTime < '2015-03-01 00:00:00' GROUP BY productName, productVersion, usageComputerID
This query will summarize denied program launches for one month (February 2015 in the example). The results include the computer and program names along with the number of times the program was denied usage on the computer. The query is grouped so all denials of a program on a computer appear on one line.
SELECT MAX(programVariantName), MAX(programVariantVersion), MAX(computerName), COUNT(*) FROM {oj (KSUsage INNER JOIN KSPrograms ON KSUsage.usageProgramID=KSPrograms.programID AND KSUsage.usageServerID=KSPrograms.programServerID ) LEFT OUTER JOIN KSComputers ON KSUsage.usageComputerID=KSComputers.computerID AND KSUsage.usageServerID=KSComputers.computerServerID } WHERE (usageEvent=7 OR usageEvent=14) AND usageWhen >= '2015-02-01 00:00:00' AND usageOtherTime < '2015-03-01 00:00:00' GROUP BY programVariantName, programVariant, usageComputerID