Security

Reply
Frequent Contributor I

SQL query help for guests/endpoint profiles

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:

 

image.png

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: