November 5, 2007 at 1:08 pm
I'm on SQL Server 2005.
Our department is tasked to "check" that the latest and newest NIC driver version is installed on our servers.
The NIC version is timestamped.
from the server audit data, we get what is called the NIC description.
Samples of NIC description:
-Intel(R) PRO/1000 PT Dual Port Server Adapter
-Broadcom BCM5708C NetXtreme II GigE (NDIS VBD Client)
-HP NC7770 Gigabit Server Adapter
-HP NC3134 Fast Ethernet NIC
from the NIC description, we build a database table that consists of the "string identifier" and the latest version number. We would like to use the NIC Description as input, look up the table and return the latest version.
The string identifier is simply a substring of the NIC description. Hence from the sample NIC descriptions above, sample string identifiers are:
- Intel(R) PRO/1000
-NetXtreme II GigE
-NC7770
-NC3134
There are about 300+ of these string identifiers.
My question (finally):
Given the NIC description, say HP NC3134 Fast Ethernet NIC, i would like to hit the string identifier NC3134 to get the latest version. However, I would like to avoid having to cursor through the list of 300+ string identifiers for performance reasons.( each server has at least 3 NICs).
My plan is to take the NIC description as an input parm into a stored proc, somehow run it through the list of string identifiers on the table in the most efficient manner and return the latest version.
I already have to do a charindex to find a match from the NIC description to the string identifier. So using a cursor to run through the list of 300+ string identifiers until a match is found or not found seems to be rather time consuming.
Please advise. Thank you much in advance.
November 5, 2007 at 1:43 pm
Thinking about that I would do something like this
SELECT
....
FROM
dbo.Audit_Data AD
INNER JOIN
dbo.NIC N
ON
AD.NIC_VALUE LIKE '%' + N.NIC_DESC + '%'
This assumes your NIC DESC will not need any special circumstance where the string is out of sequence (1) in the VALUE from the Audit Data and that NIC DESC will create unique joins (2).
1) Broadcom NetXtreme BCM5708C II GigE (NDIS VBD Client) = NetXtreme II GigE
2) HP NC7770 NetXtreme II GigE Gigabit Server Adapter = ? Which si it equal to.
Note it will not be as effective as joining it with equal but will eliminate the need for a cursor
November 5, 2007 at 8:20 pm
Thank you, anteres686. That is cool. So far it works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply