none
SCCM SQL Query - IP Address

    Question

  • 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

    Monday, September 21, 2009 3:54 PM

Answers

  • 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
    Wednesday, September 23, 2009 9:06 AM

All replies

  • No, there is no easy way to do this.

     

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

     


    http://www.enhansoft.com/
    Monday, September 21, 2009 8:20 PM
    Moderator
  • 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?

    Regards,
    Tom Watson,
    E-Mail: Tom_...@...
    Blog: http://myitforum.com/cs2/blogs/tom_watson
    • Proposed as answer by Tom Watson Tuesday, September 22, 2009 11:54 AM
    Monday, September 21, 2009 9:33 PM
  • 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
    Tuesday, September 22, 2009 10:41 AM
  • 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.
    Regards,
    Tom Watson,
    E-Mail: Tom_...@...
    Blog: http://myitforum.com/cs2/blogs/tom_watson
    Tuesday, September 22, 2009 11:54 AM
  • 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
    Tuesday, September 22, 2009 12:40 PM
  • 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 outputted data

    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
    Tuesday, September 22, 2009 7:44 PM
  • 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
    Wednesday, September 23, 2009 8:17 AM
  • 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
    Wednesday, September 23, 2009 9:06 AM