crribs.com

Exemplify Excellence : Its almost ironic how effective we can be in the last minutes of accomplishing something. The thing is, it was not the external world that brought out our efficacy, it was our perception of what needed to be done.

Quoted From: http://highestfaculties.wordpress.com/2009/04/26/defeating-student-syndrome-for-productivity/

Excellent writeup of a PHP app that interfaces with the Windows Task Scheduler.  I’m going to have to double check on his use of the Task Service object.  I thought it was only COM accessible in Task Scheduler 2.0 (the new version, that comes with Vista or Windows 7, or Server 2008).  I thought in XP (which included the Task Scheduler 1.0 – the schtasks.exe or the good old Scheduled Task’s folder) that either the Task Service COM interface had to be accessed with a lower level language, or possibly the Scheduled Task’s accessible via WMI (but not through the schtask folder interface).  Its possible thats the task scheduler this code uses for access.  If thats the reality, then you may not see all the tasks you may have entered using the GUI interface, using this tool.  More on that later.
http://codesnob.wordpress.com/2009/05/18/displaying-windows-task-scheduler-tasks-with-php/

This vbscript will retrieve all of the website’s profile numbers for your google analytics account.  Borrowed some code from http://mikaelspage.blogspot.com/2009/08/excel-functions-for-fetching-data.html

Changed a lot of code myself.  Removed some functions there may be remnants of them in there.  It does work though, and seems to be the only example on the web of doing this with VBScript.  Enjoy.

‘**********************************************************************
‘*  Get Google Analytics Profiles
‘************************************************************************

Call Main()

Sub Main()

email = “youremail@gmail.com”
password = “password”
token = getGAauthenticationToken(email, password)

getGAaccountData token, “False”

End Sub

Sub getGAaccountData(authToken, includeHeaders)

If authToken = “Authentication failed” Then
getGAprofiles = “Authentication failed”
WScript.Echo “Authentication failed”
Exit sub
End If
tempFile = “c:\googleAnalytics_Temp.txt”
Set fso = CreateObject(“scripting.filesystemobject”)
If fso.FileExists(tempfile) Then fso.DeleteFile(tempFile)
Set fileTemp = fso.OpenTextFile(tempFile, 8, True)

fileTemp.WriteLine chr(34) & “AccountName”“ , ”“ProfileNumber”“ , ”“ProfileTitle” & chr(34)

URL = “https://www.google.com/analytics/feeds/accounts/default”
Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)

objhttp.Open “GET”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.setRequestHeader “Authorization”, “GoogleLogin Auth=” & authToken
objhttp.send (“”)

gaResponse = objhttp.responseText

If InStr(1, gaResponse, “Token invalid”) > 0 Or InStr(1, gaResponse, “Authorization required”) > 0 Then
getGAprofiles = “Authentication failed”
Exit sub
End If

Set XMLdoc = CreateObject(“MSXML2.DOMDocument”)
XMLdoc.LoadXML(objhttp.responseText)
Set theRoot = XMLdoc.DocumentElement
Set children = theRoot.ChildNodes

rivi = 1
For Each child In children
If child.nodeName = “openSearch:totalResults” Then
riveja = CInt(child.Text)
If includeHeaders = True Then
ReDim TempArray(riveja, 2)
Else
ReDim TempArray(riveja, 2)
End If
End If

If child.nodeName = “entry” Then

Set children2 = child.ChildNodes
For Each child2 In children2
If child2.nodeName = “dxp:property” Then
Set attribuutit = child2.Attributes
If attribuutit.getNamedItem(“name”).Text = “ga:accountName” Then TempArray(rivi, 0) = attribuutit.getNamedItem(“value”).text

End If

If child2.nodeName = “dxp:tableId” Then TempArray(rivi, 2) = CDbl(Replace(child2.Text, “ga:”, “”))

If child2.nodeName = “title” Then TempArray(rivi, 1) = child2.text

Next
fileTemp.Write chr(34) & TempArray(rivi, 0)
fileTemp.Write chr(34) & “ , ” & chr(34) & TempArray(rivi, 2)
fileTemp.Write chr(34) & “ , ” & chr(34) & TempArray(rivi, 1) & chr(34) & vbnewline
rivi = rivi + 1
End If

If includeHeaders = True Then
TempArray(0, 0) = “AccountName”
TempArray(0, 1) = “ProfileTitle”
TempArray(0, 2) = “ProfileNumber”
End If

getGAprofiles = TempArray

Next

End sub

Function getGAauthenticationToken(email, password)
If email = “” Then
getGAauthenticationToken = “”
Exit Function
End If

If password = “” Then
getGAauthenticationToken = “Input password”
Exit Function
End If

CurChr = 1

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = “https://www.google.com/accounts/ClientLogin”
objhttp.Open “POST”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.send (“accountType=GOOGLE&Email=” & email & “&Passwd=” & password & “&service=analytics&Source=InternetEngineKPI”)

authResponse = objhttp.responseText

If InStr(1, authResponse, “BadAuthentication”) = 0 Then
authTokenStart = InStr(1, authResponse, “Auth=”) + 4
authToken = Right(authResponse, Len(authResponse) - authTokenStart)
getGAauthenticationToken = authToken
Else

DTSTaskExecResult_Failure

End If

If Err.Number <> 0 Then
DTSTaskExecResult_Failure
End If
End Function

Function LastDayOFPreviousMonth(aDate)
LastDayOFPreviousMonth = DateAdd(“d”, -1, DateSerial(Year(aDate), Month(aDate), 1))
End Function

Function FirstDayOFPreviousMonth(aDate)
If Month(aDate) > 1 Then
FirstDayOFPreviousMonth = DateSerial(Year(aDate), Month(aDate) - 1, 1)
Else
FirstDayOFPreviousMonth = DateSerial(Year(aDate) - 1, 12, 1)
End If
End Function

I know its ugly, but it does the job.  Whats that you say?  Youd like to programmatically pull your list of Profile Numbers?  Stay tuned and we’ll get er done.  What this script does, is it pulls down yesterdays number of visitors for whatever site you entire the “profilenum” of for that variable.  Dont forget to put your email and password in there too.  I know there are some scandanavian or Dutch variables in there.  I borrowed a decent chunk of this code (and changed a whole lot) from Mikael Thunberg’s VBA flavoring found at:  http://mikaelspage.blogspot.com/2009/08/excel-functions-for-fetching-data.html

There may be objects that sit unused or variables too.  I had to de-workify the code and was in a hurry.  Also, the previous version spit back all visits because it pulled all your profile numbers.  Because that remains fairly static, I pulled that function out of the code.  I may have left some remnants of it in there, though.

‘*******************************************************************************
‘*
‘*  Good Script to pull one time-frames worth of visits from google analytics
‘*  for a website with a known profilenum
‘*
‘*
‘*******************************************************************************

Set DataList = CreateObject(“System.Collections.ArrayList”)

Call main()

Sub main()

email = “emailaddress@gmail.com”
password = “password”
profilenum = “191919191″

token = getGAauthenticationToken(email, password)

‘arr = getGAdata(token, profilenum, ”visits”, Date - 7, Date - 1) ’visits for last week
arr = getGAdata(token, profilenum, “visits”, (Date – 1), (Date – 1), “”, “”, “”, “”, “”) ‘visits for previous day

WScript.Echo arr(1,1)

For Each strItem in DataList
Wscript.Echo strItem
Next

If Err.Number <> 0 Then WScript.Echo “ERROR - Main(): ” & Err.Description
End Sub

Function getGAauthenticationToken(email, password)
If email = “” Or password = “” Then
getGAauthenticationToken = “”
Exit Function
End If

CurChr = 1

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = “https://www.google.com/accounts/ClientLogin”
objhttp.Open “POST”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.send (“accountType=GOOGLE&Email=” & email & “&Passwd=” & password & “&service=analytics&Source=CrribsDotCom”)

authResponse = objhttp.responseText

If InStr(1, authResponse, “BadAuthentication”) = 0 Then
authTokenStart = InStr(1, authResponse, “Auth=”) + 4
authToken = Right(authResponse, Len(authResponse) - authTokenStart)
getGAauthenticationToken = authToken
Else

WScript.Echo “ERROR - getGAauthenticationToken()1: ” & Err.Description

End If

If Err.Number <> 0 Then
WScript.Echo “ERROR - getGAauthenticationToken()2: ” & Err.Description
End If
End Function

Function getGAdata(authToken, profileNumber, metrics, startDate, endDate, filters, dimensions, sort, includeHeaders, showArraySize)

If authToken = “Authentication failed” Then
ReDim TempArray(1 ,1)
TempArray(1, 1) = “Authentication failed”
getGAdata = TempArray
Exit Function
End If

If authToken = “” Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Authentication token missing”
getGAdata = TempArray
Exit Function
End If

If startDate > endDate Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Start date should be before end date”
getGAdata = TempArray
Exit Function
End If

startDateString = Year(startDate) & “-” & Right(“0″ & Month(startDate), 2) & “-” & Right(“0″ & Day(startDate), 2)
endDateString = Year(endDate) & “-” & Right(“0″ & Month(endDate), 2) & “-” & Right(“0″ & Day(endDate), 2)

URL = “https://www.google.com/analytics/feeds/data?ids=ga:” & profileNumber & “&start-date=” & startDateString & “&end-date=” & endDateString & “&max-results=10000″

If metrics <> “” Then
If Left(metrics, 3) <> “ga:” Then metrics = “ga:” & metrics
metrics = Replace(metrics, “&”, “&ga:”)
metrics = Replace(metrics, “&ga:ga:”, “&ga:”)

tempAns = “”

CurChr = 1

metrics = Replace(metrics, “ga%00″, “ga:”)
metrics = Replace(metrics, “%26″, “%2C”)

URL = URL & “&metrics=” & metrics
End If

If filters <> “” Then
If Left(filters, 3) <> “ga:” Then filters = “ga:” & filters
filters = Replace(filters, “;”, “;ga:”)
filters = Replace(filters, “;ga:ga:”, “;ga:”)
filters = Replace(filters, “,”, “,ga:”)
filters = Replace(filters, “,ga:ga:”, “,ga:”)

tempAns = “”
CurChr = 1

Do Until CurChr – 1 = Len(filters)
Select Case Asc(Mid(filters, CurChr, 1))
Case 37, 42, 44, 46, 48 > 57, 59, 65 > 90, 97 > 122, 126
tempAns = tempAns & Mid(filters, CurChr, 1)
Case 32
tempAns = tempAns & “%” & Hex(32)
Case Else
tempAns = tempAns & “%” & _
Format(Hex(Asc(Mid(filters, _
CurChr, 1))), “00″)
End Select

CurChr = CurChr + 1
Loop

filters = tempAns
filters = Replace(filters, “ga%00″, “ga:”)
filters = Replace(filters, “%26″, “%2C”)
URL = URL & “&filters=” & filters
End If

If dimensions <> “” Then
If Left(dimensions, 3) <> “ga:” Then dimensions = “ga:” & dimensions
dimensions = Replace(dimensions, “&”, “&ga:”)
dimensions = Replace(dimensions, “&ga:ga:”, “&ga:”)
tempAns = “”
CurChr = 1

Do Until CurChr – 1 = Len(dimensions)
Select Case Asc(Mid(dimensions, CurChr, 1))
Case 37, 48 > 57, 65 > 90, 97 > 122
tempAns = tempAns & Mid(dimensions, CurChr, 1)
Case 32
tempAns = tempAns & “%” & Hex(32)
Case Else
tempAns = tempAns & “%” & _
Format(Hex(Asc(Mid(dimensions, _
CurChr, 1))), “00″)
End Select
CurChr = CurChr + 1
Loop

dimensions = tempAns
dimensions = Replace(dimensions, “ga%00″, “ga:”)
dimensions = Replace(dimensions, “%26″, “%2C”)
URL = URL & “&dimensions=” & dimensions
End If

