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'