How to get client association event report from FM that includes association time and device details?

Summary

Default client association event can be obtained from the FM GUI. But these events don't give client OS details. Following query will provide client's MAC address, OS, username if it is a captive portal or a dot1x SSID, AP's MAC address, and join time.

Question

Is there a single association event report I can generate that gives me detailed information of the client?

Customer Environment

ZD managed by FM network

Root Cause

Default client association and disconnection report isn't detailed enough

Resolution

FlexMaster's monitor section gives insight into past and current status of the network. Reports can be generated for performance, alerts, and events from the Monitor tab.

One of the significant reports of interest could be who is joining the network and at what time. To get this report, admin can follow the below procedure:
1. Click on Events under the Monitor
2. Choose "Event Type", "Exactly equals", and "Client join" from the dropdowns. Then click on the "Query" button.
3. This will show all events saved in the database.
(Number of events that can be saved under the FM is controlled by a purge policy which is under Administer, System Settings. Looks for events older than setting.)

These events can be exported to a XLS or CSV file from this screen.

As you will see this report doesn't have many details of the client device.

The only way to get such information is through a query directly from the database. This requires MySQL shell access.

Assuming you have the details of MySQL database credentials setup at the time of FM install, here is how to login to MySQL's shell to issue the required query:
1. Login to the FM machine using a SSH terminal such as PuTTY.
2. Go to the mysql folder using the "cd /opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.1.53-linux-x86_64-glibc23/" command.
3. Issue the "./bin/mysql -S ./mysql.sock -ur -uroot -pmyP@$$@123" command to drop into the mysql prompt. Here "root" is the mysql username and "myP@$$@123" is the mysql password.
4. At the "mysql>" prompt type "use itms;" command.
5. Now type the "SELECT e.clientMAC AS 'Client MAC',c.os AS 'Device Info', c.hostName AS 'Host Name', c.manufacture AS 'Vendor', e.eventAttr, e.timest AS 'Association date' FROM (SELECT clientmac, eventattr,FROM_UNIXTIME(devicetimestamp) AS timest,zdapmac FROM event WHERE eventTypeid=10148 ORDER BY devicetimestamp) e LEFT JOIN zdclient c ON c.mac=e.clientMAC
INTO OUTFILE '/tmp/client_association_step1_new.csv' FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\n';" command to get the client association event report in the desired format.

This report gets saved in a .csv format under the /tmp folder as desired above.

Article Number:
000002879

Updated:
May 06, 2020 04:43 PM (over 4 years ago)

Tags:
System Network Management, FlexMaster UMM

Votes:
0

This article is:
helpful
not helpful