If sort = True Then URL = URL & “&sort=-” & metrics

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)

objhttp.Open “GET”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.setRequestHeader “Authorization”, “GoogleLogin Auth=” & authToken
objhttp.send (“”)

gaResponse = objhttp.responseText

If InStr(1, gaResponse, “Token invalid”) > 0 Or InStr(1, gaResponse, “Authorization required”) > 0 Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Authentication failed”
getGAdata = TempArray
Exit Function
End If

Set XMLdoc = CreateObject(“MSXML2.DOMDocument”)

XMLdoc.LoadXML (objhttp.responseText)

Set juuri = XMLdoc.DocumentElement
Set lapset = juuri.ChildNodes

For Each lapsi In lapset
If lapsi.nodeName = “openSearch:totalResults” Then
varRows = CDbl(lapsi.Text)
End If

If lapsi.nodeName = “entry” Then
Set lapset2 = lapsi.ChildNodes
For Each lapsi2 In lapset2
If lapsi2.nodeName = “dxp:dimension” Or lapsi2.nodeName = “dxp:metric” Then
varColumns = varColumns + 1
End If
Next
End If
Next

If varRows > 10000 Then varRows = 10000

If varRows = 0 And varColumns = 0 Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “No data found”
getGAdata = TempArray
Exit Function
End If

ReDim TempArray(varRows, varColumns)
If includeHeaders = True Then ReDim TempArray(varRows, varColumns)

If showArraySize = True Then
ReDim TempArray(1, 1)
TempArray(1, 1) = varColumns & “ columns * ” & varRows & “ rows”
If includeHeaders = True Then TempArray(1, 1) = TempArray(1, 1) & “ + header row”
getGAdata = TempArray
Exit Function
End If

varrow = 1

For Each lapsi In lapset
If lapsi.nodeName = “entry” Then
varcol = 1

Set lapset2 = lapsi.ChildNodes
For Each lapsi2 In lapset2
If lapsi2.nodeName = “dxp:dimension” Then
Set attribuutit = lapsi2.Attributes
TempArray(varrow, varcol) = Left(attribuutit.getNamedItem(“value”).Text, 255)
If varrow = 1 And includeHeaders = True Then TempArray(0, varcol) = attribuutit.getNamedItem(“name”).Text
varcol = varcol + 1
End If

If lapsi2.nodeName = “dxp:metric” Then
Set attribuutit = lapsi2.Attributes
If Not IsNumeric(attribuutit.getNamedItem(“value”).Text) Then
TempArray(varrow, varcol) = CDbl(Replace(attribuutit.getNamedItem(“value”).Text, “.”, “,”))
Else
TempArray(varrow, varcol) = CDbl(attribuutit.getNamedItem(“value”).Text)
End If

If varrow = 1 And includeHeaders = True Then TempArray(0, varcol) = attribuutit.getNamedItem(“name”).Text
varcol = varcol + 1
End If
Next
varrow = varrow + 1
End If
Next

getGAdata = TempArray

If Err.Number <> 0 Then
WScript.Echo “ERROR - getGAdata(): ” & Err.Description
End If
End Function

Have you ever needed to track the number of pages indexed daily for a site within Google? To save you some time, the following vbscript will grab the number of estimated pages Google returns as a single integer, the same as if you entered: site:crribs.com into the ‘Search’ box. The returned page gives you back – for example – Results 1 – 10 of about 171 from crribs.com. (0.19 seconds) . This script would return the number 171, for analytical/statistical tracking purposes. Make sure to enter the correct cannonical version of your site for the variable if youve specified one in Google Webmaster Tools.

varSite = "crribs.com"

WScript.Echo getGoogIndexedPages(varSite)

Function getGoogIndexedPages(strUrl)

End Function

I was thinking of using Twitter as a kind of ‘syslog’ for all of the scripts we have running (via the task scheduler) all over the enterprise. There is no centralized logging implemented. Ive made some attempts via text files, sql server, the event log, etc., but nothing has taken hold as – easy to implement, universal, and straightforward. This is the visual basic script I came up with to upload the messages.


