netnerds.net » VBScript

25Jul/111

VBScript: When You Can’t Use Powershell to Ping a Webpage…

Sometimes, there's a need to schedule a "ping" to a webpage (that needs to be pre-compiled, for instance). While using PowerShell is the easiest way to do this, it's not always available on older server, while VBScript is nearly always a safe bet. The biggest issue with using VBScript or the command line is what to do with the window it may open. Instead of using iexplore and taskkill, using the following VBScript is my fav alternative.

Call pingPage ("http://www.domain.com")


Function pingPage (strURL)

	Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")


	objXMLHTTP.open "GET", strURL, false

	objXMLHTTP.send()


	If objXMLHTTP.Status <> 200 Then

	'email someone about it

	End if


	Set objXMLHTTP = Nothing

End Function

On a related note: the PowerShell line looks something like this: $pingPage = (new-object net.webclient).DownloadString("http://www.domain.com")

Posted by: Chrissy LeMaire Filed under: PowerShell, VBScript 1 Comment

31Jan/100

Accessing XML using the MSDAOSP Provider on Windows Server 2008

For quite some time now, I have been using a scheduled task that executes a VBS file on my web server in order to update local weather information in a database. After porting the site to a Windows 2008 server, the scheduled task began to fail. The script is a bit out of date since the implementation of PowerShell, but there is no reason to re-write this minor script when it usually works properly.

The VBS script does the following:

1. Access current weather conditions for a zip code using the Weather.com XML data feed.
2. Save the XML to a flat file in the local directory.
3. Using the MSDAOSP Provider, connect to the XML file and open it as a record set.
4. Transform the data appropriately and insert it into a table in SQL Server which holds the current forecast.
5. Repeat for each zip code I wish to load.

The first error I encountered was "Permission Denied" even though I was an Admin user. I quickly resolved this by deleting the XML file the script was trying to overwrite, because I had previously changed the user that the script executes as.

After that error, I began to receive another error with a code of 80040E21:

Line 65 is my MSDAOSP connection string, so I assumed it was a compatibility issue with the OS. Searching the Internet for a quick fix did not return desired results, since most matches for the connection string only demonstrated how to use it.

The solution turned out to be very simple. Earlier versions Microsoft Data Access Components have been deprecated , so the Connection string was in need of a version update.

In previous versions of Microsoft Windows Server, I was able to use the following connection string:
rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"

In Windows Server 2008, the connection string must be updated to the following:
rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;"

So for those of you who would like to see how to open the file using ADO, it is as follows:

    myXMLfile = "c:\scripts\weather.xml"

    set rs = createobject("adodb.recordset")

    rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;"

    rs.Open myXMLfile    'Open a recordset from the newly created file

For more information on the MSDAOSP provider and how to use it to access XML in ADO, visit this Microsoft Support Article.

Posted by: Brandon Abshire Filed under: VBScript No Comments

14Jan/090

Efficiently Compare Two SQL Server Tables Using Mostly VBScript

Recently, a friend of mine asked to write a script to compare two SQL Server tables using mostly VBScript. The very thought made me shudder; I wanted desperately to compare the tables in SQL Server but ultimately, the company's requirements prevented us from doing so. Here were the stipulations:

First, the script should be as resuable as possible. Many tables will be compared.
The column names are unknown -- only the database name and table were consistent.
The SQL Servers were 2005 and both located on the same domain.
Windows Authentication must be used.
SSIS/DTS cannot be used.
Files can be written to the hard drive, but only as a last resort.
OPENROWSET/OPENDATASOURCE/Linked Servers cannot be used.
The script's output must be either TRUE or FALSE. TRUE if the tables matched, FALSE if they didn't.
IDENTITY columns must be ignored.

I told him I'd be back in 20 minutes as my brain figured it out. This is what it came up with:

