Last week, a friend of mine asked me if there is a way to determine if an application is installed in a workstation. He was planning to deploy IBM Lotus Sametime Connect on their network but didn't know which workstations already have it. I already have a script which audits a workstation's hardware and software so I was thinking of using this ith a little modification. He has a list of workstations in their network and he wants to use this as a reference. Here's a script which reads the text file computerList.txt containing the hostnames of workstations in your network, tries to run a PING test to see if the workstation is reachable and, if it is, runs the script to check if the application is installed. All of these generates a result which is written to a text file in CSV format so that anybody can open it in Excel to generate reports. Management loves Excel.
One thing to note is that you can change the application name to anything you wish provided you know the complete application name as stored in your Add/Remove Programs or the registry. If you want to read the hostnames from your Active Directory infrastructure, check out the script written by Matthew Jenkins (I actually validated my script against his as it is always good to have your work checked). So Raymond, this post is for you. You no longer have to go thru all 2000+ workstations in your network
Dim loopCount, directory, objFSO,objFile,objFSO2,objFile2
'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(directory & "\computerList.txt", 1)
'===LOG of servers with successful PING
strFilePath = directory & "\Results.csv"
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
' Open the file for write access.
On Error Resume Next
Set objFile2 = objFSO2.OpenTextFile(strFilePath, 2, True, 0)
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "File " & strFilePath & " cannot be opened"
Set objFSO2 = Nothing
End If
On Error GoTo 0
'Write HEADER
objFile2.WriteLine "SERVER,PING STATUS, SOFTWARE INSTALLED"
'variable to search for a specified application
strApp="IBM Lotus Sametime Connect"
Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
If Reachable(strComputer)="Success" Then
intResult = SearchApp(strComputer, strApp)
If(intResult = 1) Then
strInstalled = "INSTALLED"
ElseIf(intResult = 2) Then
strInstalled = "NONE"
ElseIf(intResult = 3) Then
strInstalled = "UNABLE TO QUERY"
End If
objFile2.WriteLine strComputer & ",SUCCESS," & strInstalled
Else
objFile2.WriteLine strComputer & "," & Reachable(strComputer) & ",N/A"
End If
Loop
objFile.Close
Set objFSO =NOTHING
Set objFile = NOTHING
objFile2.Close
Set objFSO2 =NOTHING
Set objFile2 = NOTHING
MSGBOX "Finished"
'===================================
' Function SearchApp(strComputer, sApplication)
On Error Resume Next
' Initialize some variables first
SearchApp = 2
sProgramName = ""
sProgramVersion = ""
sKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" ' key containing uninstall info
' Attempt to connect to client's registry
Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
Set oReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
' Ensure we connected ok to the client, if not just return false, it's probably not a valid Windows box
If Err.Number <> 0 Then
SearchApp = 3
Exit Function
End If
' Enumerate client registry looking for application
oReg.EnumKey HKLM, sKeyPath, arrSubKeys ' get installed programs' subkeys
For Each subKey In arrSubKeys ' get info from each installed program subkey
' attempt to get DisplayName
If(oReg.GetStringValue(HKLM, sKeyPath & subKey, "DisplayName", sProgramName) <> 0) Then
' if no DisplayName try for QuietDisplayName
oReg.GetStringValue HKLM, sKeyPath & subKey, "QuietDisplayName", sProgramName
End If
' attempt to get DisplayVersion
If(oReg.GetStringValue(HKLM, sKeyPath & subKey, "DisplayVersion", sProgramVersion) <> 0) Then
' if no DisplayName try for QuietDisplayName
oReg.GetDWORDValue HKLM, sKeyPath & subKey, "VersionMajor", sProgramVersion
End If
' If the name exists, return true
If sProgramName = sApplication Then
SearchApp = 1
Exit Function
End If
Next
End Function
'===================
Function Reachable(strComputername)
Dim wmiQuery, objWMIService, objPing, objStatus
wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)
For Each objStatus in objPing
SELECT CASE objStatus.StatusCode
CASE 0
Reachable="Success"
CASE 11001
Reachable="Buffer Too Small"
CASE 11002
Reachable="Destination Net Unreachable"
CASE 11003
Reachable="Destination Host Unreachable"
CASE 11004
Reachable="Destination Protocol Unreachable"
CASE 11005
Reachable="Destination Port Unreachable"
CASE 11006
Reachable="No Resources"
CASE 11007
Reachable="Bad Option"
CASE 11008
Reachable="Hardware Error"
CASE 11009
Reachable="Packet Too Big"
CASE 11010
Reachable="Request Timed Out"
CASE 11011
Reachable="Bad Request"
CASE 11012
Reachable="Bad Route"
CASE 11013
Reachable="TimeToLive Expired Transit"
CASE 11014
Reachable="TimeToLive Expired Reassembly"
CASE 11015
Reachable="Parameter Problem"
CASE 11016
Reachable="Source Quench"
CASE 11017
Reachable="Option Too Big"
CASE 11018
Reachable="Bad Destination"
CASE 11032
Reachable="Negotiating IPSEC"
CASE 11050
Reachable="General Failure"
END SELECT
Next
End Function
Wednesday, December 19, 2007
Tuesday, December 18, 2007
Singapore User Groups Certification Campaign
If you are in Singapore and are interested to get certified, then, read on. Microsoft Singapore in cooperation with Microsoft Learning has offered this to members of the local Singapore user groups - Windows, Office, Windows SBS, SQL Server, DotNet. If you achieve your MCP or MCTS certification between December 1, 2007 and March 31, 2008, you can get your exam fees reimbursed, courtesy of Microsoft. Visit this site for more details
Saturday, December 15, 2007
Lazy DBA: Backup all your databases using a script
I have a compilation of scripts which I use on a daily basis to help me make my work a bit easier. I always mention during my presentations that the laziest people are administrators who happen to have a programming background. Here's one that I frequently use. A message on my instant messenger popped up asking me how to quickly perform backups of databases in SQL Server. I told him to write a BACKUP DATABASE command. Now, he was telling me about how much time it would take since they have like hundreds of databases. The solution: write a script. The script below performs a backup of all the databases on a SQL Server 2000 instance and dumps it in a local folder.
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''F\BACKUP\'+@DBNAME+'.BAK'' WITH INIT, STATS=10'
PRINT "==========================================="
EXEC (@SQL)
PRINT "Backup for database " + @DBNAME + " has been created"
SELECT @IDENT=min(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
END
This excludes the tempdb, Northwind and Pubs databases should you have it in your instance. Restoring is, of course, a different story. You need to start with the system databases (master and msdb, in my case) before you can restore the user databases. In a future blog post, I'll have a script to read the backups generated by this script and restore them all. See how lazy I can be?
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''F\BACKUP\'+@DBNAME+'.BAK'' WITH INIT, STATS=10'
PRINT "==========================================="
EXEC (@SQL)
PRINT "Backup for database " + @DBNAME + " has been created"
SELECT @IDENT=min(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
END
This excludes the tempdb, Northwind and Pubs databases should you have it in your instance. Restoring is, of course, a different story. You need to start with the system databases (master and msdb, in my case) before you can restore the user databases. In a future blog post, I'll have a script to read the backups generated by this script and restore them all. See how lazy I can be?
Thursday, December 13, 2007
Say hello to VMWare
What can I say, I had the opportunity to work on VMWare Workstation because of an not-for-resale (NFR) license provided for Microsoft MVPs. Having a Windows XP laptop with very limited RAM is a limitation I have to contend with everytime I do presentations. Imagine running a virtual image hosting a domain controller, DHCP, DNS, IIS, SQL Server 2005 and System Center Configuration Manager 2007 with at least two or three virtual clients. My laptop goes haywire after firing up all of my virtual images. I think I'll stick to VMWare Workstation while waiting for a more powerful laptop.
One more thing, I managed to configure my virtual network with a server and workstation to access the Internet thru NAT. Since I am not allowed to add workstations on our local network, I cannot use the host machine's network card to access the Internet. I configured the network card of the VMWare image to use NAT, assigned a static IP which is in the same subnet as the VMWare NAT address on my host and pointed its DNS to the same IP as well. This made my VMWare image access the Internet thru NAT using a static IP. Dynamic IP would be a lot easier as the VMWare DHCP will assign an IP to my image but that was not an option for me since I am working with a server, which needs a static IP. After making sure that my server can access the Internet, I configured routing and remote access on my virtual Windows Server 2003 so I route the traffic from my other subnet to the IP I used to access the NAT. This way, my clients can access the Internet but are only accessible thru my virtual network,meaning even my host machine cannot access my virtual clients. Quite cool, huh.
For my first test, I did my SQL Server 2008 Declarative Management Framework session for the Singapore SQL Server User Group all on VMWare Workstation. And the audience didn't even notice I was using a non-Microsoft product. I'll post the details of my session in a separate entry
One more thing, I managed to configure my virtual network with a server and workstation to access the Internet thru NAT. Since I am not allowed to add workstations on our local network, I cannot use the host machine's network card to access the Internet. I configured the network card of the VMWare image to use NAT, assigned a static IP which is in the same subnet as the VMWare NAT address on my host and pointed its DNS to the same IP as well. This made my VMWare image access the Internet thru NAT using a static IP. Dynamic IP would be a lot easier as the VMWare DHCP will assign an IP to my image but that was not an option for me since I am working with a server, which needs a static IP. After making sure that my server can access the Internet, I configured routing and remote access on my virtual Windows Server 2003 so I route the traffic from my other subnet to the IP I used to access the NAT. This way, my clients can access the Internet but are only accessible thru my virtual network,meaning even my host machine cannot access my virtual clients. Quite cool, huh.
For my first test, I did my SQL Server 2008 Declarative Management Framework session for the Singapore SQL Server User Group all on VMWare Workstation. And the audience didn't even notice I was using a non-Microsoft product. I'll post the details of my session in a separate entry
Subscribe to:
Posts (Atom)