SQL query help for guests/endpoint profiles
06-05-2019 11:02 AM
I'm in the process of building a dashboard in Grafana for our guest self registration and I'm running across an issue.
As a background, I am accssing the tipsdb database. I found that the MAC address stored in the tips_guest_users table is stored in uppercase with dashes within the colum of attributes (in json). I was able to convert it to lowercase and remove the dashes with a simple query like this:
SELECT DISTINCT LOWER( TRANSLATE ( (g.attributes->>'mac'),'-','') ) AS mac FROM tips_guest_users g WHERE g.attributes->>'source' = 'social_register'
However now I want to cross reference this to the endpoint table so I can extract the device type that the guest is using. A query like this should work however it keeps timing out:
SELECT DISTINCT LOWER( TRANSLATE ( (g.attributes->>'mac'),'-','') ) AS mac, p.device_name FROM tips_guest_users g LEFT JOIN tips_endpoint_profiles p ON mac = p.mac WHERE g.attributes->>'source' = 'social_register'
Has anyone else done this before and gotten it to work?
Here is a sample of the dashboard I'm creating: