KSC database fields explained: nIP, nStatus [KSC for Windows]

[Antipova Anna]

Advice and Solutions (Forum Knowledgebase) Disclaimer. Read before using materials.

There are multiple fields in database that are not easy to interpret. For example nIP, nStatus and many others. Most of them are from public view v_akpub_host which is one of the main sources of information about managed computer on KSC. The objective of this article is to help understanding the encoding used, if you want to learn more about public views and specific fields refer to klakdb.chm located in the KSC installation folder.

nIP

When you will query for an IP address the result may surprise you. Instead of IP address you will receive a number, let's say 2130706433, which actually translates to 127.0.0.1. Here is an explanation how this translation is achieved.

Number 2130706433 equals 1111111000000000000000000000001‬ in binary.

Let's split it into groups of 4 to make it easier to read. 0111 1111.0000 0000.0000 0000.0000 0001 (leading zero is added for visibility). IP address is 4 byte long, which is 32 bits. As you see there are exactly 32 numbers divided into 4 groups called octets. It already starts to look like an IP address. We just need to convert binary back to decimal, while keeping it grouped: 127.0.0.1

The same can be done with the SQL query, here is an example, which returns Computer Name and its IP address in human readable format

SELECT  wstrDisplayName                                             "Display Name",         CAST(   ((nIp / 16777216) & 255) AS varchar(4)) + '.' +             CAST(((nIp / 65536) & 255) AS varchar(4)) + '.' +             CAST(((nIp / 256) & 255) AS varchar(4)) + '.' +             CAST(((nIp) & 255) AS varchar(4)         )                                                           "IP Address" FROM v_akpub_host;

Another good example is the following code which returns host's visibility, nagent installed or not, nagent alive or not and real time protection state:

SELECT h.wstrDnsName , h.wstrDisplayName, /* h.nStatus 'Host Status', */ /* Host status, bit set bit 0 set if host is visible, bit 2 set if Network Agent is installed bit 3 set if Network Agent is "alive" bit 4 set if real-time protection is installed */ CAST(((h.nStatus) & 1) AS varchar(1)) as 'Host Visible', CAST(((h.nStatus / 4) & 1) AS varchar(1)) as 'Agent Installed', CAST(((h.nStatus / 8 ) & 1) AS varchar(1)) as 'Agent Alive', CAST(((h.nStatus / 16) & 1) AS varchar(1)) as 'Protection Installed' FROM v_akpub_host h order by wstrDisplayName

nStatus

nStatus is another useful parameter stored as decimal integer. The key to understanding is the same, yet in this case each bit (not like in previous case where each 8 bits represented a number in IP address) represents its own aspect of a state. We should treat is as a (binary) bit set, where (information below is from klakdb.chm) :

• bit 0 is set if host is visible
• bit 1 is reserved
• bit 2 is set if Network Agent is installed
• bit 3 is set if Network Agent is "alive"
• bit 4 is set if real-time protection is installed

For example nStatus equals 29. 29 is 11101 in binary. Remember that binary is read from left to right. In this case the status is as follows:

• bit 0 equals 1 – that means a bit is set, which in our case means that the host is visible.
• bit 1 equals 0, but as it is reserved, we just omit it.
• bit 2 equals 1, so Network Agent is installed on the host.
• bit 3 equals 1, which means that Network Agent is “alive” – can communicate with SC etc.
• bit 4 equals 1, so protection (KES, KSWS, etc.) is installed on the host.

Additional reading

To learn more about this data format refer to this article https://en.wikipedia.org/wiki/Endianness