Last updated on March 4th, 2023 at 03:11 pm
While working on my article What is Parsename and 4 Part Version String?, I was looking at my IP address of many of my system and notice that they have both IP v4 and IP v6 address. This is NOT generally a problem as I almost never include IP address in a report, but why that is for another article. However, this reminded me that I wanted to write an article on v_GS_NETWORK_ADAPTER_CONFIGURATION verse v_Network_DATA_Serialized. This article will talk about the difference and give you sample SQL queries that you can use.
Which v_GS_NETWORK_ADAPTER_CONFIGURATION verse v_Network_DATA_Serialized is better?
Well, that is a good question. The answer is not super simple but its not difficult either. The short answer will depend upon what you what to do with the details.
In Microsoft Configuration Manager (MCM / SCCM/ ConfigMgr), details such as IP addresses is store in many different SQL views. This can generate questions as to which is the “best” to use for ConfigMgr Reporting.
v_Network_DATA_Serialized
Normally the first things I would say only use the supported SQL view. Which is almost always the one that start with v_GS_*. But in this case BOTH views are fully supported by Microsoft. We can see that by looking at the permissions for the v_Network_DATA_Serialized SQL view. We see that it natively has the smsschm_users database role with Select permissions.
Next when you look at the design of the view you can see that it does “extra” processing to create one row for IPv4 and one row for IPv6. You might ask yourself well how much of a performance hit will I see? And I will say in most cases, you will NOT see any significate difference.
The last thing to consider is that this information is collected by hardware inventory. This is one of the reasons why many consultants/MVP recommend that hardware inventory be set to daily. See ConfigMgr Inventory Cycle Recommendations for more details.
For more details on using supported ConfigMgr SQL views, please see Why Is It Important to Use Supported SQL Server Views with SCCM Reporting? And What Are the Supported SQL Server Views to Use with SCCM Reporting?
IPv4 Addresses only
This SQL query will return the computer name, NIC Description and IPv4 address.
Select
RV.Netbios_Name0,
NA.Description0,
NDS.IPAddress0
from
dbo.v_R_System_Valid RV
join dbo.v_GS_NETWORK_ADAPTER NA on RV.ResourceID = NA.ResourceID
join dbo.v_Network_DATA_Serialized NDS on (RV.ResourceID = NDS.ResourceID) and (NA.DeviceID0 = NDS.Index0)
Where
NDS.IPEnabled0 = 1
and NDS.IPAddress0 not like '%:%'
Order by
RV.Netbios_Name0
This SQL query will return the computer name, NIC Description and IPv4 address.
v_GS_NETWORK_ADAPTER_CONFIGURATION
There is no extra processing BUT both IPv4 and IPv6 co-existing within the same column, thereby making it difficult to separate the IPv4 address from IPv6 address.
This SQL view is populated by Hardware Inventory too.
Both IPv4 and IPv6 Addresses
Select
RV.Netbios_Name0,
NA.Description0,
NAC.IPAddress0
from
dbo.v_R_System_Valid RV
join dbo.v_GS_NETWORK_ADAPTER NA on RV.ResourceID = NA.ResourceID
join dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC on (RV.ResourceID = NAC.ResourceID) and (NA.DeviceID0 = NAC.Index0)
Where
NAC.IPEnabled0 = 1
Order by
RV.Netbios_Name0
This SQL query will return the computer name, NIC Description and IPv4 & IPv6 addresses within one column but in different rows.
v_RA_System_IPAddresses
Bonus, but what about v_RA_System_IPAddresses SQL View? This one is based on Heartbeat discovery. This problem it has, is that you can’t tie the IP addresses to a particular NIC. You only know that one of the NICs is using that IP.
Heartbeat Discovery IP Query
Select
RV.Netbios_Name0,
IP.IP_Addresses0
from
dbo.v_R_System_Valid RV
join dbo.v_RA_System_IPAddresses IP on RV.ResourceID = IP.ResourceID
Order by
RV.Netbios_Name0,
IP.IP_Addresses0
This SQL query will return the computer name and IPv4 or IPv6 addresses but in different rows.
Summary
At the end of the day, it will depend on what you are trying to do as to which is better for you. e.g. if you only want IPv4 see v_Network_DATA_Serialized SQL view. If, on the other hand, you don’t care if both addressed exist, then use v_GS_NETWORK_ADAPTER_CONFIGURATION.
v_GS_NETWORK_ADAPTER_CONFIGURATION verse v_Network_DATA_Serialized
In this video, I will go show you the both SQL views in action.
I hope you found this useful comparing the two SQL views. If you would like more of these types of articles, make sure to touch base and let me know.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, if you have any questions, please feel free to touch base @Garthmj.