My head is spinning trying to figure this one out. Ok. Here is the schema...
--
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [snmpPoller_HostConfig](
[id] [int] IDENTITY(1,1) NOT NULL,
[friendlyName] [nvarchar](50) NOT NULL,
[hostname] [nvarchar](255) NOT NULL,
[community] [nvarchar](255) NOT NULL,
[version] [int] NOT NULL,
[queryTimeout] [int] NOT NULL,
[isActive] [bit] NOT NULL,
[passedSanityCheck] [bit] NOT NULL CONSTRAINT [DF_snmpPoller_HostConfig_passedSanityCheck] DEFAULT ((0)),
CONSTRAINT [PK_snmpPoller_HostConfig] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [snmpPoller_TargetConfig](
[id] [int] IDENTITY(1,1) NOT NULL,
[hostId] [int] NOT NULL,
[description] [nvarchar](50) NOT NULL,
[oid] [nvarchar](255) NOT NULL,
[interval] [int] NOT NULL,
[lastPollTimestamp] [datetime] NOT NULL,
[nextPollTime] AS (dateadd(second,[interval],[lastPollTimestamp])),
[isActive] [bit] NOT NULL,
[isPolling] [bit] NOT NULL,
CONSTRAINT [PK_snmpPoller_TargetConfig] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[snmpPoller_TargetConfig] WITH CHECK ADD CONSTRAINT [FK_snmpPoller_TargetConfig_snmpPoller_HostConfig] FOREIGN KEY([hostId])
REFERENCES [snmpPoller_HostConfig] ([id])
Ok. So I have this query
WITH WorkTable ( Tag, Parent, [Host!1!hostId],
[Target!2!targetId], [Host!1!hostname], [Host!1!community],
[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]
)
AS
(
SELECT DISTINCT
1 AS Tag,
NULL AS Parent,
H.id AS [Host!1!hostId],
NULL AS [Target!2!targetId],
hostname AS [Host!1!hostname],
community AS [Host!1!community],
version AS [Host!1!version],
queryTimeout AS [Host!1!timeout],
NULL AS [Target!2!oid],
NULL AS [Target!2!interval]
FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T
WHERE H.id = T.hostId
AND t.isPolling = 0
AND t.isActive = 1
AND h.isActive = 1
AND t.lastPollTimestamp < { fn NOW() }
AND h.passedSanityCheck = 1
UNION ALL
SELECT DISTINCT
2 AS Tag,
1 AS Parent,
hostId AS [Host!1!hostId],
T.id AS [Target!2!targetId],
NULL AS [Host!1!hostname],
NULL AS [Host!1!community],
NULL AS [Host!1!version],
NULL AS [Host!1!timeout],
oid AS [Target!2!oid],
interval AS [Target!2!interval]
FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T
WHERE H.id = T.hostId
AND t.isPolling = 0
AND t.isActive = 1
AND h.isActive = 1
AND t.lastPollTimestamp < { fn NOW() }
AND h.passedSanityCheck = 1
)
SELECT * FROM WorkTable
ORDER BY [Host!1!hostId], [Target!2!targetId]
FOR XML EXPLICIT, ROOT ('Hosts')
That generates this xml
<Hosts>
<Host hostId="6" hostname="XXX.XXX.XXX.XXX" community="XXXXX" version="2" timeout="30">
<Target targetId="1757" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />
<Target targetId="1758" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />
<Target targetId="1759" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />
<Target targetId="1760" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />
</Host>
<Host hostId="8" hostname=" XXX.XXX.XXX.XXX " community="XXXXXX" version="2" timeout="30">
<Target targetId="2037" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />
<Target targetId="2038" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />
<Target targetId="2039" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />
<Target targetId="2040" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />
</Host>
</Hosts>
Now the problem is that the xml that I generate is to ‘big’. When I submit a get request to the host, I can only fit 128 OIDs into the request PDU. My query, as it is, returns many hosts with upwards with 1000 targets. So question number 1 is how do return the top 128 targets for each host?
Now here is the real problem. I am replacing a working design in an effort to pick up a scale point. The current design has a dispatcher that will hand off the hostId to a work queue where it is picked up by a ‘thread’. This thread will then execute this sql.
SqlCommand^ command = connection->CreateCommand();
command->Parameters->AddWithValue("@.hostId", hostId);
command->CommandText =
"DECLARE @.resultTable TABLE("+
" targetId int NOT NULL,"+
" hostId int NOT NULL,"+
" oid nvarchar(255) NOT NULL"+
")"+
""+
"UPDATE TOP(128) snmpPoller_TargetConfig"+
" SET isPolling = 1 "+
" OUTPUT INSERTED.id, INSERTED.hostId, INSERTED.oid INTO @.resultTable"+
"WHERE (isPolling = 0) AND (isActive = 1) AND (id = @.hostId) AND (lastPollTimestamp < { fn NOW() })"+
""+
""+
"SELECT * FROM @.resultTable";
SqlDataReader^ reader = command->ExecuteReader();
So rather than make (host * (number of targets / 128)) calls per dispatch cycle, I need to make it all in one call.
So SQL gurus…. What’s the kung-fu? How do I get all my hosts with the top 128 targets AND set a flag on all targets in one atomic shot (I believe this ‘one-shot’ statement can be built off the with(), but I’m nothing but confused at this point)
Paul
Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com
I actually just came up with this. It works just fine, but I'd like to consolidate it down to one statement. Any thoughts?
--
BEGIN TRANSACTION
DECLARE @.buffer TABLE
(
Tag int,
Parent int,
[Host!1!hostId] int,
[Target!2!targetId] int,
[Host!1!hostname] nvarchar(255),
[Host!1!community] nvarchar(255),
[Host!1!version] int,
[Host!1!timeout] int,
[Target!2!oid] nvarchar(255),
[Target!2!interval] int
);
WITH WorkTable ( Tag, Parent, [Host!1!hostId],
[Target!2!targetId], [Host!1!hostname], [Host!1!community],
[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]
)
AS
(
SELECT DISTINCT
1 AS Tag,
NULL AS Parent,
H.id AS [Host!1!hostId],
NULL AS [Target!2!targetId],
hostname AS [Host!1!hostname],
community AS [Host!1!community],
version AS [Host!1!version],
queryTimeout AS [Host!1!timeout],
NULL AS [Target!2!oid],
NULL AS [Target!2!interval]
FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T
WHERE H.id = T.hostId
AND t.isPolling = 0
AND t.isActive = 1
AND h.isActive = 1
AND t.lastPollTimestamp < { fn NOW() }
AND h.passedSanityCheck = 1
UNION ALL
SELECT DISTINCT
2 AS Tag,
1 AS Parent,
hostId AS [Host!1!hostId],
T.id AS [Target!2!targetId],
NULL AS [Host!1!hostname],
NULL AS [Host!1!community],
NULL AS [Host!1!version],
NULL AS [Host!1!timeout],
oid AS [Target!2!oid],
interval AS [Target!2!interval]
FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T
WHERE H.id = T.hostId
AND t.isPolling = 0
AND t.isActive = 1
AND h.isActive = 1
AND t.lastPollTimestamp < { fn NOW() }
AND h.passedSanityCheck = 1
AND t.id IN(
SELECT TOP(128) id FROM snmpPoller_TargetConfig
WHERE isPolling = 0 AND isActive = 1 AND lastPollTimestamp < { fn NOW() }
AND hostId = h.id
)
)
INSERT INTO @.buffer
SELECT * FROM WorkTable
UPDATE t SET isPolling = 1
FROM snmpPoller_TargetConfig t,
@.buffer b
WHERE
t.id = b.[Target!2!targetId]
SELECT * FROM @.buffer
ORDER BY [Host!1!hostId], [Target!2!targetId]
FOR XML EXPLICIT, ROOT ('Hosts')
COMMIT TRANSACTION
Sorry, I can't post the data, the schema should be suffecient. Just say there for 100 hosts records and 750 target records per host.
|||Please take a look at the OUTPUT clause. You can just perform the UPDATE statement and get the relevant columns (even expressions) from the inserted/deleted tables into a table variable for example. You can then do the SELECT against the table variable. Please take a look at my blog post below for one such example:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx
