Thursday, June 09, 2005

Rightfax 9 Dialing Rules Export

How to export Rightfax 9 Dialing Rules from SQL into a CSV file.

I was unable to use any of the GUI's in Rightfax 9 to export the dialing rules database as since SP1, they have moved them to SQL tables.

Using SQL Manager, there are 3 tables I need as far as I can see.

dbo.DTDiallingRules : holds a list of every rule with unique Rule ID's
dbo.DTConfigurationDaillingRules : holds server GUID's plus associated Rule ID's
dbo.DTConfigurations : maps server name to server GUID's.

So it should be a matter of creating a DSN in ODBC manager to your SQL server, then exporting the 3 tables, cross referencing and job done. And so faxrules.vbs was born.

'==============================================
'
' VBScript Source File
'
' NAME: FaxRules.vbs
'
' AUTHOR: Woosie.
' DATE : 08-06-2005
'
' COMMENT: Export Rules on a daily basis to keep tabs on things.
'
'==============================================

Dim DTDR,DTCDR,DTC

' -------------------------
' Create Connection to ODBC
' -------------------------
' These lines may need changing.
DSN="rightFAXDSN"
uid="sa"
pwd="xxxxxxxx"

FullPath="c:\"
todays=FormatDateTime(Date,1)
Dim MyTime,MyHour,Myminute
MyTime=now
Myhour=hour(mytime)
myminute=minute(mytime)
times=myhour & "-" & myminute
FullPath=FullPath & "\" & todays & ".csv"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objfile = fso.OpenTextFile(fullpath, 8, True)

Dim OBJdbConnection
Set OBJdbConnection = CreateObject("ADODB.Connection")
Set RS = CreateObject ("ADODB.Recordset")
Set SQLStmt = CreateObject("ADODB.Command")

' -------------------------
' Open Connection
' -------------------------
OBJdbConnection.ConnectionString = "dsn=" & DSN &";uid=" & uid & ";pwd=" & pwd &";DATABASE=RightFax;APP=ASP Script"
OBJdbConnection.Open

set rsDTCDR=createobject("adodb.recordset")
RSDTCDR.open "DTConfigurationsDialingRules",OBJdbConnection,3,3
objfile.writeline("Server,RuleID,SourceServer,Pattern,DestinationServer,Protocol,PriorityDelta,

Flags,PriorityMask,StartChannel,TimeofDayMask_0,TimeofDayMask_1,
TimeofDayMask_2,
TimeofDayMask_3,TimeofDayMask_4,TimeofDayMask_5,TimeofDayMask_6,
BeginDelete,BeginReplace,EndDelete,EndReplace,UserID,GroupID,EndChannel,
Comment,MinQueueDepth,ExtraWeight,DelayHour,
FaxSize,MLAccount,MLPassCode,MLDocAccount,DNDNotifyList,NotifySender")
DTCDR=rsDTCDR.GetRows()
DTCC=UBound(DTCDR,1)
DTCR=UBound(DTCDR,2)
For lo1=0 To DTCR
Rule=DTCDR(1,lo1)
Disabled=DTCDR(2,lo1)
If disabled = 0 Then
objfile.writeline(GUID2Server(DTCDR(0,lo1)) & ",Disabled")
Else
objfile.writeline(GUID2Server(DTCDR(0,lo1)) & "," & RuleID(rule))
End if
next
Set rsDTDR=Nothing
Set rsDTCDR=Nothing
Set rsDTTC=Nothing
Set objfile=Nothing


Function GUID2Server(target)
' -------------------------
' Return Servername from GUID
' -------------------------
set rsDTC=createobject("adodb.recordset")
RSDTC.open "DTConfigurations",OBJdbConnection,3,3
GUID2Server="Not Found"
DTC=rsDTC.GetRows()
DTCC1=UBound(DTC,1)
DTCR1=UBound(DTC,2)
For loGUID=0 To DTCR1
If target=DTC(0,loGUID) Then
GUID2Server=DTC(1,loGUID)
End if
Next
Set rsDTC=Nothing
End Function

Function RuleID(target)
set rsDTDR=createobject("adodb.recordset")
RSDTDR.open "DTDialingRules",OBJdbConnection,3,3
RuleID="Not Found"
DTC=rsDTDR.GetRows()
DTCC2=UBound(DTC,1)
DTCR2=UBound(DTC,2)
For loGUID=0 To DTCR2
If target=DTC(0,loGUID) Then
RuleID=""
For add=0 To DTCC2
RuleID=RuleID & chr(34) & DTC(add,loGUID) & Chr(34) & ","
Next
End If
Next
Set rsDTDR=Nothing
End Function

No comments: