Showing posts with label Microsoft SQL Server. Show all posts
Showing posts with label Microsoft SQL Server. Show all posts

Wednesday, June 4, 2008

MSUagageReport.vbs: User report of network storage

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server
Description: This script report is run weekly and gives each network user a recap and detail of their personal network share usage. This report is run after the process 'x' has gotten the network detail into an MS SQL database, which simplifies access and reporting. Any errors are written to the application event log to help trouble shooting.
Sample Report:






Code:
'=========================================================================

' FILE: MSUagageReport.vbs

' AUTHOR: Robert Lawson

' COMPANY: Soka University of America

' DATE: 6/04/2008 Robert Lawson, Creation Date



' COMMENT:

' - Reports on information summared in SQL database by OneCard: MSrefresh

' - Server run on has to be allow SMTP relay on Exchange



'=========================================================================

option explicit

On Error Resume Next

const conScriptName = "MSUagageReport.vbs"

const conSendMail = FALSE ' TRUE = will send email, FALSE=will not

strCSSfile = "C:\data\vbscript\MSUagageReportCSS.html" ' Figure out to get from same dir as this script

' strCSSfile = "D:\Tech\MSUagageReportCSS.html" ' Figure out to get from same dir as this script

const conDEBUG = TRUE ' TRUE = SQL and displays, FALSE= full SQL and no displays

const conUDriveKey = 0 ' Key value for UDrive



Dim objShell, strMsg, intLoc

Dim strCSSfile, strHTML, strHTMLCSS, strTempFile

Dim objFile, fso, objFileTemp

Dim objConn, objRS, objRS2, objRS3, strSQL, strConn



' Data

Dim strSource, strType, strAsOf, strName, strEmail

Dim strID, strNETUser, strNameFirst, strNameLast, strNETEmail

Dim strDtlFile, strDtlFolder, strDtlDateCreate, strDtlLastAccess

Dim strTypelist, strTotCount, strTotSize

Dim numTotCount, numTotSize, numCutOffSize

Dim numPctUsed, numSize, numCount, numDtlSize, numQuota

Dim strPctUsed, strSize, strCount, strDtlSize, strQuota, strCutOffSize

Dim numSourceK, numUserK

Dim datAsOf, datDtlDateCreate, datDtlLastAccess

Dim datStartDate



' SMTP email

Dim strTo, strBCC, strCC, strFrom, strSubject, strTextBody

Const conMaxDetailLines = " Top 30 "

Const conDtlFolderMax = 34

Const conDtlFileMax = 23

Const conUdriveSource = 0 ' This is agreed upon (with myself) #



Const conFromUser = "MSNotification@campus.edu" ' valid email if you want user to reply

Const conEmailAdminUser = "robert.lawson@campus.edu" ' comma seperated

Const ForReading = 1 ' FSO



' ============================================================

' Setup

' ============================================================

Set objShell = CreateObject( "WScript.Shell" )



' Write event log that you started

strMsg = "BEGIN: " & conScriptName

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg



' DB stuff

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.Recordset")

Set objRS2 = CreateObject("ADODB.Recordset")

Set objRS3 = CreateObject("ADODB.Recordset")

strConn = "Provider=SQLOLEDB; SERVER=ServerName; DATABASE=DBname;Integrated Security = SSPI"

objConn.Open strConn



' ============================================================

' Load initial XHTML: CSS

' ============================================================

Set fso = CreateObject("Scripting.FileSystemObject")

Set objFile = fso.OpenTextFile(strCSSfile, ForReading)

if objFile > 0 then

strHTMLCSS = objFile.ReadAll

'if conDEBUG then Wscript.Echo "+objFile = " & strHTML

else

Wscript.Echo conScriptName & ". ERROR: Unable to open CSS file: " & strCSSfile

wscript.quit

end if



strTempFile = "C:\temp\MSUagageReport.html"

Set objFileTemp = fso.CreateTextFile(strTempFile, 2,TRUE)



' ============================================================

' Find out about the source

' ============================================================

strSQL = "SELECT * FROM MSsource WHERE SourceK = " & conUDriveSource

if conDEBUG then Wscript.Echo "strSQL = " & strSQL



objRS.Open strSQL, objConn, 2

if (objRS.BOF or objRS.EOF) then

strMsg = conScriptName & ": Unable to get source info: " & conUDriveSource

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

WScript.Quit

end if



numCutOffSize = objRS("CutOffSize")

objRS.Close



Select Case numCutOffSize

Case 1024 : strCutOffSize = "1KB"

Case 1048576 : strCutOffSize = "1MB"

Case 1073741824 : strCutOffSize = "1GB"

Case Else strCutOffSize = "?"

End Select



' ============================================================

' Get all users for a source, then detail and email'em

' ============================================================

strSQL = "SELECT * FROM MSuser WHERE SourceK = " & conUDriveSource

'strSQL = strSQL & " AND NETUser IN ('sleepy','dopey','sneezy')" '******** DEBUG ************

if conDEBUG then strSQL = strSQL & " AND NETUser IN ('sleepingb')"

if conDEBUG then strSQL = "SELECT * FROM MSuser WHERE SourceK = 3"

if conDEBUG then Wscript.Echo "strSQL = " & strSQL



objRS.Open strSQL, objConn, 2

if (objRS.BOF or objRS.EOF) then

strMsg = conScriptName & ": No MS users to process for source " & conUDriveSource

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

WScript.Quit

end if



strDateTime = Date & " " & Time

numUsers = 0



do while not objRS.EOF

strNETUser = objRS("NETUser")

strID = objRS("ID")

strSource = objRS("Source")

datStartDate = objRS("StartDate")

numSourceK = objRS("SourceK")

numUserK = objRS("UserK")

if conDEBUG then Wscript.Echo "--Working on NETUser = " & strNETUser

numUsers = numUsers + 1



strSQL = "SELECT * FROM OneCardMaster WHERE ID = '" & strID & "'"

if conDEBUG then Wscript.Echo strSQL

objRS2.Open strSQL, objConn, 2

if (objRS2.BOF or objRS2.EOF) then

strMsg = conScriptName & ": Unable to open OneCardMaster for ID " & strID

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

WScript.Quit

end if

strNameFirst = TRIM(objRS2("NameFirst"))

strNameLast = TRIM(objRS2("NameLast"))

strNETEmail = objRS2("NETEmail")

strNETUser = objRS2("NETuser")

objRS2.Close

' ============================================================

' Build header

' ============================================================

numSize = objRS("FileSpace")

numCount = objRS("FileCount")

numQuota = objRS("FileQuota")



' Quota and Pct Used

if numQuota = -1 then

strQuota = "Unlimited"

strPctUsed = "_"

elseif numQuota = -2 then

strQuota = "Not Set"

strPctUsed = "_"

Else

strQuota = pad(FormatNumber(numQuota,0,false,false,true),"r",10)

if numSize <> -1 then

numPctUsed = (numSize/numQuota)

strPctUsed = FormatPercent(numPctUsed,0,false,false,true)

end if

end if



if numSize <> -1 then

strSize = pad(FormatNumber(numSize,0,true,false,true),"r",10)

else

strSize = "???/Error"

end if



' Create header

strName = strNameFirst & " " & strNameLast

strEmail = strNETEmail

'strEmail = "robert.lawson@uni.edu" ' ***** DEBUG



strCount = pad(FormatNumber(numCount,0,true,false,true),"r",10)

strAsOf = FormatDateTime(datStartDate, vbShortDate)



strHTML = strHTMLCSS ' Initialize

strHTML = strHTML & "

strHTML = strHTML & "

strHTML = strHTML & "

"

strHTML = strHTML & "strHTML = strHTML & ""

strHTML = strHTML & "strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & "
strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "
" & strPctUsed & "" & strSize & "" & strQuota & "" & strCount & "" & strSource & "" & strAsOf & "
"



' ============================================================

' Build detail lines

' ============================================================

strSQL = "SELECT " & conMaxDetailLines & _

" * FROM MSUserFile WHERE SourceK = " & numSourceK & " AND UserK = " & numUserK & _

" ORDER BY Size DESC"



if conDEBUG then Wscript.Echo "strSQL = " & strSQL

objRS3.Open strSQL, objConn, 2



strHTML = strHTML & "

strHTML = strHTML & "

"

strHTML = strHTML & "strHTML = strHTML & ""

numCount = 0
do while not objRS3.EOF
numCount = numCount + 1

numDtlSize = objRS3("Size")
if numDtlSize > 0 then numDtlSize = (numDtlSize / 1024) ' KB to MB
strDtlFile = objRS3("FileName") ' conDtlFileMax
if LEN(strDtlFile) > conDtlFileMax THEN
' strDtlFile = LEFT(strDtlFile,10) & "..." & RIGHT(strDtlFile,(conDtlFileMax - 13)) ***************
End if
strDtlFolder = objRS3("Folder") ' conDtlFolderMax
strDtlFolder = REPLACE(strDtlFolder, strSource,"")
if LEN(strDtlFolder) > conDtlFolderMax THEN
' strDtlFolder = "..." & RIGHT(strDtlFolder,(conDtlFolderMax - 3)) *****************
End if
datDtlDateCreate = objRS3("CreateDate")
datDtlLastAccess = objRS3("LastAccess")
strDtlSize = pad(FormatNumber(numDtlSize,0,false,false,true),"r",7)

strDtlLastAccess = FormatDateTime(datDtlLastAccess, vbShortDate)
strDtlDateCreate = FormatDateTime(datDtlDateCreate, vbShortDate)
'strDtlFolder = pad(strDtlFolder, "l", conDtlFolderMax) **********************
'strDtlFile = pad(strDtlFile, "l", conDtlFileMax) ********************

strHTML = strHTML & "strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""



objRS3.movenext

Loop ' Detail lines

strHTML = strHTML & "
strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "
" & strDtlSize & "" & strDtlFile & "" & strDtlFolder & "" & strDtlDateCreate & "" & strDtlLastAccess & "
"

objRS3.Close



' ============================================================

' File Extension Recep

' ============================================================

strSQL = "SELECT * FROM MSUserFileExtSum " & _

" WHERE SourceK = " & numSourceK & " AND UserK = " & numUserK & _

" ORDER BY TotSize DESC"



if conDEBUG then Wscript.Echo "strSQL = " & strSQL

objRS3.Open strSQL, objConn, 2



strHTML = strHTML & "

strHTML = strHTML & "

"

strHTML = strHTML & "strHTML = strHTML & ""

numCount = 0
' Type,Typelist,TotCount,TotSize
do while not objRS3.EOF
numCount = numCount + 1

strType = objRS3("Type")
strTypelist = objRS3("Typelist")
numTotCount = objRS3("TotCount")
numTotSize = objRS3("TotSize")

strTotCount = pad(FormatNumber(numTotCount,0,false,false,true),"r",7)
strTotSize = pad(FormatNumber(numTotSize,0,false,false,true),"r",7)

strHTML = strHTML & "strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""

strHTML = strHTML & ""



objRS3.movenext

Loop ' File type

strHTML = strHTML & "
strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "strHTML = strHTML & "
" & strType & "" & strTotSize & "" & numTotCount & "" & strTypelist & "
"

objRS3.Close



' Disclaimer stuff

strHTML = strHTML & "

strHTML = strHTML & "This report is a recap of your Soka Udrive account, intended for your personal use and review. The detail of your largest files, sorted by size, is to help you keep tabs on 'the big ones'. Note that only files larger than " & strCutOffSize & " are considered in count and detail report. Files and folders too large to display will be abbreviated with a set of periods. If you have any questions or concerens, please call the IT Help Desk at 949.480.6666 or send an email to helpdesk@uni.edu."

strHTML = strHTML & "



" & strDateTime & "; " & conScriptName & "

"



strHTML = strHTML & "" ' *** END



' ============================================================

' Create email

' ============================================================

if numCount > 0 then

strTo = strEmail

if conDEBUG then strBCC = conEmailAdminUser

strCC = ""

strFrom = conFromUser

strSubject = "U Drive Summary" & conMaxDetailLines & "" & strNETUser

else ' You had summary record with no supporting detail

strTo = conEmailAdminUser

strBCC = ""

strCC = ""

strFrom = conFromUser

strSubject = "Failure to Email User:" & strNETUser & ", No supporting detal"

end if

if conDEBUG then Wscript.Echo strHTML



' Set the message properties.

if conSendMail then

Call SendMail(strFrom,strTo,strCC,strBCC,strSubject,strHTML,TRUE)

strMsg = conScriptName & ": Mailed: " & strName & " @ " & strSubject & _

"; Size: " & strSize & "; Limit: " & strLimit & "; Count: " & strCount

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

end if



objRS.movenext

Loop ' All users



strMsg = conScriptName & ": end execution. Total emails sent = " & numUsers

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg



' ============================================================

' The End

' ============================================================

strMsg = "END: " & conScriptName

if conDEBUG then

Wscript.Echo strMsg

objFileTemp.Write(strHTML)

End if

objShell.LogEvent 0,strMsg



objRS.Close

objConn.Close

Set objFile = nothing

Set objFile = nothing



' ======================================================

Function DoError(strErrMsg)

On Error Resume Next

Dim objShell



if conDEBUG then wscript.Echo strErrMsg



Set objShell=CreateObject("wscript.shell")

objShell.LogEvent 1,strErrMsg

End Function



' ======================================================

Function pad(strString, strWay, numLen)

' Padd out a passed string

' strWay "r" right, "l" left pad

' numLen overall size you want

Dim numLenVar, numDelta



numLenVar = LEN(strString)

if numLenVar > numLen then

pad = LEFT(strString,numLen)

Exit Function

elseif numLenVar = numLen then

pad = strString

Exit Function

end if



numDelta = numLen - numLenVar

if strWay = "l" then

pad = strString & String(numDelta, " ")

else ' "r"

pad = string(numDelta," ") & strString

end if



End Function



' ==================================================

' SUB: SendMail

' ===================================================

Sub SendMail(sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, bolHTML)

' FILE: SampleEmail.vbs

' PURPOSE: Send SMTP email, html or text, supporting Asian Languages (UTF-8)

' AUTHOR: Robert Lawson

' COMPANY: Soka University of America

' DATE: 08-May-2008 Robert Lawson, Creation Date

' From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45670'

on error resume next



Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"

Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"

dim cdoMessage, cdoConfiguration



Set cdoConfiguration = CreateObject ("CDO.Configuration")

With cdoConfiguration

.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.uni.edu"

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

.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

.Fields.Update

End With



Set cdoMessage = CreateObject("CDO.Message")

With cdoMessage

' Update the CDOSYS Configuration (don't make 1 line!!!)

SET .Configuration = cdoConfiguration

.BodyPart.charset = "unicode-1-1-utf-8"

.Fields.Update

.From = sFromAddress

.ReplyTo = sFromAddress

.To = sToAddress

.Cc = sCcAddress

.Bcc = sBccAddress

.Subject = sSubject

if bolHTML then

.HTMLBody = sBody

else

.Textbody = sBody

end if

.Send

End With



Set cdoMessage = Nothing

Set cdoConfiguration = Nothing

End Sub

Friday, April 11, 2008

Camp9PT849DBrefresh.sql: PS CS database refresh

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft
Description: This script updates PeopleSoft Campus Solution's v9 database all the steps to make it operational, point to 'test' instances, update security and clean out working tables.

Code:
/*
File: Camp9PT849DBrefresh.sql
Update : 11-Apr-2008/Robert Lawson
Purpose: Post db updates for Campus Solutions 9/PT 8.49

*/
-- Fix database users
exec sp_change_users_login 'update_one','folks','folks'
exec sp_change_users_login 'update_one','Angel_Access','Angel_Access'
exec sp_change_users_login 'update_one','db-user','db-user'

-- \scripts\grant.sql
GRANT SELECT ON PSSTATUS TO folks
GRANT SELECT ON PSACCESSPRFL TO folks
GRANT SELECT ON PSOPRDEFN TO folks


-- Disable student accounts
UPDATE PSOPRDEFN SET ACCTLOCK = 1
WHERE OPRCLASS = 'STUDENT'

-- Clear references to reports
DELETE FROM PS_CDM_LIST WHERE DISTNODENAME <> 'LSTEST'

-- QA: App Messaging
DELETE FROM PSAPMSGDOMSTAT
DELETE FROM PSAPMSGDSPSTAT

-- IB: Local Gateway
UPDATE PSGATEWAY SET
CONNURL = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE CONNGATEWAYID = 'LOCAL'

-- IB: Nodes
UPDATE PSNODECONPROP SET
PROPVALUE = 'http://ff1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_EP'

UPDATE PSNODECONPROP SET
PROPVALUE = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_LS'

-- IB: Connectors
UPDATE PSCONNPROP SET
PROPVALUE = 'http://ff1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'FINANCIAL'

UPDATE PSCONNPROP SET
PROPVALUE = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'CAMPUS'

-- Report Distribution
UPDATE PS_SERVERDEFN SET DISTNODENAME = 'LSTEST ' WHERE SERVERNAME = 'PSNT '

-- Business Interlink (Credit Card Processing)
UPDATE PSIOSETTINGS SET
IOVALUE = REPLACE(IOVALUE,'LSPROD','LSTEST')
WHERE IONAME = 'CREDITCARD_TRANSACTION' AND
IOSETTINGNAME = 'properties_file'

-- REN server (rebuilt by App Server)
DELETE FROM PSMCFRENURLID
DELETE FROM PSREN

-- Change Assistant
UPDATE PSOPTIONS SET
SHORTNAME = 'LSTEST',
LONGNAME = 'LSTEST',
GUID = '',
SYSTEMTYPE = 'STS'

-- Security
INSERT INTO PSROLEUSER
(ROLEUSER, ROLENAME,DYNAMIC_SW) VALUES
('cgreen', 'UPG_STDN_REC','N')
INSERT INTO PSROLEUSER
(ROLEUSER, ROLENAME,DYNAMIC_SW) VALUES
('cgreen', 'UPG_STDTNFIN','N')

-- appmsgpurgeall.dms (remove log statement; clears app messages)
-- core tables:
DELETE FROM PSAPMSGPUBHDR;
DELETE FROM PSAPMSGPUBDATA;
DELETE FROM PSAPMSGPUBCON;
DELETE FROM PSAPMSGSUBCON;
DELETE FROM PSAPMSGPUBERR;
DELETE FROM PSAPMSGPUBERRP;
DELETE FROM PSAPMSGPUBCERR;
DELETE FROM PSAPMSGPUBCERRP;
DELETE FROM PSAPMSGSUBCERR;
DELETE FROM PSAPMSGSUBCERRP;
DELETE FROM PSAPMSGPCONDATA;
DELETE FROM PSAPMSGSCONDATA;
DELETE FROM PSIBERR;
DELETE FROM PSIBERRP;

-- synchronous core tables:
DELETE FROM PSIBLOGHDR;
DELETE FROM PSIBLOGDATA;
DELETE FROM PSIBLOGERR;
DELETE FROM PSIBLOGERRP;
DELETE FROM PSIBLOGIBINFO;

-- archive tables:
DELETE FROM PSAPMSGARCHPH;
DELETE FROM PSAPMSGARCHPD;
DELETE FROM PSAPMSGARCHPC;
DELETE FROM PSAPMSGARCHSC;
DELETE FROM PSAPMSGARCHPT;
DELETE FROM PSAPMSGARCHST;
DELETE FROM PSIBLOGHDRARCH;
DELETE FROM PSIBLOGDATAARCH;
DELETE FROM PSIBLOGIBINFOAR;

Monday, August 7, 2006

EASUsageAudit.vbs: User weekly mailbox recap

Author: Robert Lawson
Environment: Windows Server, Exchange, EAS, vb-script
Description: This script runs weekly and recaps a users email EAS detail. It is meant to give them a quick snap-shot of their account and show them their largest email attachments. This report is meant to help make mailbox's self-managed, and works in conjunction with nightly quota notification: EASnearquotaemail.vbs.
Features:

- Cascading Style Sheet
- HTML constructed
- DEBUG feature
- Errors write to application event log
- Database driven using ADO

Sample Report:





Code Listing

'=========================================================================
' FILE: EASUsageAudit.vbs
' AUTHOR: Robert Lawson
' COMPANY: Soka University of America
' DATE: 6/01/2006 Robert Lawson, Creation Date
' 11/29/2006 Robert Lawson General update ready for production
' 12/13/2006 Robert Lawson Added email send/receive size
' 12/21/2006 Robert Lawson Converted to XHTML
' 11/12/2007 Robert Lawson Added VIEW_STORAGE_BY_USER_REF2 for # and usage,
' this is what is used to enforce quota.
' 04/09/2008 Robert Lawson Skip if error 438 for send mail
' COMMENT: Audit EAS Usage and notification
' - Relies heavily t-sql views: VWEASUserQuotaUsage and VWEASUserTopMsgs
' - Server run on has to be allow SMTP relay on Exchange

'=========================================================================
option explicit
On Error Resume Next
const conScriptName = "EASUsageAudit.vbs"
const conSendMail = FALSE ' TRUE = will send email, FALSE=will not
strCSSfile = "C:\data\vbscript\EASUsageAuditCSS.html"
const conDEBUG = TRUE ' TRUE = SQL and displays, FALSE= full SQL and no displays

Dim objShell, strMsg, intLoc
Dim strCSSfile, strHTML, strHTMLCSS
Dim objFile, fso
Dim objConn, objRS, objRS2, objRS3, strSQL, strConn
Dim numQuota, numMsgSize, numCount, numUsers, numPctUsed, numSendSize, numReceiveSize
Dim numREF_COUNT, numUNCOMPRESSEDSIZESUM, numCOMPRESSEDSIZESUM
Dim strName, strEmail, strSize, strLimit, strCount
Dim strDateTime, strMailDate, strMailSize, strMailFrom, strMailSubject, strMailFolder
Dim strMailBody, strUSERID, strDetail, strQuotaDesc, strPctUsed, strSendSize, strReceiveSize
Dim iMsg, iConf, Flds
Dim strTo, strBCC, strCC, strFrom, strSubject, strTextBody

Const conEASQuotaUnlimited = -1
Const conMaxExchangeAttachment = 10 ' MB
Const conMaxEmailDetail = " Top 20 "
' Do not set your smtp server information here.
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _
cdoSendUsingPort = 2, _
cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const conSMTPserver = "smtp.campus.edu"
Const conFromUser = "EmailNotification@campus.edu" ' valid email if you want user to reply
Const conEmailAdminUser = "robert.lawson@campus.edu" ' comma seperated
Const ForReading = 1 ' FSO

numSendSize = 20 ' MB, ****** get from Exchange, when time
numReceiveSize = 20 ' MB, ****** get from Exchange, when time
' ============================================================
' Setup
' ============================================================
Set objShell = CreateObject( "WScript.Shell" )

' Write event log that you started
strMsg = "BEGIN: " & conScriptName
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg

' DB stuff
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Set objRS2 = CreateObject("ADODB.Recordset")
Set objRS3 = CreateObject("ADODB.Recordset")
strConn = "Provider=SQLOLEDB; SERVER=ServerName; DATABASE=dbName;Integrated Security = SSPI"
objConn.Open strConn

' Email stuff (SMTP)
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Fields.Item(cdoSendUsingMethod) = cdoSendUsingPort
iConf.Fields.Item(cdoSMTPServer) = conSMTPserver
iConf.Fields.Update

' ============================================================
' Load initial XHTML: CSS
' ============================================================
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFile = fso.OpenTextFile(strCSSfile, ForReading)
if objFile > 0 then
strHTMLCSS = objFile.ReadAll
'if conDEBUG then Wscript.Echo "+objFile = " & strHTML
else
Wscript.Echo conScriptName & ". ERROR: Unable to open CSS file: " & strCSSfile
wscript.quit
end if

' ============================================================
' Get user's, then detail and email'em
' ============================================================
strSQL = "SELECT USERID, UserName, QuotaSize, QuotaType, Email, MsgCount, MsgSize " & _
"FROM VWEASUserQuotaUsage"
if conDEBUG then strSQL = strSQL & " WHERE USERID = 7" ' ******** DEBUG ************
if conDEBUG then Wscript.Echo "strSQL = " & strSQL

objRS.Open strSQL, objConn, 2
if (objRS.BOF or objRS.EOF) then
strMsg = conScriptName & ": No EAS records to process"
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
WScript.Quit
end if

strDateTime = Date & " " & Time
numUsers = 0
do while not objRS.EOF
strUSERID = objRS("USERID")
if conDEBUG then Wscript.Echo "--Working on USERID = " & strUSERID
numUsers = numUsers + 1
' Get EAS standard information
strSQL = "SELECT USERID, REF_COUNT, UNCOMPRESSEDSIZESUM, COMPRESSEDSIZESUM " & _
"FROM VIEW_STORAGE_BY_USER_REF2 WHERE USERID = '" & strUSERID & "'"
if conDEBUG then Wscript.Echo "strSQL = " & strSQL
objRS2.Open strSQL, objConn, 2
if (objRS2.BOF or objRS2.EOF) then
strMsg = conScriptName & ": Unable to open VIEW_STORAGE_BY_USER_REF2 for user " & strUSERID
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
WScript.Quit '******????
end if
numREF_COUNT = cDbl(objRS2("REF_COUNT")) ' EAS Total email count
numUNCOMPRESSEDSIZESUM = cDbl(objRS2("UNCOMPRESSEDSIZESUM")) ' EAS MB size un-compressed
numCOMPRESSEDSIZESUM = cDbl(objRS2("COMPRESSEDSIZESUM")) ' EAS MB size compressed
objRS2.Close

' ============================================================
' Build header
' ============================================================
numQuota = clng(objRS("QuotaSize"))
'numMsgSize = clng(objRS("MsgSize")) Use numUNCOMPRESSEDSIZESUM
if numQuota = -1 then
strQuotaDesc = " (Unlimited)"
strPctUsed = " "
else
strQuotaDesc = " "
numPctUsed = (numUNCOMPRESSEDSIZESUM/numQuota)
strPctUsed = FormatPercent(numPctUsed,0,false,false,true)
end if

' Create header
strName = objRS("UserName")
strEmail = objRS("Email")
strSize = pad(FormatNumber(numUNCOMPRESSEDSIZESUM,0,false,false,true),"r",10)
strLimit = pad(FormatNumber(numQuota,0,false,false,true),"r",10)
strCount = pad(FormatNumber(numREF_COUNT,0,false,false,true),"r",10)
strSendSize = pad(FormatNumber(numSendSize,0,false,false,true),"r",4)
strReceiveSize = pad(FormatNumber(numReceiveSize,0,false,false,true),"r",4)

strHTML = strHTMLCSS ' Initialize
strHTML = strHTML ... author note: code with html construction available on request.
' ============================================================
' Build detail lines
' ============================================================
strSQL = "SELECT " & conMaxEmailDetail & _
"SUBJECT, FROMFLD, MSGDATE, MsgSize, FOLDERNAME FROM VWEASUserTopMsgs WHERE USERID = " & strUSERID & _
" ORDER BY MsgSize DESC"
if conDEBUG then Wscript.Echo "strSQL = " & strSQL
objRS3.Open strSQL, objConn, 2

strHTML = strHTML & ... author note: code with html construction available on request.

numCount = 0
do while not objRS3.EOF
numCount = numCount + 1
strMailDate = pad(datevalue(objRS3("MSGDATE")), "r", 10)
strMailSize = pad(CStr(objRS3("MsgSize")),"r", 5) & " MB"
strMailFrom = pad(objRS3("FROMFLD"), "l", 26)
strMailSubject = pad(objRS3("SUBJECT"), "l",31)
strMailFolder = pad(objRS3("FOLDERNAME"), "l",70)

strHTML = strHTML ... author note: code with html construction available on request.
objRS3.movenext
Loop ' All users
strHTML = strHTML & ... author note: code with html construction available on request.

objRS3.Close

' Disclaimer stuff
strHTML = strHTML & ... author note: code with html construction available on request.
' *** END

' ============================================================
' Create email
' ============================================================
if numCount > 0 then
strTo = strEmail
if conDEBUG then strBCC = conEmailAdminUser
strCC = ""
strFrom = conFromUser
strSubject = "Email Account Summary" & conMaxEmailDetail & "" & RTRIM(objRS("UserName"))
else ' You had summary record with no supporting detail
strTo = conEmailAdminUser
strBCC = ""
strCC = ""
strFrom = conFromUser
strSubject = "Failure to Email User:" & RTRIM(objRS("UserName")) & ", No supporting detal"
end if
if conDEBUG then Wscript.Echo strHTML

