Below is my code. I need to create some variables that I can put in my SQL query. They need to increment by 15 minutes each time the script runs....therefore pulling data every 15 minutes. Below is what I have so far. I'm just not sure how to do the incrementing each time the script runs?
Code:
Dim strCon
Dim SQLArray
Dim strTime
Dim stpTime
theDate = date()
newDate = DateAdd("D", -1, theDate)
strYear = datepart("yyyy", Now())
strMonth = datepart("m", Now())
strDay = datepart("d", Now())
strHour = datepart("h", Now())
strMin = "00"
strMin2 = "15"
strMin3 = "30"
strMin4 = "45"
strTime = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin
strTime2 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin2
strTime3 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin3
strTime4 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin4
strCon = "Data Source=<servername>:3203/SE001P;password=passwd;User ID=userid;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
'Has data
Set oRs = oCon.Execute("SELECT TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI'), count(*) FROM t_messaging_log lg WHERE lg.logtime > TO_DATE('"&strTime&"', 'YYYY-MM-DD HH24:MI') AND lg.logtime < TO_DATE('"&stpTime&"', 'YYYY-MM-DD HH24:MI') AND lg.FUNCTIONCATEGORY = 'AgentApp-heAppSubmissionDelegate' GROUP BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI') ORDER BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI')")
If oRs.BOF or oRs.EOF = "True" then
strEmpty = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;><TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR></TABLE>"
strResults = strEmpty & vbCr & "<H1><b>Sales Express ACAPS Submit Numbers - Zero in last 15 minutes/H1>"
sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
Wscript.quit
Else
SQLArray = oRs.GetRows(,,Array(CStr("TO_CHAR(LG.LOGTIME,'HH24')"),CStr("TO_CHAR(LG.LOGTIME,'MI')"),CStr("COUNT(*)")))
End If
'Begin writing array to variable
strBegin = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;>"
strHead = "<TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR>"
strRows = ""
for i = 0 to ubound(SQLArray,2)
strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
next
strResults = strBegin & strHead & strRows & "</TABLE>"
oCon.Close
Set oRs = Nothing
Set oCon = Nothing
'Send email notification function
function sendEmail (strTo, strCC, strFrom, strSubject, strBody)
set objEmail = CreateObject("CDO.Message")
objEmail.From = strFrom
objEmail.To = strTo
objEmail.CC = strCC
objEmail.Subject = strSubject
objEmail.HTMLBody = strBody
objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "smtpservername"
objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
objEmail.Configuration.Fields.Update
objEmail.Send
end function