Database layout
NoTouch Center stores its data in a database. The database layout has been in use for a while and is very stable. There are several "core" tables that you will use in pretty much any query you make, e.g. for creating custom Reports.
Note: If you are looking how to configure database access, look here: Database configuration. If you want to open up your MySQL for custom Reports, look here: Direct MySQL access. This article is about the database layout itself.
Contents
Clients and groups
CONFIGOBJECT
The most important table is CONFIGOBJECT. It contains devices, groups and connections. The important columns are:
- COID. A unique, numeric ID for the individual entry. It is used to identify groups and clients and also for joins with other tables.
- NAME. The name is textual/character string field that contains the visible name of the object (i.e. the one it is represented in the tree).
- COTYPE. This field denotes the type of object.
- 0. Group.
- 1. Group template client.
- 2. Group template connection.
- 3. Client.
- 4. Connection.
- Values greater than 4 are reserved for internal purposes.
- PARENT. This field contains the COID of the parent. So, if a client is member of a group, the group's COID will be its PARENT value.
It is important to keep the 0/HEAD row in the database. The HEAD group is the invisible super-group of all groups. Clients that are directly assigned to HEAD are essentially the "Unassigned" clients. For instance, if you want to query for the unassigned devices, check for COTYPE=3 (clients) and PARENT=0. Similarly, all entries with COTYPE=3 and PARENT<>0 are clients that are assigned to a group.
+------+-------------------+--------+--------+--------+-------+---------+ | COID | NAME | COTYPE | PARENT | MLEVEL | CCOID | VISIBLE | +------+-------------------+--------+--------+--------+-------+---------+ | 0 | HEAD | 0 | 0 | 0 | 0 | 0 | | 1 | DE | 6 | 0 | 0 | 0 | 0 | | 2 | EN | 6 | 0 | 0 | 0 | 0 | | 3 | My Clients | 0 | 0 | 0 | 0 | 1 | | 4 | Default | 1 | 3 | 1 | 0 | 1 | | 5 | _tcstartup | 2 | 4 | 2 | 0 | 1 | | 6 | LIS000C29263944 | 3 | 3 | 1 | 4 | 1 | | 7 | _tcstartup | 4 | 6 | 2 | 5 | 1 | | 8 | Browser | 2 | 4 | 2 | 0 | 1 | | 9 | Browser | 4 | 6 | 2 | 8 | 1 | | 12 | My VDI connection | 2 | 4 | 2 | 0 | 1 | | 13 | My VDI connection | 4 | 6 | 2 | 12 | 1 | +------+-------------------+--------+--------+--------+-------+---------+
Note how the connections are present multiple times, because they are part of the group template (COID=4) as well as the client (COID=6).
CLIENTSTATE
The CLIENTSTATE table contains the state (on/off/updating/...) and the Event ID of the last Status Event.
+------+-------+------+ | COID | STATE | EID | +------+-------+------+ | 6 | 1 | 15 | +------+-------+------+
The fields are:
- COID. Contains the COID of the related CONFIGOBJECT.
- STATE. The following "states" are used:
- 0. Up.
- 1. Down. (Well, to be more precise, we "think" that the client is down [1])
- 2. Reconfiguring.
- 3. Download update.
- 4. Writing update.
- 5. Unknown state.
- 6. Just booting up.
- 7. Halted. Client was powered down.
- EID. Relates to an event EID.
In a productive installation you will find that each client will have an entry in CLIENTSTATE. Still, CLIENTSTATE will have less entries than CONFIGOBJECT, of course, because CONFIGOBJECT also contains groups and connections and more.
For clients (and only for clients) you can treat CLIENTSTATE as a natural extension to CONFIGOBJECT. The following join is very common:
SELECT * FROM CONFIGOBJECT CO JOIN CLIENTSTATE CS on CO.COID=CS.COID; +------+-----------------+--------+--------+--------+-------+---------+------+-------+------+ | COID | NAME | COTYPE | PARENT | MLEVEL | CCOID | VISIBLE | COID | STATE | EID | +------+-----------------+--------+--------+--------+-------+---------+------+-------+------+ | 6 | LIS000C29263944 | 3 | 3 | 1 | 4 | 1 | 6 | 1 | 15 | +------+-----------------+--------+--------+--------+-------+---------+------+-------+------+
Note: A database wizard might say, wait, shouldn't we rather use a LEFT JOIN plus filtering for CO.COTYPE=3? Well, yes and no. We are not querying for clients. We want to get the clientstate information. Records that do not have a client state, we are not interested in (normally).
Configuration
The set (and only the "set") configuration values are stored in CONFIGVALUE. Some of the values are automatically set and used by NoTouch Center. Others correspond exactly to the values you see in the GUI that have the checkbox checked.
SELECT * FROM CONFIGVALUE WHERE COID>2; +------+-----------------------+--------------------------------------------------------+ | COID | CODE | VAL | +------+-----------------------+--------------------------------------------------------+ | 3 | AUTOASSIGN_UNIQUE_KEY | gxxxxcej | | 3 | LICENSE2 | LMD | | 5 | SESSION_MODE | 17 | | 6 | CLIENT_RID | 5 | | 6 | INSTALLED_IMAGE | 2.39.397d-EEs-k206-150610 | | 6 | LICENSE2 | LMD | | 6 | MAC_ADDRESS | 00:0C:29:XX:XX:44 | | 6 | SERIAL | 3 | | 6 | STAT_PLATFORM_ID | 0 | | 6 | STAT_SERIALNR | 564d9defc2ea21cf-3dae1b2d74263944 | | 6 | TC_SERIAL | 2 | | 8 | SESSION_MODE | 11 | +------+-----------------------+--------------------------------------------------------+
You can not derive a client's configuration purely from the database. NoTouch Center takes scripted parameters, inheritance and default values into account when generating a configuration for clients.
Please note that COIDs 0, 1 and 2 are used internally and so are the CONFIGVALUEs that relate to these COIDs. Hence we suggest to always include a WHERE COID>2 clause for readability and to avoid unwanted values.
Events and Status Values
STATUSVALUE
The STATUSVALUE table contains the values that a clents with each announce. A common idiom to get the latest status values to a given COID is this:
select SV.CODE,SV.VAL FROM STATUSVALUE SV JOIN CLIENTSTATE CS ON CS.EID=SV.EID WHERE CS.COID=6; +------------------+--------------------------+ | CODE | VAL | +------------------+--------------------------+ | ASSET_TAG | No Asset Tag | | BAT2_INF_PRESENT | No information available | | BAT_INF_PRESENT | No information available | | BIOS_RELEASE | 05 | | BIOS_VENDOR | Phoenix Technologies LTD | [...] +------------------+--------------------------+
Similarly, if you just one status value, add another clause:
select SV.CODE,SV.VAL FROM STATUSVALUE SV JOIN CLIENTSTATE CS ON CS.EID=SV.EID WHERE CS.COID=6 AND CODE="SYSTEM_PRODUCT"; +----------------+-------------------------+ | CODE | VAL | +----------------+-------------------------+ | SYSTEM_PRODUCT | VMware Virtual Platform | +----------------+-------------------------+
What we want to stress is that the queries here are really "shortcuts" to the status values of the "last" status event because this is what most people are interested in. Also, many status values do not change over time anyway (unless you exchange a PC's motherboard). Others however do change at each time (e.g. client uptime/load information). If you are also interested in the history of events, please read on.
EVENT
The EVENT table stores client events, i.e. whenever something happens with or to a client. These are the fields:
- EID. Automatically assigned Event ID.
- COID. The ID of the CONFIGOBJECT the event relates to.
- ETYPE. Event type:
- 1. Announce/Status event. The client did an "announce" and status values were stored. There should be entries in the STATUSVALUE table relating to exactly that event's EID.
- 2. Message. A message was received and stored.
- 3. Client presumed off.
- 4. Client MKey authentication failure (token mismatch or client has no token at all). This naturally happens after a factory reset and is a good thing (security).
- 5. Client MKey authentication token was reset.
- 6. The client requests a firmware URL.
- IDATE. The date/timestamp when the event occured.
That means not all events store status values. Only those where ETYPE=1 store status values.
Note: There is a thread in NoTouch Center that mercilessly deletes older status values and events. The - at that particular time - "last" (latest) status event will never be deleted, however!