ADO's recordset getstring() will be used for direct comparisons. No variables will need to be assigned, no data will need to be written.
The INFORMATION_SCHEMA will be queried to discover column names. Column names will be reused by both SELECT and ORDER BY.
ORDER BY will be used to ensure the data is returned in the same order on both tables.
COLUMNPROPERTY will be used to check if the column is an identity (I know excluding the ID means that the table may not be exactly a match, but it's what was requested, so I obliged.)

So without further ADO

strServer1 = "server1"

strServer2 = "server2"

strDatabaseName = "mydatabase"

strTableName = "mytable"


CALL CompareTables(strServer1,strServer2,strDatabaseName,strTableName)


SUB CompareTables(server1,server2,databaseName,tableName)


'This is a SQL Server 2005 Connection String with Windows Authentication. Change as necessary.

connString1 = "Provider=SQLNCLI;Server=" & server1 & ";Database=" & databaseName & ";Integrated Security=SSPI;"

connString2 = "Provider=SQLNCLI;Server=" & server2 & ";Database=" & databaseName & ";Integrated Security=SSPI;"


'Since this is a template script, we don't know what the columns are so we'll use the INFORMATION_SCHEMA to figure it out.

'Not only will this be useful in the SELECT statement (SELECT col1, col2, col3) but also is necessary for the ORDER BY.

'Because we'll be using a text comparison, columns and order by must be  in the same exact order on both server1 and server2

strSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & tableName & "' AND (COLUMNPROPERTY(OBJECT_ID('" & tableName & "'),column_name,'IsIdentity')) != 1 ORDER BY ORDINAL_POSITION ASC"

SET rsColumns = CreateObject("adodb.recordset")

	rsColumns.Open strSQLColumns, connString1, 1,1

		IF rsColumns.eof and rsColumns.bof THEN

			Msgbox "The table has no columns or does not exist."

			rsColumns.close

			SET rsColumns = NOTHING

			Wscript.quit

		ELSE

			do until rsColumns.eof

				strColumns = strColumns & rsColumns("COLUMN_NAME") & ","

				rsColumns.movenext

				loop

				'Take off last comma

				strColumns = left(strColumns,len(strColumns)-1)

				rsColumns.close

				SET rsColumns = NOTHING

		END IF


'Ninja

strSQL = "SELECT " & strColumns & " from " & tableName & " ORDER BY " & strColumns & " asc"


SET rsserver1 = CreateObject("adodb.recordset")

	rsserver1.Open strSQL, Connstring1, 1,1

		IF rsserver1.eof and rsserver1.bof THEN

			Msgbox "No Rows to Compare in Table 1"

			rsserver1.close

			SET rsserver1 = NOTHING

			wscript.quit

		END IF


SET rsserver2 = CreateObject("adodb.recordset")

	rsserver2.Open strSQL, Connstring2, 1,1

		IF rsserver2.eof and rsserver2.bof THEN

			Msgbox "No Rows to Compare in Table 2"

			rsserver1.close

			SET rsserver1 = NOTHING

			rsserver2.close

			SET rsserver2 = NOTHING

			wscript.quit

		END IF


	IF rsserver1.GetString() = rsserver2.GetString() THEN

		Result = TRUE

		ELSE

		Result = FALSE

	END IF


	rsserver1.close

	SET rsserver1 = NOTHING

	rsserver2.close

	SET rsserver2 = NOTHING


	MsgBox Result


END SUB

Although VBScript is a favorite of mine, I'm a bit surprised that it's still a requirement on new project. Anyway, thanks for the distraction, friend. It was nice to get away from VM troubleshooting for a bit.

Posted by: Chrissy LeMaire Filed under: SQL Server, VBScript No Comments

29Dec/084

MSXML: Access is denied – 80070005 – MSXML4.dll

Recently, I wanted to see if my ex in DC was reading my blog so I ran my referer log against an outdated copy of the MaxMind GeoIP locator database. I figured the database could use some updating so I ran the ImportMaxMindGeoIP.vbs script I posted awhile back, and much like in real life, I received an "Access Denied" error.

Line 63 was basically a simple line that stated: objXMLHTTPindex.Send() Most of the access denied errors on the net referred to POSTing unencrypted data but the script was performing a GET. After about a half hour of troubleshooting, I realized that the URL my script was pointing to was returning an HTTP code of "301: Moved Permanently."

Once I plugged in the new updated URL, the script worked without a problem. So if you're receiving a similar error, be sure to check that the URL hasn't been moved. You can do this by using Sam Spade, Firefox Extensions or just loading up the URL in your browser and seeing if it's redirected to a new location.

As for knowing if the ex is reading my blog, the results were inconclusive. I guess I'll find out soon enough. I mean, I could just ask her, but what's the fun in that?

Posted by: Chrissy LeMaire Filed under: VBScript 4 Comments

11Apr/084

VBScript: Clean Up Imported SIM Card Contacts in Outlook

The other day, I was texting a friend and some guy looked at me and said "Oh hey, 2002 called, it wants its T9 back." Haha, slightly cliche but I laughed anyway. Then I went and bought myself a Blackberry.

I totally love it, I won't lie. So I imported my contacts into Outlook from a Sony Ericsson mobile phone and they came out kinda chu-chu. All the contacts came in as "Work" numbers and were all prefixed by ";\M". I cleaned the M's out manually but then wrote a script to list all the numbers as Mobile numbers. For consistency's sake, I also cleaned up all of the numbers themselves to just be, for instance, 2345551212. Here's the script in case you would like to do a mass cleanse as well.

Set objOutlook = CreateObject("Outlook.Application")

Set objNamespace = objOutlook.GetNamespace("MAPI")


Set colContacts = objNamespace.GetDefaultFolder(10).Items ' 10 = olFolderContacts


For Each objContact In colContacts

	if len(objContact.MobileTelephoneNumber) = 0 then

		objContact.MobileTelephoneNumber = objContact.BusinessTelephoneNumber

		objContact.BusinessTelephoneNumber = ""

		'Wscript.Echo objContact.FullName, objContact.BusinessTelephoneNumber, objContact.MobileTelephoneNumber

		objContact.Save

	end if


	if len(objContact.MobileTelephoneNumber) > 0 then

		mobileNumber = objContact.MobileTelephoneNumber

		mobileNumber = replace(mobileNumber," ","")

		mobileNumber = replace(mobileNumber,"-","")

		mobileNumber = replace(mobileNumber,"(","")

		mobileNumber = replace(mobileNumber,")","")

		mobileNumber = replace(mobileNumber,"+1","")


		if left(mobileNumber,1) = "1" then mobileNumber = right(mobileNumber,len(mobileNumber)-1)

		objContact.MobileTelephoneNumber = mobileNumber

		objContact.Save

	End if


Next

Ouu my grammar has gone downhill since I spent a weekend in Louisiana.

Posted by: Chrissy LeMaire Filed under: VBScript 4 Comments

3Jul/072

T-SQL Equivalent of VBScript's FormatDateTime Function

Looking for the T-SQL (somewhat) equivalent to VBScript's FormatDateTime function? I've been too, for years. I finally found it within the CONVERT() function. As stated in SQL Server Books Online:

In CONVERT ( data_type [ ( length ) ] , expression [ , style ] ), style is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Manuj Bahl wrote a nice article covering date and time manipulation in SQL Server 2000 and it in, he summarized BOL's table that lists the different style types. It went something like this:

Style ID	Style Type
0 or 100	mon dd yyyy hh:miam (or pm)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmam (or pm)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd thh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmam
131	dd/mm/yy hh:mi:ss:mmmam

Try this out with GETDATE() by running the following statement:
SELECT CONVERT(VARCHAR,GETDATE(),7) AS currentdate

Your results should look something like this: Jul 03, 07 Nice! Have fun

Posted by: Chrissy LeMaire Filed under: SQL Server, VBScript 2 Comments

11May/073

VBScript: Forward SQL Server Alerts/Windows Events to (Cingular) SMS

Someone recently asked why I don't use SQL Mail on my SQL Servers (2000 and below). The answer is: I don't want to install Outlook on my SQL Servers. Sure, there are ways around it but I prefer just using "net send" to alert the Operator (moi) then forwarding those messages on to my inbox and phone.

Although my employer offered me a Blackberry, I never really cared for them. The battery life is just too short and I don't like talking into something the size of my hand. So.. I use a regular phone that doesn't receive e-mails easily. It does, however, love to receive texts. Cingular even gave me an email address to go with my phone and this is what I use to Alert me if any of my jobs (Backups, Optimizations, etc) fail.

First, I created a DNS CNAME record to my workstation called DBAWorkstation. Then I created an operator named DBA Workstation. I like to keep things generic in the event that I leave or my workstation name changes. In the event that happens, there's just one change at the DNS level. So next, I set the jobs to alert this operator via net send if any jobs fail. Finally, I set up a script that runs all the time on my workstation which has IIS's SMTP installed locally:

'****************************************************************************

' This script created by Chrissy LeMaire (clemaire@gmail.com)

' Website: http://netnerds.net/

'

' NO WARRANTIES, etc.

'

' This script monitors Windows Events and forwards any Application Popups

' to an email address

'*****************************************************************************


' Push Event Viewer Alert

    Set objWMIService = GetObject("winmgmts:{(security)}!root/cimv2")

    Set eventSink = wscript.CreateObject("WbemScripting.SWbemSink", "EVSINK_")

    strWQL = "Select * from __InstanceCreationEvent where TargetInstance isa  'Win32_NTLogEvent' and TargetInstance.SourceName = 'Application Popup' and TargetInstance.EventCode = 26"

    objWMIService.ExecNotificationQueryAsync eventSink,strWQL


' Keep it going forever

While (True)

    Wscript.Sleep(1000)

Wend


Sub EVSINK_OnObjectReady(objObject, objAsyncContext)

'Here's where I do a whole lot of cleaning. Edit to your liking.

   strCleanedMsg = replace(objObject.TargetInstance.Message,"Application popup: Messenger Service  : ","")

   strCleanedMsg = replace(strCleanedMsg,"Message from ","")

   strCleanedMsg = replace(strCleanedMsg," to " & objObject.TargetInstance.ComputerName,"")

   strCleanedMsg = replace(strCleanedMsg," for DB Maintenance Plan 'DB Maintenance for All DBs'","")

   strCleanedMsg = replace(strCleanedMsg,"JOB RUN:","")

   strCleanedMsg = replace(strCleanedMsg," was run on "," failed around ")

   strCleanedMsg = Left(strCleanedMsg,InStr(strCleanedMsg,"DURATI")-2)

   If Len(strCleanedMsg) > 159 Then strCleanedMsg = Left(strCleanedMsg,159) ' SMS allows max 160 characters. I picked 159 just in case.


    Set objCDO = CreateObject("CDO.Message")

    objCDO.From = "SQL Alert <me@myemployer.com>"

    objCDO.To = "0000000000@cingularme.com" ' Phone number goes there. Cingular does the SMS magic

    objCDO.CC = "me@myemployer.com"

    objCDO.TextBody = strCleanedMsg

    objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1 'cdoSendUsingPort (1 = local, 2= remote, 3 = Exchange)

    objCDO.Configuration.Fields.Update

    objCDO.Send

    set objCDO = Nothing

End Sub

Run that script as a service and you're set with SMS/E-mail notifications in SQL Server 2000 and below. SQL Server 2005 can do the same thing..but e-mail is much more accessible within the service itself.

Posted by: Chrissy LeMaire Filed under: SQL Server, VBScript 3 Comments

12Feb/0729

Active Directory: E-mail Notification for Newly Added Users and Computers

Recently, someone asked if there was a way to be notified when servers have been added to an Active Directory domain. I looked around the Internet and it seems there's not a direct way to do this without some large software package. So in lieu of having an instant notification, I created a script that tallies up newly added user and computer accounts and emails the admin with all the info. This script is initially setup to be run once a day, but you can modify to whatever frequency you want.

I really expected this to take at most a couple hours to write but parsing through all the data turned out to take a heckofa lot of time. From converting the desired comparison date to UTC to parsing the AD attribute memberOf, I spent a good day working on this. I learned a lot, though and found this page which details the AD schema to be very helpful.

If you need anything more than this, you may want to consider an enterprise-type administration package such as Microsoft's MOM.

ADAddedUsersNComputers.vbs

'****************************************************************************

' This script created by Chrissy LeMaire (clemaire@gmail.com)

' Website: http://netnerds.net/

'

' ADAddedUsersNComputers.vbs

'

' This script Checks AD for any additions made to Users or Computers

' in the past 24 hours. The time interval to check can be changed below.

'

' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.

'*****************************************************************************


'Please modify these four settings

strSMTPServer = "myexchangeserver"

strEmailFrom = "Administrator  <me@mydomain.com>"

strEmailTo = "Administrator <me@mydomain.com>"


strTimeInUTC = CompareDateUTCConvert("h",-24) 'This is the same syntax as dateAdd(). The example will get new users/computers added in the past 24 hours.


'Unless you want to change the domain to check or the format of the emailed info, nothing below really needs to be modified.

On Error Resume Next

numPersonCount = 0

numComputerCount = 0


Set objAdRootDSE = GetObject("LDAP://RootDSE")

Set objRS = CreateObject("adodb.recordset")

	varConfigNC = objAdRootDSE.Get("defaultNamingContext")

	strConnstring = "Provider=ADsDSOObject"

	strWQL = "SELECT ADsPath FROM 'LDAP://" & varConfigNC & "' WHERE createTimeStamp > '" & strTimeInUTC & "' and (objectCategory = 'Person' or objectCategory = 'Computer')"


	objRS.Open strWQL, strConnstring

			Do until objRS.eof

				Set objADUserOrComputer = GetObject(objRS.Fields.Item(0))

				strObjectCategory = ParseDN(objADUserOrComputer.objectCategory)


				Select Case strObjectCategory

				Case "Person"

				numPersonCount = numPersonCount + 1

						If Len(objADUserOrComputer.displayName) > 0 Then strUserMsg = strUserMsg & vbCrLf & "displayName = " & objADUserOrComputer.displayName

						'strUserMsg = strUserMsg & vbCrLf & "distinguishedName = " & objADUserOrComputer.distinguishedName

						strUserMsg = strUserMsg & vbCrLf & "sAMAccountName = " & objADUserOrComputer.sAMAccountName

						strUserMsg = strUserMsg & vbCrLf & "sAMAccountType = " & SAMAccountTypetoName(objADUserOrComputer.sAMAccountType)

						strUserMsg = strUserMsg & vbCrLf & "whenChanged = " & objADUserOrComputer.whenChanged

						strUserMsg = strUserMsg & vbCrLf & "whenCreated = " & objADUserOrComputer.whenCreated

						strUserGroups = ParseMemberOf(objADUserOrComputer.memberOf,objADUserOrComputer.PrimaryGroupID)

						strUserMsg = strUserMsg & vbCrLf & "Member Of: " & strUserGroups

						If Len(objADUserOrComputer.userPrincipalName) > 0 Then strUserMsg = strUserMsg & vbCrLf & "userPrincipalName = " & objADUserOrComputer.userPrincipalName

						strUserMsg = strUserMsg & vbCrLf

				 Case "Computer"

				 numComputerCount = numComputerCount + 1

						strCompMsg = strCompMsg & vbCrLf & "dNSHostName = " & objADUserOrComputer.dNSHostName

						strCompMsg = strCompMsg & vbCrLf & "isCriticalSystemObject = " & objADUserOrComputer.isCriticalSystemObject

						strCompMsg = strCompMsg & vbCrLf & "operatingSystem = " & objADUserOrComputer.operatingSystem

						strCompMsg = strCompMsg & vbCrLf & "operatingSystemServicePack = " & objADUserOrComputer.operatingSystemServicePack

						strCompMsg = strCompMsg & vbCrLf & "operatingSystemVersion = " & objADUserOrComputer.operatingSystemVersion

						If InStr(objADUserOrComputer.rIDSetReferences,"Domain Controller") > 0 Then strCompMsg = strCompMsg & vbCrLf & "Domain Controller = Yes"

						If Len(objADUserOrComputer.description) > 0 Then strCompMsg = strCompMsg & vbCrLf & "description = " & objADUserOrComputer.description

					 	If Len(objADUserOrComputer.machineRole) > 0 Then strCompMsg = strCompMsg & vbCrLf & "machineRole = " & objADUserOrComputer.machineRole

						If Len(objADUserOrComputer.physicalLocationObject) > 0 Then strCompMsg = strCompMsg & vbCrLf & "physicalLocationObject = " & ParseDN(objADUserOrComputer.physicalLocationObject)

						strCompMsg = strCompMsg & vbCrLf

				 End Select

				objRS.movenext

				Set objADUserOrComputer = Nothing

			Loop

		objRS.close

Set objRS = Nothing

Set objAdRootDSE = Nothing


If Len(strUserMsg) > 0 Then strEmailMessage = strEmailMessage & "--------- USERS ---------" & vbCrLf & strUserMsg & vbCrLf

If Len(strCompMsg) > 0 Then strEmailMessage = strEmailMessage & "--------- COMPUTERS ---------" & vbCrLf & strCompMsg

If Len(strUserMsg) = 0 And Len(strCompMsg) = 0 Then strEmailMessage = "No users or computers have been added in the last 24 hours."


 Set objCDO = CreateObject("CDO.Message")

    objCDO.Subject = "Users Added: " & numPersonCount & ". Computers Added: " & numComputerCount & "."

    objCDO.From = strEmailFrom

    objCDO.To = strEmailTo

    objCDO.TextBody = strEmailMessage

    objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdoSendUsingPort (1 = local, 3 = Exchange)

    objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPServer

    objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    objCDO.Configuration.Fields.Update

    objCDO.Send

    set objCDO = Nothing


Function CompareDateUTCConvert(dateAddInterval,compareNumber)

'Wow, this is a lil complex. So createTimestamp is in UTC format.

'So first we grab your machine's time bias and then apply it.

'Next, we adjust the date to the one you specified above (now()-24hours by default)

'Finally, we parse the final date to UTC format ie. 20070207032200.0Z


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

	Set colTimeZone = objSWbemServices.ExecQuery("SELECT * FROM Win32_TimeZone")

		For Each objTimeZone in colTimeZone

		 strBias = objTimeZone.Bias

		Next

	Set colTimeZone = Nothing

Set objSWbemServices = Nothing

	strCompareDate = dateAdd(dateAddInterval,compareNumber,now())

	strUTCCompare = DateAdd("n",strBias*(-1),strCompareDate)

	CurrentUTC = Year(strUTCCompare) & Right("0" & Month(strUTCCompare),2) & Right("0" & Day(strUTCCompare),2)

	CurrentUTC = CurrentUTC & Right("0" & Hour(strUTCCompare),2) & Right("0" & Minute(strUTCCompare),2) & Right("0" & Second(strUTCCompare),2) & ".0Z"

	CompareDateUTCConvert = CurrentUTC

End Function


Function ParseDN(strDN)

'Take a DN and extract what we want then make it pretty.

	arrDN = split(strDN,",") 'CN=Example-Thing,CN=Whatever,CN=Etc

	strDN = right(arrDN(0),len(arrDN(0))-3) 'CN=Example-Thing -> Example-Thing

	strDN = replace(strDN,"-"," ") 'Example Thing

	ParseDN = strDN

End Function



Function ParseMemberOf(memberof,primarygroupid)

'This shows what groups a person belongs to.

'The output of memberof changes depending on

'how many groups the user is a member of, etc.

	Select Case TypeName(memberof)

		Case "String" ParseMemberOf = ParseDN(memberof)

		Case "Empty" ParseMemberOf = PrimaryGroupIDtoName(primarygroupid,varConfigNC)

		Case "Variant()"

			For each groupDN in memberof

				 strUserGroups = strUserGroups & vbCrLf & ParseDN(groupDN)

			Next

			ParseMemberOf = strUserGroups

		Case Else ParseMemberOf = "Unknown"

	End Select

End Function


Function SAMAccountTypetoName(theType)

'Just makin it more useful...

	Select Case theType

		Case 268435456 SAMAccountTypetoName = "Group Object"

		Case 268435457 SAMAccountTypetoName = "Non-Security Group Object"

		Case 536870912 SAMAccountTypetoName = "Alias Object"

		Case 536870913 SAMAccountTypetoName = "Non-Security Alias Object"

		Case 805306368 SAMAccountTypetoName = "Normal User Account"

		Case 805306369 SAMAccountTypetoName = "Machine Account"

		Case 805306370 SAMAccountTypetoName = "Trust Account"

		Case 1073741824 SAMAccountTypetoName = "App Basic Group"

		Case 1073741825 SAMAccountTypetoName = "App Query Group"

		Case 2147483647 SAMAccountTypetoName = "Account Type Max"

		Case Else SAMAccountTypetoName = "Unknown"

	End Select

End Function


Function PrimaryGroupIDtoName(PGID,varConfigNC)

'Ugh.. the alternative to this function can be found here:

'http://support.microsoft.com/kb/297951

'both are kinda nasty.

  Set objRSPGID = CreateObject("adodb.recordset")

    Connstring = "Provider=ADsDSOObject"

    strSQL = "<ldap://" & varConfigNC & ">;(objectCategory=group);distinguishedName,primaryGroupToken,name;subtree"

		 objRSPGID.Open strSQL, Connstring

      If not objRSPGID.eof and not objRSPGID.bof Then

      	Do until objRSPGID.eof Or Len(strGroupName) > 0

      	If PGID = objRSPGID("primaryGroupToken") Then strGroupName = objRSPGID("name")

     	  objRSPGID.movenext

    		Loop

      End If

     objRSPGID.close

  Set objRSPGID = Nothing

  If Len(strGroupName) = 0 Then strGroupName = "Unknown"

  PrimaryGroupIDtoName = 	strGroupName

End Function

To schedule this, save the above code as ADAddedUsersNComputers.vbs in C:\scripts (for ex.) and use Scheduled Tasks to run the following command: %windir%\system32\wscript.exe C:\scripts\ADAddedUsersNComputers.vbs. I suggest running it daily at the end of each workday.

Posted by: Chrissy LeMaire Filed under: Active Directory, VBScript 29 Comments

30Jan/072

VBScript & SQL: Programatically Find the Location of an IP Address

This is more of a post for proper keywords. My previous entry titled "Import MaxMind City CSVs into SQL Server 2005" assumes the user has already conducted research to find that MaxMind's free GeoIP database provides the functionality to look up the location of an IP address. So, if you wish to quickly and easily find an IP address' location, do the following.

1. Ensure you have SQL Server installed somewhere. If you don't currently have it installed,
    you can download SQL Server 2005 Express for free.
2. Read this post about importing the necessary (and free) database for IP lookups.
    a. Download zip & unzip
    b. Read README.txt
    c. Copy the unzip.exe to your Windows directory
    d. Run the included SQL File
    e. Run the included VBS file
3. Create a VBS file to access the information.
4. Double-click the VBS file and enter the IP address

LookupIPAddress.vbs

  strSQLServer = "localhost"

  strDBName = "maxmindGeoIP"


  strIPaddr = InputBox("Enter the IP address:","IP To Location")


  If Len(strIPaddr) = 0 Then Wscript.Quit


  Set objRS = CreateObject("ADODB.recordset")

  strConnstring =  "Driver={SQL Server};Server=" & strSQLServer & ";Database=" & strDBName & ";Trusted_Connection=Yes;"

  strSQL = "EXEC usp_IPtoLocation '" & strIPaddr & "'"

  objRS.Open strSQL, strConnstring, 1, 1


  If objRS.eof And objRS.bof Then

  	MsgBox "Something is ultra-broken. Is your database populated? " 'Everything should return at least something.

  Else

  	strLocation = objRS("location")

	  	If InStr(strLocation,"Unknown") > 0 Then

	  	MsgBox "The IP address, " & strIPaddr & ", cannot be found in the database  ",64,"IP To Location"

	  	Else

		  MsgBox  strIPaddr & " resolves to.." & vbCrLf & vbCrLf & objRS("location"),64,"IP To Location"

			End If

	 End If


 	Set objRS = Nothing

Then, finding the location is as easy as double-clicking the file, entering an IP, and pressing "OK"....

In less than 1 millisecond, (after the initial stored procedure execution), your results will show up

Posted by: Chrissy LeMaire Filed under: Networking, SQL Server, VBScript 2 Comments

24Jan/0782

VBScript: Download and Save a Binary File

Update: If you are an iTunes user that needs VBScript, this page won't help you. Please visit the following two sites for two different solutions: Apple Docs and KeathMilligan.net.

This is an old script I dug up which I call "fileFetch". It's a script that downloads a binary file using XMLHTTP and saves it using an ADO stream. This is handy for nightly downloads and will work within SQL Server DTS packages.

Dirty Code

' Set your settings

    strFileURL = "http://www.domain.com/file.zip"

    strHDLocation = "D:\file.zip"


' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")


    objXMLHTTP.open "GET", strFileURL, false

    objXMLHTTP.send()


		If objXMLHTTP.Status = 200 Then

			Set objADOStream = CreateObject("ADODB.Stream")

			objADOStream.Open

			objADOStream.Type = 1 'adTypeBinary


			objADOStream.Write objXMLHTTP.ResponseBody

			objADOStream.Position = 0    'Set the stream position to the start


			Set objFSO = Createobject("Scripting.FileSystemObject")

				If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation

			Set objFSO = Nothing


			objADOStream.SaveToFile strHDLocation

			objADOStream.Close

			Set objADOStream = Nothing

		End if


		Set objXMLHTTP = Nothing

Posted by: Chrissy LeMaire Filed under: VBScript 82 Comments

Older Entries »

About

Authors

Chrissy LeMaire

Tweet to @cl

Brandon Abshire

About

Authors

Categories