topic Re: Query NAC Database in ExtremeCloud IQ- Site Engine Management Center

Query NAC Database

TeamNetzBetrieb — Thu, 28 Feb 2019 16:55:23 GMT

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

Re: Query NAC Database

StephanH — Thu, 28 Feb 2019 21:03:06 GMT

Hello Michael,

there are more steps to get your requested date. First of all you have to use the XMC database not a NAC database. All data are transmitted and aggregated in XMC.

Here is a excellent article how to connect to the database from @Martin Flammia (he is here in the HUB, too):
http://www.extremenetworks.guru/extreme-networks-managemet-netsight-access-database-externally/

If you are connected to the database (the correct one is the netsightrpt database for the statistics), you have to check where your data are stored. For that "select * from rpt_statistics" to receive all statistics availably on your system. Please be awary that entries differ from installation to installation.

Search in the output for your statistic. I think the display name is "NAC End-Systems seen last 24 hours "
Take the ID form the first column.

Now you can do an "Select * from rpt_default_day where statisticid =

Have fun.

Best regards
Stephan

Re: Query NAC Database

TeamNetzBetrieb — Thu, 28 Feb 2019 23:10:25 GMT

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

Re: Query NAC Database

Kurt_Semba — Fri, 01 Mar 2019 15:50:09 GMT

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

Re: Query NAC Database

StephanH — Thu, 07 Mar 2019 13:15:51 GMT

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

Re: Query NAC Database

TeamNetzBetrieb — Wed, 20 Mar 2019 21:51:51 GMT

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