mine_detector

The SQL database view “v_GS_NETWORK_ADAPTER_CONFIGURATION” contains inventoried data about NIC’s on managed devices.  The IPAdress0 column usually mashes IPv4 and IPv6 addresses together when a device has both protocols enabled.   Pulling a distinct list of IPv4 addresses from this can be somewhat tricky.  The query below uses the IIF, LEFT and PATINDEX functions to parse the column string data and return just IPv4 address values for each.  Cheers!

CREATE TABLE #DAVE
SELECT   dbo.v_R_System.Name0, dbo.v_R_System.AD_Site_Name0,
dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,
IIF (
IPAddress0 LIKE ‘%,%’,
LEFT(dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0, PATINDEX(‘%,%’, dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0)-1),
IPAddress0) AS X
INTO #DAVE
FROM    dbo.v_R_System INNER JOIN
dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION ON dbo.v_R_System.ResourceID = dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
WHERE  (dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 IS NOT NULL)
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s