ISE: get endpoint last auth date based on mac address

[syllkons1]

Hello, 

I need to get the last successful authentication date of an endpoint based on its mac address from ISE. This will have to be automated down the road so using the GUI Reports is not an option.

After testing the API I realized it's not possible to get historical data, just active session info.

Would this be something I could do with data-connect? The idea is to dynamically provide a list of mac addresses to some kind of python script and obtain the last authentication date for each mac address.

BR,

Konstantinos

[ahollifield · Accepted solution]

https://cs.co/ise-berg#dataconnect

Or send syslog somewhere and report/API to that syslog collector/SIEM.

[thomas · Accepted solution]

Yes, Data Connect is the fastest and easiest method by querying ENDPOINTS_DATA or RADIUS_ACCOUNTING tables.

https://github.com/1homas/ISE_Python_Scripts :
- iseql.py
- isedc.py | Docs
- data/SQL : contains many example SQL queries

[syllkons1]

Hey @thomas , thank you for the effort you have put into this, I have gone over your github and youtube videos and everything is really helpful. 

That being said, I am looking for something very specific i.e. the last time an endpoint was seen on ISE - using MAB in my scenario.

For this I have been querying ENDPOINTS_DATA for field UPDATE_TIME but I am not sure if that's the correct field to be looking at. Do you know if that's the one? I have checked other tables as well but I can't seem to find this information anywhere else.