PC and User Information
This topic lists examples of how to access Client and user information from the Site database using the Database Viewer.
Retrieving Lists of PC Names which Match a Given Criteria
To retrieve the names of all Client PCs in the Administration Department:
SELECT NODES.NODENAME FROM NODES WHERE DEPT LIKE 'admin%';
To retrieve the names of all PCs belonging to the Engineering and Sales network domains:
SELECT NODES.NODENAME, NODES.DOMAINNAME FROM NODES
WHERE DOMAINNAME IN ('ENGINEERING', 'SALES');
To select the Client in those Departments which begin with a letter in the range A - F.
SELECT * FROM NODES WHERE DEPT BETWEEN 'A%' AND 'F%';
Summary of all Group Definitions
This statement retrieves the full list of fixed and dynamic groups that have been defined at a Site, together with the group description (the GRPCOMMENT field). It automatically sorts the list using the ORDER BY keywords:
SELECT GROUPS.GROUPNAME, GROUPS.GRPCOMMENT
FROM GROUPS ORDER BY GROUPS.GROUPNAME
Obtaining General Information about a Specific PC
To find out which department Client SB belongs to:
SELECT NODES.NODENAME NODES.DEPT FROM NODES WHERE NODES.NODENAME LIKE 'SB'
To find out the user name of the Client named SB:
SELECT NODES.NODENAME, NODES.USERNAME FROM NODES WHERE NODES.NODENAME LIKE 'SB'
To find out the Operating System of the Client named SB:
SELECT NODES.NODENAME, NODES.OSNAME FROM NODES WHERE NODES.NODENAME LIKE 'SB'
Finding Client names by Searching for User Names
A Client's NODENAME normally corresponds to the name displayed in the Network (Identification) dialog of the PC's Windows Control Panel. However, in some circumstances, such as when analyzing data from audited PCs, the Client name may not match the PC name.
If you need to discover the name of a Client, you may be able to retrieve it by specifying a user name as your SQL criteria:
SELECT NODES.NODENAME, NODES.USERNAME, NODES.DEPT FROM NODES
WHERE NODES.USERNAME LIKE 'Dean Bates'
Retrieving User Prompt Information
The Get User Data operation collects user information and desktop-related information, such as location, department, cost code, phone number and other asset information. The database's NODEPROP table stores all of the user prompted information.
Referencing Node Property Table Fields in SQL
The following field names can be used to extract information from Client properties:
Field |
Description |
Typical Usage in SQL Statement |
PROPNAME |
Property Name |
WHERE PROPNAME='$DEPARTMENT' |
PROPVALUE |
Property Value |
AND PROPVALUE LIKE 'engineering' |
These two field names (PROPNAME and PROPVALUE) can be incorporated in a SQL statement as shown in the two examples here, which retrieve a list of PCs which match specified Client Property criteria:
SELECT * FROM NODES
INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME
WHERE NODEPROP.PROPNAME = '$DEPARTMENT'
AND NODEPROP.PROPVALUE LIKE 'engineering'
SELECT NODES.NODENAME FROM NODES
INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME
WHERE NODEPROP.PROPNAME = '$LOCATION'
AND NODEPROP.PROPVALUE LIKE 'upstairs%'
Standard Client Properties
The standard Client properties are as follows:
Property Name |
Description |
$DEPARTMENT |
The department that the PC belongs to (for example, Engineering) |
$HWDATE |
The date on which hardware inventory was last saved to the Site database. |
$LOCATION |
The location of the PC (for example, Engineering Section, Mercury House) |
$OWNER |
The recorded owner of the PC |
$SWDATE |
The date on which the Software Inventory was last saved to the Site database. |
$TELNUMBER |
The user's telephone number |
$USERNAME |
The name with which the owner logs on to the PC |
$OfflineArea |
The path of the offline area used to transfer data between Clients and the Site Database. |
$OfflineId |
The Client-specific subdirectory - the PC's data upload area is located on path $OfflineArea\$OfflineID, such as \\AWS\PUSH%Guest\LU0005 |
Selecting the Client view details
Tokens enable you to pass data held in the Site Database to Command Launch operations. For example, you can use tokens (such as $NODE) in a SQL statement passed to the Database Viewer
When you launch the Database Viewer, the tokens are substituted with the appropriate values. For example, if the Client SB is selected in the Console's Clients view, then the $NODE token would return SB. In the example below, the token ($NODE) appears highlighted. Remember that tokenized values must be wrapped in the appropriate characters, such as a single quote ( ' ) for text values, or a hash ( # ) symbol for dates.
Tokens work only with individual Clients. If multiple Clients are selected, only the first Client in the selection is passed to the Command Launch operation.
This example returns the same summary details as those found in the Console's Clients view, with the addition of Operating System and Version information:
SELECT NODES.NODENAME AS Node, NODES.DEPT AS Dept,
NODES.OWNERNAME AS Owner, NODES.OWNERTELE AS Telephone,
NODES.CPU AS CPU, NODES.CPUSPEED AS Speed,
NODES.NETADDR AS NetworkAddress,
NODES.OSNAME AS OperatingSystem, NODES.OSVERSION AS Version
FROM NODES WHERE NODES.NODENAME LIKE '$NODE'