Keep watch on heavy software usage directly in production server

Keep watch on heavy software usage directly in production server

We might have SQL Server Management Studio (SSMS), Business Intelligence Development Studio (BIDS) or Full Visual Studio (VS) Installed in live production server but personally I prefer NOT TO USE it directly in production server especially in peak business hours because these are the heavy applications and uses lots of resources.

We might have given permission to few different people to access production server but If we don’t prefer anybody open these heavy applications in server, we have to have some kind of monitoring in server so that if anybody open these applications, we get an alert in form of email so that we can login to that server, find the person who is using it and notify him/her about not to use it.

I have one small .VBS script which I can run from the windows schedule task after every few minutes to check whether any among the restricted applications are running or not. If any of the applications, we have restricted, are running, that .VBS script send an alert message to me.

I have two servers to watch.

1. “Server1Name”

2. “Server2Name”

I am running this .VBS script for another server which is in same network of both above server. I always prefer to run these type of monitoring job, schedule task, packages from non-production server.

You have to replace server names in below given script and then you can set the schedule task for below given .VBS script.

[sourcecode language=”vb”]
Sub SendMail(Sender, Recipient, Subject, Message)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = Sender
objMessage.To = Recipient
objMessage.htmlbody = Message
objMessage.Send
End Sub

Dim objWMIService, objProcess, colProcess
Dim strMangementStudio
Dim strVisualStudio
Dim Count
Dim arrylist(1)
arrylist(0) = ("Server1Name")
arrylist(1) = ("Server2Name")
Count = 0

For Each strComputer in arrylist
On Error Resume Next

If strComputer = "Server1Name" Then
strMangementStudio = "ssms"
strVisualStudio = "devenv"
Else
strMangementStudio = "ssms"
strVisualStudio = "devenv"
End If

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

txt = "<table cellspacing=’1′ cellpadding=’0′ border=’0′ bordercolor=’black’ id=’shell’>"
txt = txt & ""
txt = txt & "
left: black thin solid; border-bottom: black thin solid’>Server</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’>Process ID</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’>Process Name</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’>Memory Usage(KB)</th>"
txt = txt & ""

Set colProcess = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process WHERE name LIKE ‘%" & strMangementStudio & "%’ or name LIKE ‘%" & strVisualStudio &
"%’")

For Each objProcess In colProcess

txt = txt & ""
txt = txt & "
left: black thin solid; border-bottom: black thin solid’><font size=’2′>" & strComputer & "</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’><font size=’2′>" & objProcess.ProcessId & "</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’><font size=’2′>"& objProcess.Name &"</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid’><font size=’2′>"& (objProcess.WorkingSetSize/1024)
&"</Font></td>"
txt = txt & ""

Count = 1
Next

txt = txt & ""
objWMIService = Nothing
colItems = Nothing
Next

If Count = 1 Then
SendMail "Info@SQLHub.com","ritesh@SQLHub.com","Microsoft Applications found opened at " & Now(), txt
End If
[/sourcecode]

Open notepad, save above given code in note pad with file name “Watch Heavy Software.VBS” and call this .VBS file from schedule task.

Do reply me back whether this script is useful or not. I would also like to know if there is any other way then this .VBS script or powershell script.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.