none
SCCM SQL Query - IP Address

    שאלה

  • 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

    יום שני 21 ספטמבר 2009 15:54

תשובות

  • 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
    • סומן כתשובה על-ידי Craig M Whelan יום רביעי 23 ספטמבר 2009 09:06
    יום רביעי 23 ספטמבר 2009 09:06

כל התגובות

  • 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/
    יום שני 21 ספטמבר 2009 20:20
    מנחה דיון
  • 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
    • הוצע כתשובה על-ידי Tom Watson יום שלישי 22 ספטמבר 2009 11:54
    יום שני 21 ספטמבר 2009 21:33
  • 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
    יום שלישי 22 ספטמבר 2009 10:41
  • 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
    יום שלישי 22 ספטמבר 2009 11:54
  • 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
    יום שלישי 22 ספטמבר 2009 12:40
  • 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
    יום שלישי 22 ספטמבר 2009 19:44
  • 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
    יום רביעי 23 ספטמבר 2009 08:17
  • 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
    • סומן כתשובה על-ידי Craig M Whelan יום רביעי 23 ספטמבר 2009 09:06
    יום רביעי 23 ספטמבר 2009 09:06
  • 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]

    • הוצע כתשובה על-ידי quadrant6 יום שלישי 05 דצמבר 2017 18:17
    יום חמישי 14 מאי 2015 13:34
  • using examples below i've created  next query to  get single, primary IP

    SELECT DNSHostName0 AS [NetBIOS Name], 
    CASE WHEN IPAddress0 like '%,%' THEN left(IPAddress0,CHARINDEX(',',IPAddress0)-1) 
    ELSE IPAddress0 END AS [IP Address]
    FROM         v_GS_NETWORK_ADAPTER_CONFIGUR    
    WHERE ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 not like 'fe%')
    and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 IS NOT NULL)
    and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 not like '169.254.%')
    and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].DefaultIPGateway0  IS NOT NULL)
    and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPEnabled0 = '1')

    יום שלישי 26 יוני 2018 07:57