'**************************************************************************
'* Scriptname: PostToTwitterSimple.vbs
'* TWITTER STATUS UPDATE
'* Brad Shultz - crribs.com
'**************************************************************************
strUsername = "username" 'Username
strPassword = "password" 'Password
strMessage = "This is a test twitter update from a vbscript." 'Message for twitter

Function SendToTwitter(strMessage, strUsername, strPassword)
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.open "POST", "http://twitter.com/statuses/update.xml", false, strUsername, strPassword
objHTTP.send "status=" & strMessage
SendToTwitter = objHTTP.responseText
Set objHTTP = nothing
End Function

In the spirit of sharing, and noticing that Red Gate Software’s SQL Prompt is up to $295 dollars for a single license, I’d like to offer this freeware/beta that Red Gate released to the public right after it acquired the SQL Prompt software from a company (who was giving it away for free).  This application brings intellisense/code completion to Sql Server- Management Studio, Query Analyzer, Editpad (I think), etc.  They realized that they were going to have to make substantial changes to the codebase in order to optimize it to their standards; so in the meantime, the current version of SQL Prompt was offered up for free.  I havent been able to find this anywhere else, so- here it is:  the original freeware version of Red Gate Software’s SQL Prompt.  Its a beta type version, but works very well as far as I’ve used it.  It requires no license because it was released as freeware.  Download it here.

Red Gate Software's SQL Prompt - the freeware edition; Intellisense for SQL Server!!!

http://www.mediafire.com/file/ejnazzdwtwi/SQLPromptSetup.msi

http://www.liberum.org/content/features.aspx

Is a neat free .asp help-desk solution. It could be customized pretty easily to an organization’s specific needs. I’ve found that help-desk software can be expensive and over-complicated for our sometimes very basic needs.

At work, a main component of my job involves daily maintenance of 200 or so scheduled vbscript’s that maintain servers, generate daily data feeds, process images, transfer files, and do the bulk of our ETL processing (retrieval, normalization, etc.).  Because many of the imports consist of relational files that we need to combine into one, file- an internal schema we use in an attempt to standardize loading into the db- we use the Microsoft text driver to query the text files (with the text driver, you can query delimited text files with a subset of SQL, so you can join multiple files together and aggregate relational data).

The text driver requires a file named “schema.ini” to be placed within the directory holding the files you want to query.  As you guessed, the schema.ini specifies the schema of the file.  You set values like character set, if the first row has headers or not, and the length/type of the headers.  But you are SOL if you have a .csv file with more than 255 columns.  I haven’t found any really good solutions yet (actually, I’ve used some pretty terrible ones).  I have actually resorted to firing off a DTS package in the middle of a script that trims off unneeded columns and outputs a new file, with less than 255 columns.  Short of looping through the whole .csv, splitting every line by the delimiter, and reassembling a new file based on the resulting array, I can’t even really think of any good solutions.  One promising lead is the free component for working with .csv files from Chilkat (http://www.example-code.com/vbscript/csv_read.asp – it can be downloaded for free on this page- the download link is the little peach-colored down arrow icon about two inches from the top of the page).

I’m thinking, though, that it may be time to ditch vbscript in favor of Powershell, which has an ‘import-csv’ commandlet.  It takes a .csv file in memory, and creates an object for every header item.  It works really well, in my limited testing.

Sometimes useful apps disappear from the web.  I was looking for this guy recently, and finally tracked down the installer on an old hard drive I have.  This application was offered as a freebie by Microsoft.  It’s basically the ‘Express’ version of VB5, intended to be used solely for creating ActiveX controls.  Its a great lightweight IDE if you find yourself needing to edit some pre-.net vb code, or, in fact, creating an ActiveX control.  Couldn’t find the sucker anywhere, so thought I would be charitable and make it available.  Cheers.

http://www.mediafire.com/?3zwy2onzwdw