FAQ

Beschreibung

Das Bitlocker Management von BitTruster liefert einige vordefinierte Reports mit.

Wollen Sie selbst Reports definieren können, so können diese vordefinierten Reports als Anschauungsmaterial dienen.
Auch sehen Sie anhand dieser Beispiele, welche Tabellen und Felder in der hinterlegten SQL Datenbank hierfür angesprochen werden.

Lösung

Total Count of Machines

SELECT COUNT(Computers.ComputerID) ‘Total Count of Computers’
FROM Computers
WHERE Computers.Active = 1

LICENSE COUNT

SELECT COUNT(Computers.ComputerID) ‘LICENSE COUNT’
FROM Computers
JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
WHERE Computers.Active = 1 AND Volumes.BootPartition = 1 AND Volumes.ConversionStateCRCPercentage > 0 AND Computers.Active = 1 AND Volumes.Active = 1

List of compliant computers with operating system

SELECT Computers.ComputerName ‘Compliant Computer’, OSTypes.Name ‘Operating System’
FROM Computers
JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
WHERE Computers.Active = 1 AND Volumes.BootPartition = 1 AND Volumes.ProtectionStateID = 1 AND (Volumes.Active = 1 OR Volumes.Active = null)

List of compliant computers with operating system and assigned users

SELECT Users.ForeName ‘Foreame’, Users.Name ‘Lastname’, Computers.ComputerName ‘Computer’, OSTypes.Name ‘Operating System’
FROM Computers
JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
JOIN Users2Computers ON Users2Computers.ComputerID = Computers.ComputerID
JOIN Users ON Users.UserID = Users2Computers.UserID
WHERE Computers.Active = 1 AND Volumes.BootPartition = 1 AND Volumes.ProtectionStateID = 1 AND (Volumes.Active = 1 OR Volumes.Active = null)

Count of compliant computers

SELECT COUNT(Computers.ComputerID) ‘Count of Compliant Computers’
FROM Computers JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
WHERE Computers.Active = 1 AND Volumes.BootPartition = 1 AND Volumes.ProtectionStateID = 1 AND (Volumes.Active = 1 OR Volumes.Active = null)

List of non-compliant computers with operating system

SELECT Computers.ComputerName ‘Non-Compliant Computer’, OSTypes.Name ‘Operating System’
FROM Computers
LEFT JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
LEFT JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
WHERE Computers.Active = 1 AND ( (Volumes.BootPartition = 1 AND (Volumes.Active = 1 OR Volumes.VolumeID = null) ) OR Volumes.BootPartition IS NULL) AND (Volumes.ProtectionStateID !=1 OR Volumes.ProtectionStateID IS NULL)

Count of non-compliant computers

SELECT COUNT(Computers.ComputerID) ‘Count of Non-Compliant Computers’
FROM Computers
LEFT JOIN Volumes ON Volumes.ComputerID = Computers.ComputerID
LEFT JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
WHERE Computers.Active = 1 AND ( (Volumes.BootPartition = 1 AND Volumes.Active = 1 ) OR Volumes.BootPartition IS NULL) AND (Volumes.ProtectionStateID !=1 OR Volumes.ProtectionStateID IS NULL)

List of machines without a successful connection

SELECT Computers.ComputerName ‘Computer’, OSTypes.Name ‘Operating System’, Computers.LastFailedConnection ‘Last connection attempt’
FROM Computers
LEFT JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
WHERE Computers.Active = 1 AND Computers.LastSuccessfullConnection IS NULL

Count of machines without a successful connection

SELECT COUNT (Computers.ComputerID)’Count of Computers without a successful connection’
FROM Computers
LEFT JOIN OSTypes ON OSTypes.OSTypeID = Computers.OSTypeID
WHERE Computers.Active = 1 AND Computers.LastSuccessfullConnection IS NULL

List of known Computer Models

SELECT Manufacturer, Model
FROM ComputerModels