' Set the message properties.
if conSendMail then
With iMsg
Set .Configuration = iConf
.To = strTo
if len(strBCC) > 0 then .BCC = strBCC
if len(strCC) then .CC = strCC
.From = strFrom
.Subject = strSubject
.HTMLBody = strHTML
End With
iMsg.Send ' send the message.
if err.number = 438 then
if conDEBUG then Wscript.Echo "Skipping error 438 " & err.Description
elseif err.number <> 0 then
intLoc = 5
strMsg = conScriptName & ": Error @ " & intLoc & ". err = " & err.number & " smtp mail send failed for USERID=" & strUSERID
DoError(strMsg)
else ' at some point write to db log
strMsg = conScriptName & ": Mailed: " & strName & " @ " & strSubject & _
"; Size: " & strSize & "; Limit: " & strLimit & "; Count: " & strCount
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
end if ' ierr
end if ' conSendMail

objRS.movenext
Loop ' All users

strMsg = conScriptName & ": end execution. Total emails sent = " & numUsers
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg

' ============================================================
' The End
' ============================================================
strMsg = "END: " & conScriptName
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg

objRS.Close
objConn.Close
Set objFile = nothing
Set objFile = nothing


' ======================================================
Function DoError(strErrMsg)
On Error Resume Next
Dim objShell

if conDEBUG then wscript.Echo strErrMsg

Set objShell=CreateObject("wscript.shell")
objShell.LogEvent 1,strErrMsg
End Function

' ======================================================
Function pad(strString, strWay, numLen)
' Padd out a passed string
' strWay "r" right, "l" left pad
' numLen overall size you want
Dim numLenVar, numDelta

numLenVar = LEN(strString)
if numLenVar > numLen then
pad = LEFT(strString,numLen)
Exit Function
elseif numLenVar = numLen then
pad = strString
Exit Function
end if

numDelta = numLen - numLenVar
if strWay = "l" then
pad = strString & String(numDelta, " ")
else ' "r"
pad = string(numDelta," ") & strString
end if

End Function

VWEASUserQuotaUsage.SQL: EAS usage view

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and provides key to inhouse Onecard system and match to Active Directory sAMAccount name.
Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs and EASNearQuotaEmail.vbs .
Code:

