Report/script for a range or ranges of IPAddresses (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting



Message

tbiggi -> Report/script for a range or ranges of IPAddresses (6/4/2008 11:31:25 AM)

I am looking for a way in SQL (T-SQL or W-SQL) to find clients in a specific range.  For instance, all clients (i.e. the network adapter configuration IPAddress) in a range of:

xx.xxx.xx.[113-126]

I need to do this because certain ranges are used by certain departments or business units, etc. (i.e. another unit might get the [127-190] addresses.  In addition, I will have hundreds or even thousands of these ranges that are assigned to a specific dept/unit and need to get all of those in a query or rules to include them in a collection or collections.

Ideas?

Thanks,
Tom



mseely -> RE: Report/script for a range or ranges of IPAddresses (6/4/2008 12:16:56 PM)

Probably not the most efficent way of doing it ....

Select * from v_network_adapter_configur
where ipaddress0 like '10.42.28.%'
and convert(int,right(ipaddress0,charindex('.',reverse(ipaddress0,1)-1)) between 113 and 126
and not ipaddress0 is null



tbiggi -> RE: Report/script for a range or ranges of IPAddresses (6/4/2008 12:41:11 PM)

I get an error message of "The reverse function requires 1 arguments".

Will this function work in WQL (i.e. a rule in a collection)?



mseely -> RE: Report/script for a range or ranges of IPAddresses (6/4/2008 1:50:13 PM)

Oops.
Forgot a ')' in there.  [One problem with testing on one machine, and then manually typing in the same text onto another machine]
Also - looks like it might not work in an IPv6 environment.

Select * from v_network_adapter_configur
where ipaddress0 like '10.42.28.%'
and convert(int,right(ipaddress0,charindex('.',reverse(ipaddress0),1)-1)) between 113 and 126
and not ipaddress0 is null

As is, wouldn't be able to use the query with WQL as a number of the functions used are not available in WQL.



tbiggi -> RE: Report/script for a range or ranges of IPAddresses (6/4/2008 1:53:14 PM)

Well, that was stated as part of the requirement ... it needs to work in WQL to be of any value within an SMS query or collection.  Unfortunately WQL is filled with many limitations which makes it a really big pain in SMS.



mseely -> RE: Report/script for a range or ranges of IPAddresses (6/4/2008 6:47:40 PM)

For WQL, one possibility would be:

SELECT * from SMS_R_SYSTEM inner join
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION  on
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.Resourceid =
SMS_R_SYSTEM.RESOURCEID where
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.1[0-4][0-9]%'

--which would give you everything from 10.20.30.100 to 10.20.30.149

to get 113-126, the last line would be changed to
(SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.11[3-9]%' OR SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.12[0-6]%')

while 127-190 would be
(SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.12[7-9]%' OR SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.1[3-8][0-9]%' OR
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.20.30.190%' )


Mark



jnelson993 -> RE: Report/script for a range or ranges of IPAddresses (6/5/2008 12:32:43 AM)

Oooh, I like that!  Clever.



tbiggi -> RE: Report/script for a range or ranges of IPAddresses (6/5/2008 2:36:17 AM)

I have been trying that all day but do not get the results needed which is why I opened this thread.  For instance,  if I use for the last octet '10.20.30.[241-254]%' and I still get everything - not just the values in the range of 241 to 253. This must have to do with the way SQL or WQL processes the IPAddress since it is a varchar and not int (integer).

Keep in mind I have thousands of entries for partial class C subnets ... trial and error for each of thousands of lines could take forever.



tbiggi -> RE: Report/script for a range or ranges of IPAddresses (6/5/2008 3:05:22 AM)

It looks like each 'number' in the octet in question needs to be treated individually.  For instance, if I need .49 thru .62 then I need to do:

like .[49%] or like .5[0-9] or like .6[0-2]  (the 49 could just be = since it is the last 'number' in 40-49)

And for get the groupings using parens 'cause they just get thrown away when you go back to the design view.



jnelson993 -> RE: Report/script for a range or ranges of IPAddresses (6/5/2008 3:51:37 AM)

It's important to understand the bracket wildcard's meaning.  These brackets let you specify

A range
[0-9] means any single character between 0 and 9 or
[a-g] means any single characters from a through g

A Set
[135] means a single character matching a 1,  3 or 5.
[adf] means a single character matching an a, d, or f.

And you can also put those brackets next to each other to do multiple ranges like [0-9][0-9] matches every number between 00 and 99.

So, when you have [241-254] in there...who knows the outcome...I'd guess it would treat it like a set and match on any single character matching 1, 2, 4, 5 or hyphen.

So if you're doing 241 - 254 you have to start with 241 and roll the 1's over like an odometer. 
LIKE .24[1-9]     -- 241 - 249
LIKE .25[0-4]     -- 250 - 254

As for the parenthesis groupings, depending on what else you have going on in your query, you may need to skip the query designer and finish it by hand.  When you start getting advanced with your queries, you have to stop relying on the query designer because it can hurt you more than it can help you and there's lots of stuff it can't do, but WQL can do. It's great for getting you started, but it can be the death of you when you're trying to just tweak something you had working.





tbiggi -> RE: Report/script for a range or ranges of IPAddresses (6/5/2008 7:16:00 AM)

I forgot that the set was single character - duh!

Thanks for the help folks.  Now for the VBScript to write out the T-SQL for about 3,000 individual entries ...



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.203125