Assigning IP Subnets via SQL Query

[darwin_hammons]

DId anyone figure out the tables needed to assign the IP subnets to Beacon ?

I see how to find the subnets but then what table is need to query the Beacon / Flexera Component type ?

Select Subnet_MT.NetworkLocationID,Subnet_MT.IPSubnet, NetworkLocation_MT.Name, NetworkLocation_MT.DN
FRom
Subnet_MT
INNER JOIN NetworkLocation_MT ON NetworkLocation_MT.NetworkLocationID = Subnet_MT.NetworkLocationID

[mfranz]

SELECT *
FROM Site si
LEFT JOIN SiteSubnet sisu
	ON si.SiteID = sisu.SiteID
LEFT JOIN BeaconSiteSubnetMapping bssm
	ON sisu.SubnetID = bssm.SubnetID
LEFT JOIN Beacon b
	ON bssm.BeaconID = b.BeaconID

[Liebi123]

Hi,
maybe the following trigger will help you to reach your goal? The trigger automatically assigns all sites to the main beacon. Of course it can be customized if there are multiple beacons.

Best
Alex

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trd_AddBeaconSiteSubnet]'))
DROP TRIGGER [dbo].[trd_AddBeaconSites]

GO

CREATE TRIGGER [dbo].[trd_AddBeaconSiteSubnet]
	ON [dbo].[SiteSubnet_MT]
	AFTER INSERT, UPDATE
AS
BEGIN
	
-- ASSIGN ALL SITE SUBNET TO MAIN BEACON AFTER INSERT AND UPDATE ON TABLE SITESUBNET_MT
	SET NOCOUNT ON;

	-- CREATE TEMP TABLE FOR IPADDRESS TO BEACON ASSIGNMENT
	IF OBJECT_ID('tempdb..#BeaconSiteSubnetMapping') IS NOT NULL
		DROP TABLE #BeaconSiteSubnetMapping

	CREATE TABLE #BeaconSiteSubnetMapping 
		(
			BeaconID		[int] NOT NULL,
			SubnetID		[int] NOT NULL
		)

	-- INSERT DETAILS OF SITE SUBNET TO BEACON ASSIGNMENT
	INSERT INTO #BeaconSiteSubnetMapping
	SELECT
		(SELECT TOP 1 BeaconID FROM Beacon_MT) AS [BeaconID]
		,SubnetID AS [SubnetID]
	FROM [dbo].[SiteSubnet_MT]

	-- CREATE SITE SUBNET TO BEACON ASSIGNMENT
	EXEC BeaconSiteSubnetMappingPutBatchByBeaconIDSubnetID

END