/*
File : VWEASUserQuotaUsage.SQL
Update : 08-July-2006/Robert Lawson
Purpose: Usage and quota for EAS users (only users with usage)
Notes :
- Size and Quota are expressed in MB, Quota = -1, is unlimited
- Works with VWEASUserTopMsgs

SELECT * FROM VWEASUserQuotaUsage
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserQuotaUsage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserQuotaUsage]

CREATE VIEW dbo.VWEASUserQuotaUsage AS
SELECT
US.USERID,
RTRIM(US.USERNAME) AS UserName,
US.EASQUOTAOPTION,
QuotaSize = CASE WHEN
US.EASQUOTAOPTION = 1 THEN GP.EASQUOTA
ELSE US.EASQUOTA
END,
GP.GROUPNAME,
QuotaType = CASE WHEN
US.EASQUOTAOPTION = 1 THEN 'Group'
ELSE 'User'
END,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) AS sAMAccountName,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) + '@uni.edu' AS Email,
COUNT(*) AS MsgCount,
SUM(CAST(PR.MSGSIZE AS BIGINT))/1048576 AS MsgSize
FROM PROFILE PR, REFER RF, GROUPS GP, NTACCOUNT NT, USERS US
WHERE
US.EASSTATUS = 0 AND
(US.GROUPID > 0 AND US.GROUPID <> 2) AND
NT.USERID = US.USERID AND
GP.GROUPID = US.GROUPID AND
RF.USERID = US.USERID AND
RF.FOLDERID > 0 AND
RF.FOLDERID = (SELECT MAX(R2.FOLDERID) FROM REFER R2 WHERE
R2.USERID = RF.USERID AND R2.MSGID = RF.MSGID AND R2.FOLDERID > 0) AND
PR.MSGID = RF.MSGID
GROUP BY US.USERID, US.USERNAME, US.EASQUOTAOPTION, GP.EASQUOTA, US.EASQUOTA, NT.NTACCOUNT, GP.GROUPNAME

VWEASUserTopMsgs.sql: EAS top user attachments

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and list of largest email attachments. Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs .
Code:
/*
File : VWEASUserTopMsgs.sql
Update : 08-July-2006/Robert Lawson
Purpose: Get biggest emails for a given user
Notes :
- Size is in MB to be consistent with Quota and VWEASUserQuotaUsage
- Works with VWEASUserQuotaUsage (gets the user information)

SELECT TOP 20 * FROM VWEASUserTopMsgs WHERE USERID = 9 ORDER BY MsgSize DESC
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserTopMsgs]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserTopMsgs]

CREATE VIEW dbo.VWEASUserTopMsgs AS
SELECT
RF.MSGID,
RF.USERID,
FL.FOLDERNAME,
PR.SUBJECT,
PR.FROMFLD,
PR.MSGDATE,
PR.MSGSIZE/1048576 MsgSize
FROM FOLDER FL, PROFILE PR, REFER RF
WHERE
RF.FOLDERID > 0 AND
RF.FOLDERID = (SELECT MAX(R2.FOLDERID) FROM REFER R2 WHERE
R2.USERID = RF.USERID AND R2.MSGID = RF.MSGID AND R2.FOLDERID > 0) AND
PR.MSGID = RF.MSGID AND
FL.USERID = RF.USERID AND
FL.FOLDERID = RF.FOLDERID

Thursday, June 1, 2006

EASNearQuotaEmail.vbs: Email over quota notice

Author: Robert Lawson
Environment: SQL Server, Windows Server, vb-script, EAS, Exchange
Description: This script is run as nightly job to notify users if their email EAS mailbox storage is nearing quota. The assumption is that the EAS storage for email attachments will fill up first and that the users have been getting a weekly report of the usage: EASUsageAudit.vbs
Features:
- Configurable quota to begin sending notification
- Custom wording to send in email
- bcc notification to administrator(s) of email sent
- Write to application event log for easy trouble-shooting
Sample:





Code Listing:

'=========================================================================

' FILE: EASNearQuotaEmail.vbs

' AUTHOR: Robert Lawson

' COMPANY: Soka University of America

' DATE: 6/01/2006 Robert Lawson, Creation Date

' 11/12/2006 Added EAS VIEW_STORAGE_BY_USER_REF2, not "as" correct,

' but is used for enforcing quota.

' COMMENT: Audit EAS Usage and notification

' - Set value of "conSendMail" if you send mail or not, also conDEBUG

' - Relies heavily on t-sql view VWEASUserQuotaUsage

' - Server run on has to be allow SMTP relay on Exchange

' - VWEASUserQuotaUsage can not calc percent, must be done in program



'=========================================================================

option explicit

On Error Resume Next

const conScriptName = "EASNearQuotaEmail.vbs"

const conDEBUG = TRUE

const conSendMail = FALSE ' TRUE = will send email, FALSE=will not



Dim objShell, strMsg, intLoc

Dim objConn, objRS, objRS2, strSQL, strConn

Dim numQuotaSize, numMsgSize, numEASPct, numMsgCount

Dim numREF_COUNT, numUNCOMPRESSEDSIZESUM, numCOMPRESSEDSIZESUM

Dim strQuotaSize, strMsgSize, strEASPct, strUserName

Dim strMailBody, numCount, numUsers, strUSERID, strDetail, strQuotaDesc

Dim iMsg, iConf, Flds

Dim strTo, strBCC, strCC, strFrom, strSubject, strTextBody



Const conEASQuotaUnlimited = -1

Const conMaxEASNotificationPct = .95 ' EAS percent to warn at (1=100%)

' Do not set your smtp server information here.

Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _

cdoSendUsingPort = 2, _

cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"

Const conSMTPserver = "smtp.campus.edu"

Const conFromUser = "E-Admini@campus.edu"

Const conEmailAdminUser = "robert.lawson@campus.edu" ' comma seperated



' Setup

Set objShell = CreateObject( "WScript.Shell" )



' Write event log that you started

strMsg = conScriptName & ": Begin execution"

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg



' Email stuff (SMTP)

Set iMsg = CreateObject("CDO.Message")

Set iConf = CreateObject("CDO.Configuration")

iConf.Fields.Item(cdoSendUsingMethod) = cdoSendUsingPort

iConf.Fields.Item(cdoSMTPServer) = conSMTPserver

iConf.Fields.Update



' DB stuff

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.Recordset")

Set objRS2 = CreateObject("ADODB.Recordset")

strConn = "Provider=SQLOLEDB; SERVER=YourServer; DATABASE=DBname;Integrated Security = SSPI"

objConn.Open strConn



' Get summary for active EAS users with space used

strSQL = "SELECT USERID, UserName, QuotaSize, QuotaType, Email, MsgCount, MsgSize " & _

"FROM VWEASUserQuotaUsage"

strSQL = strSQL & " WHERE QuotaSize > 0" ' Not unlimited

' strSQL = strSQL & " AND USERID = 497" ' **** DEBUG ****

if conDEBUG then Wscript.Echo "strSQL = " & strSQL

objRS.Open strSQL, objConn, 2

if (objRS.BOF or objRS.EOF) then

strMsg = conScriptName & ": No EAS records to process"

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

WScript.Quit

end if



numUsers = 0

do while not objRS.EOF

strUSERID = objRS("USERID")



strSQL = "SELECT USERID, REF_COUNT, UNCOMPRESSEDSIZESUM, COMPRESSEDSIZESUM " & _

"FROM VIEW_STORAGE_BY_USER_REF2 WHERE USERID = '" & strUSERID & "'"

if conDEBUG then Wscript.Echo "strSQL = " & strSQL

objRS2.Open strSQL, objConn, 2

if (objRS2.BOF or objRS2.EOF) then

strMsg = conScriptName & ": Unable to open VIEW_STORAGE_BY_USER_REF2 for user " & strUSERID

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

WScript.Quit '******????

end if

numREF_COUNT = cDbl(objRS2("REF_COUNT")) ' EAS Total email count

numUNCOMPRESSEDSIZESUM = cDbl(objRS2("UNCOMPRESSEDSIZESUM")) ' EAS MB size un-compressed

numCOMPRESSEDSIZESUM = cDbl(objRS2("COMPRESSEDSIZESUM")) ' EAS MB size compressed



strUserName = RTRIM(objRS("UserName"))

numQuotaSize = cDbl(objRS("QuotaSize"))

' numMsgCount = cDbl(objRS("MsgCount")) Use numREF_COUNT

' numMsgSize = cDbl(objRS("MsgSize")) Use numUNCOMPRESSEDSIZESUM

if numQuotaSize <= 0 then numEASPct = 1.0 else numEASPct = numUNCOMPRESSEDSIZESUM / numQuotaSize end if if numEASPct > conMaxEASNotificationPct then

numUsers = numUsers + 1

if conDEBUG then Wscript.Echo "--USERID/Name = " & strUSERID & "/" & strUserName & " pct: " & numEASPct



if numQuotaSize = conEASQuotaUnlimited then

strQuotaDesc = " (Unlimited)"

strEASPct = " "

else

strQuotaDesc = " "

strEASPct = FormatPercent(numEASPct,0,false,false,true)

end if

strMsgSize = FormatNumber(numUNCOMPRESSEDSIZESUM,0,false,false,true)

strQuotaSize = FormatNumber(numQuotaSize,0,false,false,true)

strMsgCount = FormatNumber(numREF_COUNT,0,false,false,true)



' Create header FormatNumber(objRS("QuotaSize"),0,false,false,true)

strMailBody = ""

strMailBody = _

"You have received this message because your mailbox is approaching its size limit." & vbCrLF & _

"Your mailbox size is currently " & strEASPct & " full (size (MB): " & strMsgSize & "; limit (MB): " & strQuotaSize & "). " & _

"When your mailbox fills up you will not be able to send nor receive any more email. " & _

"Please do the following to reduce mailbox volume:" & vbCrLF & _

"1. Delete items in the Sent-Items folder" & vbCrLF & _

"2. Empty the Deleted-Items folder" & vbCrLF & _

"3. Sort messages by the attachment icon and delete messages with large attachments" & vbCrLF & _

"Mailbox space can be freed up most efficiently by focusing on deleting emails with attachments. In most cases a large portion of a mailbox’s volume is occupied by a collection of emails with attachments like photos, music, videos and scans. This also includes emails you sent to others and remain in your “Sent Items” folder. Note the mailbox size and limits refer to the EAS system." & vbCrLF & _

"Thank you" & vbCrLF & "SUA-Email-Administrators" & vbCrLF & vbCrLF & _

"source: " & conScriptName & vbCrLF



' Prep email

strTo = objRS("Email")

strBCC = conEmailAdminUser

strCC = ""

strFrom = conFromUser

strSubject = "Your Mailbox is Approaching its Size Limit" & strUserName

strTextBody = strMailBody

if conDEBUG then Wscript.Echo strMailBody



' Send thu sucku

if conSendMail then

With iMsg

Set .Configuration = iConf

.To = strTo

if len(strBCC) > 0 then .BCC = strBCC

if len(strCC) then .CC = strCC

.From = strFrom

.Subject = strSubject

.TextBody = strTextBody

End With

iMsg.Send ' send the message.

if err.number <> 0 then

intLoc = 5

strMsg = conScriptName & ": Error @ " & intLoc & ". smtp mail send failed for " & strUSERID

DoError(strMsg)

else ' at some point write to db log

strMsg = conScriptName & ": email sent to " & strTo & ", subject: " & strSubject

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg

end if ' ierr

end if ' conSendMail

end if ' numEASPct

objRS.movenext

objRS2.Close

Loop ' All users



strMsg = conScriptName & ": end execution. Total emails sent = " & numUsers

if conDEBUG then Wscript.Echo strMsg

objShell.LogEvent 0,strMsg



objRS.Close

objConn.Close



' ======================================================

Function DoError(strErrMsg)

On Error Resume Next

Dim objShell



' wscript.Echo strErrMsg



Set objShell=CreateObject("wscript.shell")

objShell.LogEvent 1,strErrMsg

End Function

Friday, October 22, 2004

Fin8PT844PostDBRefresh.sql: PS Financial database refresh

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft
Description: This script updates PeopleSoft Financials v8.8 database all the steps to make it operational, point to 'test' instances, update security and clean out working tables.
Code:
/*
File : Fin8PT844PostDBRefresh.sql
Update : 22-Oct-2004/Robert Lawson
Purpose: Post db updates for Finance 8.8


*/
-- Fix user
exec sp_change_users_login 'update_one','folkss','folkss'
exec sp_change_users_login 'update_one','fdb-user','fdb-user'

-- Report node
UPDATE PS_SERVERDEFN SET DISTNODENAME = 'FITEST ' WHERE SERVERNAME = 'PSNT '

-- Change Assistant
UPDATE PSOPTIONS SET GUID = ''
UPDATE PSOPTIONS SET LONGNAME = 'FITEST', SHORTNAME = 'FITEST'

-- Home brandings
UPDATE PSMSGCATDEFN SET
MESSAGE_TEXT = 'FITEST Home'
WHERE MESSAGE_SET_NBR = '95'
AND MESSAGE_NBR = '401'

/*
-- non-core users lock out, removed 6/16/2005
UPDATE PSOPRDEFN SET ACCTLOCK = 1 WHERE
OPRID NOT IN ('VENUS','BINGO','weepy','sneezy','grouchy','sleepy')
*/

-- REN server (not working right now)
-- DELETE FROM PSREN
-- DELETE FROM PSMCFRENURLID

-- Integration Broker
DELETE FROM PSAPMSGDOMSTAT
DELETE FROM PSAPMSGDSPSTAT

-- Local Gateway
UPDATE PSGATEWAY SET
CONNURL = 'http://cs1np.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE CONNGATEWAYID = 'LOCAL'

-- Node/Gateway
UPDATE PSNODECONPROP SET
PROPVALUE = 'http://fs1np.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_EP'

UPDATE PSNODECONPROP SET
PROPVALUE = 'http://cs1.soka.campus:12/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_LS'

-- IB, Local Gateway, Connectors

UPDATE PSCONNPROP SET
PROPVALUE = 'http://fs1np.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'FINANCIAL'

UPDATE PSCONNPROP SET
PROPVALUE = 'http://cs1.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'CAMPUS'

-- DM/QA: appmsgpurgeall.dms (remove log statement; clears app messages)
-- core tables:
DELETE FROM PSAPMSGPUBHDR;
DELETE FROM PSAPMSGPUBDATA;
DELETE FROM PSAPMSGPUBCON;
DELETE FROM PSAPMSGSUBCON;
DELETE FROM PSAPMSGPUBERR;
DELETE FROM PSAPMSGPUBERRP;
DELETE FROM PSAPMSGPUBCERR;
DELETE FROM PSAPMSGPUBCERRP;
DELETE FROM PSAPMSGSUBCERR;
DELETE FROM PSAPMSGSUBCERRP;
DELETE FROM PSAPMSGPCONDATA;
DELETE FROM PSAPMSGSCONDATA;

-- synchronous core tables:
DELETE FROM PSIBLOGHDR;
DELETE FROM PSIBLOGDATA;
DELETE FROM PSIBLOGERR;
DELETE FROM PSIBLOGERRP;

-- archive tables:
DELETE FROM PSAPMSGARCHPH;
DELETE FROM PSAPMSGARCHPD;
DELETE FROM PSAPMSGARCHPC;
DELETE FROM PSAPMSGARCHSC;
DELETE FROM PSAPMSGARCHPT;
DELETE FROM PSAPMSGARCHST;
DELETE FROM PSIBLOGHDRARCH;
DELETE FROM PSIBLOGDATAARCH;

Thursday, July 3, 2003

sp_CreateRoleTables: Translate PS security to DB security

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft (Learning Solutions 8/PT 8.19)
Description: This procedure creates a database security role from a PeopleSoft security role. This was used to mimic PeopleSoft security for MS Access user access.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateRoleTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CreateRoleTables]
GO

CREATE PROCEDURE sp_CreateRoleTables
@InputRole VARCHAR(100),
@Debug smallint = 0
AS
/*
Name: sp_CreateRoleTables

Purpose: Create database security role from a PeopleSoft security role.

Date Whom What
----------------------------------------------------------------------------------
03-JUL-03 Robert Lawson Creation date, PT 8.19 & SQL Server 2000

Parmaters
-----------------------------------------------------------------
@InputRole Passed Role name
@Degug Passed 0=no, 1=yes; default is 0.

Description:
This stored procedure helps setup security for non-PeopleSoft application access to
a PeopleSoft database. The technique is to create a database security group equal to
the PeopleSoft security role. The assumption is that you use NT authentication, and you
match the user's network account with the database security role.
The database security role is created from the PeopleSoft security role. The database
security role has SELECT permission ('read only') for all the tables the PeopleSoft
security role allows a user. The tables allowed to a PeopleSoft user are found
through this chain:
Security Role to Permissions List
Permission List/Query to Access Group Permissions
Access Group Permisssions (Tree Name/Access Group) to Tree Structure
Traverse Tree Structure to actual records
The traverse portion is complicated as T-SQL has no method like Oracle's "CONNECT BY.. PRIOR".

How To Use:
In Query Analyzer, in the database you are working in:
1. Execute this script to create the stored procedure
2. Execute this stored procedure for each security role, as such:
exec sp_CreateRoleTables 'UPG_STDN_REC'
In Enterprise Manager
3. In Security/Logins, add new user, if not there, using domain/user and Windows Authentication
- Database Access for this database roles are "public" and "security role"
- Default database is this database
*/

/*
==========================================================
Variables and temp table
==========================================================
*/
IF EXISTS (SELECT 'x' FROM tempdb.dbo.sysobjects WHERE type = 'U' AND LEFT(name,15) = '#CreateRoleTables')
DROP Table #CreateRoleTables
CREATE TABLE #CreateRoleTables
(Tree CHAR(20), Parent CHAR(20), Child CHAR(20), CNode int, PNode int, TLevel int, Type CHAR(1))

DECLARE
@TLevel int, -- Number of levels traversed. 0=first
@Tree CHAR(20), -- Tree name
@CNode int, -- Child node number
@PNode int, -- Parent node number
@Child CHAR(20), -- Parent "contains" level
@Parent CHAR(20), -- Parent level
@Type CHAR(1), -- 'R'ecord, 'G'roup
@CNode1 int, -- Node number
@PNode1 int, -- Node number
@Child1 CHAR(20), -- Child
@Parent1 CHAR(20), -- Parent
@Type1 VARCHAR(1), -- 'R'ecord, 'G'roup for Parent
@EndOfTree smallint, -- 0=no,1=yes. When true, then no more levels to traverse within tree
@SQLString NVARCHAR(500), -- from sp_helpsql
@Table VARCHAR(100), -- SQL table name
@RoleCursor CURSOR,
@ParentCursor CURSOR,
@ChildCursor CURSOR,
@TableCursor CURSOR

SET NOCOUNT ON -- Turn off row count
/*
==========================================================
Load tree name/access groups
==========================================================
*/
SET @TLevel = 0 -- Top of tree
SET @EndOfTree = 1 -- 0=not, 1=yes at EndofTree

SET @RoleCursor = CURSOR FOR
SELECT DISTINCT SA.TREE_NAME, TN.PARENT_NODE_NAME, TN.TREE_NODE,
TN.TREE_NODE_NUM, TN.PARENT_NODE_NUM, TN.TREE_NODE_TYPE
FROM PSTREENODE TN, PS_SCRTY_ACC_GRP SA, PSROLECLASS RC
WHERE
RC.ROLENAME = @InputRole AND
SA.CLASSID = RC.CLASSID AND
SA.ACCESSIBLE = 'Y' AND
TN.TREE_NAME = SA.TREE_NAME AND
TN.TREE_NODE = SA.ACCESS_GROUP AND
TN.TREE_NODE_TYPE = 'G' -- 'G'roup

OPEN @RoleCursor -- Returns all Tree Names & Access Group's for a security role
WHILE (0=0)
BEGIN
FETCH NEXT FROM @RoleCursor INTO
@Tree, @Parent, @Child, @CNode, @PNode, @Type
if @@Fetch_Status <> 0 break

IF @Debug = 1 PRINT 'RoleCursor ' + @Tree + ' ' + @Parent + ' ' + @Child

INSERT INTO #CreateRoleTables (Tree, Parent, Child, CNode, PNode, TLevel, Type) VALUES
(@Tree, @Parent, @Child, @CNode, @PNode, @TLevel, @Type)
SET @EndOfTree = 0
END -- RoleCursor
CLOSE @RoleCursor

/*
==========================================================
Traverse Tree Logic
==========================================================
*/
WHILE @EndOfTree = 0
BEGIN
IF @Debug = 1 PRINT @TLevel
SET @EndOfTree = 1

SET @ParentCursor = CURSOR FOR
SELECT Tree, Parent, Child, CNode, PNode, Type
FROM #CreateRoleTables
WHERE TLevel = @TLevel

OPEN @ParentCursor -- Get 'parent' level
WHILE (0=0)
BEGIN
FETCH NEXT FROM @ParentCursor INTO
@Tree, @Parent, @Child, @CNode, @PNode, @Type
if @@Fetch_Status <> 0 break
IF @Debug = 1 PRINT '@ParentCursor ' + @Parent + ' ' + @Child

SET @ChildCursor = CURSOR FOR
SELECT TREE_NAME, PARENT_NODE_NAME, TREE_NODE, TREE_NODE_NUM, PARENT_NODE_NUM, TREE_NODE_TYPE
FROM PSTREENODE WHERE
TREE_NAME = @Tree AND
PARENT_NODE_NUM = @CNode AND
PARENT_NODE_NAME = @Child

OPEN @ChildCursor -- Get level below 'parent' information
WHILE (0=0)
BEGIN
FETCH NEXT FROM @ChildCursor INTO
@Tree, @Parent1, @Child1, @CNode1, @PNode1, @Type1
if @@Fetch_Status <> 0 break

IF @Debug = 1 PRINT '@ChildCursor ' + @Parent1 + ' ' + @Child1

INSERT INTO #CreateRoleTables (Tree, Parent, Child, CNode, PNode, TLevel, Type) VALUES
(@Tree, @Parent1, @Child1, @CNode1, @PNode1, (@TLevel + 1), @Type1)
SET @EndOfTree = 0

END -- ChildCursor
END -- ParentCursor

SET @TLevel = @TLevel + 1 -- move down a level (the child becomes the parent, awwww...)
IF @Debug = 1 PRINT '@Tlevel = ' + CONVERT(CHAR(5),@TLevel) + ' @EndOfTree = ' + CONVERT(CHAR(5),@EndOfTree)
END -- EndOfTree

CLOSE @ChildCursor
CLOSE @ParentCursor

/*
==========================================================
Role: remove privledges (and retain membership) or create it
==========================================================
*/ -- From master.dbo.sp_helprole
IF EXISTS (select 'x' from sysusers where name = @InputRole and (issqlrole = 1 or isapprole = 1))
BEGIN
SET @SQLString = N'REVOKE ALL FROM ' + @InputRole
IF @Debug = 1 PRINT @SQLString
EXEC sp_executesql @SQLString
END
ELSE
BEGIN
SET @SQLString = N'sp_addrole ' + @InputRole + ', dbo'
IF @Debug = 1 PRINT @SQLString
EXEC sp_executesql @SQLString
END

/*
==========================================================
Permissions: add to role
==========================================================
*/
SET @TableCursor = CURSOR FOR
SELECT DISTINCT
CASE
WHEN RTRIM(RD.SQLTABLENAME) <> '' THEN RTRIM(RD.SQLTABLENAME)
ELSE 'PS_' + RTRIM(RT.Child)
END
FROM PSRECDEFN RD, #CreateRoleTables RT
WHERE
RT.Type = 'R' AND
RD.RECNAME = RT.Child AND
RD.RECTYPE IN (0,1) -- 0=Record,1=View

OPEN @TableCursor -- Returns list of SQL table names
WHILE (0=0)
BEGIN
FETCH NEXT FROM @TableCursor INTO
@Table
if @@Fetch_Status <> 0 break

-- Verify table actually exists in database (from master.dbo.sp_help)
IF EXISTS (select 'x' from sysobjects where id = object_id(@Table))
BEGIN
SET @SQLString = N'GRANT SELECT ON ' + @Table + ' TO ' + @InputRole
IF @Debug = 1 PRINT 'TableCursor ' + @SQLString
EXEC sp_executesql @SQLString
END
ELSE IF @Debug = 1 PRINT 'TableCursor, *** NO TALBE: ' + @Tree
END -- TableCursor
CLOSE @TableCursor

/*
==========================================================
The End
==========================================================
*/
DEALLOCATE @RoleCursor
DEALLOCATE @ParentCursor
DEALLOCATE @ChildCursor
DEALLOCATE @TableCursor

Thursday, September 13, 2001

DBLogFix.sql: MSSQL log file fixes

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft
Description: These are progressive steps to "fix" run-away log files on MS SQL Server database.
Code:
/*
file: DBLogFix.sql

purpose: fix log file too full

create: 13-SEP-2001/Robert Lawson

-- Verify log file set to grow by 5MB, fewer and less chance problem(?)
DBCC OPENTRAN -- open transactions
DBCC LOGINFO -- log file paging, Lots and any with status=2 will not truncate

-- change to database recovery to simple
SELECT DATABASEPROPERTYEX( 'LSPROD' , 'recovery' )
ALTER DATABASE LSPROD SET RECOVERY SIMPLE
*/

-- 1.
BACKUP LOG LSPROD WITH TRUNCATE_ONLY

-- 2.
DBCC SHRINKDATABASE(LSPROD, TRUNCATEONLY)

-- 3. This can only be done with all users off
EXEC sp_detach_db 'LSPROD', 'true'

-- 4. Rename/purge the existing log file (otherwise it will just get attached again!)

-- 5. This step will create a new log file (.ldf)
EXEC sp_attach_db @dbname='LSPROD', @filename1='D:\MSSQL.1\MSSQL\Data\LSPROD.mdf'