Query NAC Database

[TeamNetzBetrieb]

Hi, can anybody tell me, how I can query the database on the NAC?

Hint:
I try to make a query for the actual count of end systems on the NAC?
I have found, that a table "end_system" in the database exist, but the query:
tag=# select end_system_id from end_system
give me no results.

Regards
Michael

[TeamNetzBetrieb]

Hi SH,

thank you very much for your suggestion with the XMC databases, so I can find a very good solution for my problem:

First, I have to use the database "netsight", not "netsightrpt". As table in this database I use "tam_endsystem". Here all end-systems are stored. Then I made a search for all end-systems with status "ACCEPT" on each of our nac-appliances.

So, my cli command in directory /usr/local/Enterasys_Networks/NetSight/mysql/bin/mysql is:
./mysql --host=localhost --user=USER -pPASSWORD --port=4589 --protocol=TCP netsight -e 'select COUNT(*) from tam_endsystem where nacApplianceIp = "IP-OF-NAC-APPLIANCE" AND STATE = "ACCEPT";'

This script I saved for every of our nac-appliances with there corresponding IP-Address.

Regards and thanks,

Michael

[StephanH]

Hello Kurt,

thank you for your hint regarding graphiql. That's a good possibility. But in my opinion. The best solution depends on what somebody wants do to with the end system count.

If you will see the consumed licenses it's hard to pull the end system counts time by time, because of some mac addresses do not live long depending on the timers in switches, nac and wlan.
For this case the database information is much more accurate I think. Depending how accurate it must be . If 100 mac more or less it's not a problem to pull the end system macs.


Regards
Stephan

[Kurt_Semba]

Mike,

as Stephan mentioned: if you query the end-system count from a NAC appliance's DB you will need to repeat this process on all your NAC appliances. It is easier to query the total count of all end-systems from all NAC appliances from the XMC database.

That said, we generally do not encourage customers/partners to directly query the DB. The preferred option would be to query end-system (and other) data via the API. If you have XMC v8.2 a simple way would be to pull the list of all MACs and then count them yourselve through some script. To test this, use your browser to go to your XMC at (use your XMC IP)

https://10.65.47.90:8443/nbi/graphiql/index.html

On the left side insert
{
accessControl{
allEndSystemMacs
}
}

Then click the play button above and you should see the list of all MACs on the right.

Kurt

[TeamNetzBetrieb]

Hi, thank you for your answer. I will test this.

In the meantime, I have found another method. I have made a script with the following statement:
psql -U postgres -d tag -c "select count(*) from end_system" | grep -A1 - | grep -v -

The script open the database "tag" (the postgres database on the nac) and count the entries in the table end_system. The output gives me 4 lines, the count is found under the line "-----" because of this I made some grep for isolating the number.

Regards

Michael