Please excuse my lack of knowlege with regard to SQL, I've had a pretty good search around the forums and google and haven't found an answer to my problem, I'd be grateful for direction or an answer...

Using a SQL to extract data from the SMS SQL DB, the query takes data from v_RA_System_IPAddresses, specifically IPAddr.IP_Addresses0. This returns the IP address of the machine - which is good...however with a multihomed machine or cluster...multiple IP addresses are returned

Is there a way to ONLY get the primary IP address listed in the results...be it by query magic or using a specific table and data field?

Again, I apologise for my lack of SQL skills/poor terminology used

Many thanks in advance

No, there is no easy way to do this.

Even if you could , how would you determine what is the "primary" IP address?

John Nelson's blog illustrates how to put the IP addresses into a single line:-

http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/21/108484.aspx

But Garth's correct. It certainly isn't easy.

Quick question - why need the IP address in the report?

Thanks for the link - I'll check it out and see if it can give the result required, in a round-about way

The data results are used by another system, the IP address is being used a 'unique' reference...not my system, not my design...just picking up the pieces

Thanks again - I'll post back if the results are correct

I just wanted to do a quick check. I often get asked for reports to include IP address, and when I ask why, I invariably get told to help determine the location of the PC. My usual response is, "why not use AD Site for that?".

Anyway, good luck in your findings.

I think Garth also hit the nail on the head, there's no marker to indicate the primary address being used...I've now been given visibility of the data and also seen how the current query is outputting data

The current query when run in Stuido is showing a separate record for each unique hostname/ip combination...for example

MACHINE1 IPADDRESS1
MACHINE1 IPADDRESS2

When the data is exported during whatever process is being run, it's presented as the following

"MACHINE1" "IPADDRESS1, IPADDRESS2"


The IP addresses are allocated for management interfaces and also clustering interfaces. I think whoever wants this data may have to go back to the drawing board and find a different unique identifier for a system - there are plenty of them! Why they chose something that can occur multiple times on a machine I have no idea

Thanks for the help and assistance

I'm getting close with a solution - I'll post the code I've got when I get back into the office tomorrow but I've been using LEFT, LEN &PATINDEX to remove the last IP address, however I'm left with two addresses, just need to remove the last one and any commas left

I've been told the first address will suffice in the final outputteddata

I've managed to remove data containing 0. 10. and 172. addresses, so I'm pleased with my progress, especially as I'd never written a SQL query until this task!!

If anyone has any ideas...now or after I post the code tomorrow morning, I'll be very grateful

I guess it might be worth posting this query into a specific SQL forum too!

TIA

OK, here's the code I've got so far

SELECT LEFT(IPAddress0, LEN(IPAddress0) - PATINDEX('%,%', IPAddress0)) AS [IP Address], DNSHostName0 AS [Host Name]
FROM v_GS_NETWORK_ADAPTER_CONFIGUR
WHERE (IPAddress0 IS NOT NULL) AND (NOT (IPAddress0 LIKE '10%')) AND (NOT (IPAddress0 LIKE '172%')) AND (NOT (IPAddress0 LIKE '0%'))
ORDER BY [Host Name]



This removes all text from the right up to the first comma...a good start, is there a way then to work on data that's been outputted, this being the SCCM database I can't really write these results back to another table and work on them again from there

Again, thanks in advance for looking and any replies

This does EXACTLY what we need it to...

SELECT LEFT(IPAddress0, LEN('%.%.%.%.%.%.%') - patIndex(',', IPAddress0)) AS [IP Address], DNSHostName0 AS [Host Name]
FROM v_GS_NETWORK_ADAPTER_CONFIGUR
WHERE (IPAddress0 IS NOT NULL) AND (NOT (IPAddress0 LIKE '10%')) AND (NOT (IPAddress0 LIKE '172%')) AND (NOT (IPAddress0 LIKE '0%'))
ORDER BY [Host Name]


I don't think it looks pretty but it gets the job done...until the goalposts change of course!

Thanks for the pointers, really helped

• Marked as answer by Craig M Whelan Wednesday, September 23, 2009 9:06 AM

Garth is correct about the Primary IP, yet this query does have its uses.  I noticed some IPs had a trailing comma - so I fixed this with a REPLACE

SELECT REPLACE(LEFT(IPAddress0, LEN('%.%.%.%.%.%.%')  - patIndex(',', IPAddress0)),',','') AS [IP Address], DNSHostName0 AS [Host Name]

FROM v_GS_NETWORK_ADAPTER_CONFIGUR

WHERE   (IPAddress0 IS NOT NULL) AND (NOT (IPAddress0 LIKE '10%')) AND (NOT (IPAddress0 LIKE '172%'))

AND (NOT (IPAddress0 LIKE '0%')) AND (NOT (IPAddress0 LIKE '192.168%'))

ORDER BY [Host Name]