Find Servers in a domain
-
Been struggling with this for a few hours now.
I know how to find all sql servers on a domain (osql -L) easy enough.
and I know how to find the version of sql server from a known server with the dos reg /query commands. But does anyone have any scripts to find both the server and version via either osql, isql or AD or anything?
See, my company has about 160 SQL servers (I'm new, this isn't my fault), many of which are msde or SQL express editions on workstations, and I need to find out which Server versions are on which version number. It's tricky but I just need a servername...version list.
-
It can be handled this way. Write a DOS program to do the following
1. use OSQL -L to list the servers/machines running sql server and move the list to a text file.
2. read the name one by one from the text file and find the version of the machine name read.
But ensure that you have access to all the sql servers placed in your work environment so that nothing fails. also try to handle errors as much as possible.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh,
Do you have any batch code that will actually do this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I currently don't have one. But sure that this can be written and used in huge environments.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Steve,
Would you post the "dos reg /query" code you're using to find the version? Then I'll see what I can do...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sure, I actually was able to do it all in dos (with osql)
but it is an ugly file. I didn't have time to pretty up the results
but here is the code to actually extract all the server names and grab
the sql version (note, basically if the registry key isn't there then this is
an msde version, clunky I know, but it works for my purposes). I still would
like to find a way to do this whole thing in SQL but without cursors (I simply
REFUSE to write a cursor query, just out of spite
), I couldn't find a wayto do it (although testing, I did find a query that I had almost working WITH a cursor)
2 step process:
1. osql -L out a text file of the names of all servers that report sql on the domain.
NOTE: I called this sqlenum.txt
2. create a batch file (in the same dir) with this code in it:
FOR /f "delims=|" %%a IN (sqlenum.txt) DO (
REG QUERY \\%%a\HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\ComputerName\ComputerName /V COMPUTERNAME >>\\[your local machine name]\c$\sql_machines\SQL_VER.TXT
REG QUERY \\%%a\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /V CSDVERSION >>\\[your local machine name]\c$\sql_machines\SQL_VER.TXT
ECHO '**************************' >> \\[your local machine name]\c$\sql_machines\SQL_VER.TXT
)
Code NOTE:
******Replace [your local machine name] with .......your local machine name.
NOTE: This will return the machine name and the sql version (if it is available)
NOTE1: the REG QUERY lines are suppose to be all one line but this forum doesn't allow me to do that, so make sure it's all one line in your text.
NOTE2: This query can take a little while as if the machine is offline it will need to do the whole 30 second timeout thing.
-
Note that servers can mark themselves as "hidden" from -L and if any routers block 1434, this won't work either.
If you have SMS or someone can run a query, the search for MSSQL% in the services list is a more accurate way.
-
Steve Jones,
I'm a bit ignorant here... how do you "run a query, the search for MSSQQL% in the services list ia a more accurate way"? Where is that "services List" you speak of? (SQL Server 2000 and 2005)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Steve Hare...
I'll see what I can hammer out but I'm also interested in what Steve Jones said about searching a "services list" for MSSQL%. I just need to find out what he's talking about because of the "hidden" thing he talked about.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think he means (this is a big guess), you can query the registry for services that match MSSQL%.
- It looks like this part of the question has been answered, but here is one way I check for server editions/versions.
- I have a file called ServerList.txt that contains all of the server names.
- I have also created a batch file that has the following in it. I execute this batch file at the command prompt.
FOR /F %%I in (C:\ServerList.txt) do isql -S %%I -E -w500 -r1 -h-1 -n -s"," -i VERSION_CHECK_SCRIPT.txt| findstr /V /r /c:"^$" >> C:\VERSION_RESULTS.log- VERSION_CHECK_SCRIPT.txt contains the following:
SET NOCOUNT ONUSE MASTER
SELECT SUBSTRING(@@SERVERNAME,1,40), SUBSTRING(@@VERSION,1,300)
The @@VERSION part will return a lot of unneccesary stuff, but this is the best I have been able to do considering I am hitting SQL Server 7.0 and 2000 with isql.- The output file will look like this (all on one line), except it is wrapping on the board:
SOMESERVERNAME ,Microsoft SQL Server 2000 - 8.00.818 (Intel X86)May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
-
Cat,
This is what i told before i just said the way to get it and you haev coded. Great. hats off Buddy.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Check this site for SQLRecon. It is a free sql server scanner.
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
-
I did try that tool once before, pretty good, but it does some pretty scary scans on the network and get's our Firewall Admin all in a tizzy.
I have to email him 10 minutes before I do one or he goes insane with all the port scans happening. Even in stealth mode it flags multiple intrusion/detection alerts on a network. -
I have been using SQLPing 3.0 that I found at the link below to discover servers, but it is GUI based, so it is hard to automate. It does do a good job of finding SQL Servers in an IP address range using about 8 different methods of discovery, and allows export of the information to an XML file. It has a lot of the information you are looking for, and it will even find SQL Server where it is not currently running.
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
You can use this script to parse the @@version global variable on each server to get the version information:
Parse @@VERSION
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84713
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply