1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
|
Dim ExcelSheet
Dim Age
Dim TextStream
Dim fileObj
Dim Fso
Dim strLatestFile
Dim DateInfo
Dim DiffADate
Dim msg
Dim strPath
Dim strSaveFolder
Dim NewAge
on error resume next
'
' the backup exec folder containing the logs must be shared as 'bx$'
' LIST OF SERVER TO BE CHECKED, list then as <server> not \\<server>
' one per line...
strPath = "c:\scripts\servers.txt"
'require folder 'strSaveFolder' to store excel sheets
strSaveFolder = "c:\backuplogs"
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
ExcelSheet.ActiveSheet.Cells(1, 1).Value = "This is column A, row 1"
ExcelSheet.ActiveSheet.Columns(1).ColumnWidth = 15
ExcelSheet.ActiveSheet.Columns(2).ColumnWidth = 40
ExcelSheet.ActiveSheet.Columns(3).ColumnWidth = 30
ExcelSheet.ActiveSheet.Columns(4).ColumnWidth = 35
ExcelSheet.ActiveSheet.Cells(1, 1).Value = "Server"
ExcelSheet.ActiveSheet.Cells(1, 2).Value = "Date completed"
ExcelSheet.ActiveSheet.Cells(1, 3).Value = "Status"
ExcelSheet.ActiveSheet.Cells(1, 4).Value = "Error"
ExcelSheet.ActiveSheet.Cells(1, 4).Value = Date
Row = 2
col = 1
Set Fso = CreateObject("scripting.FileSystemObject")
If (Fso.FileExists(strPath)) Then
Set ServerList = Fso.OpenTextFile(strPath, 1)
Else
MSGBOX "we need a list of servers guys!"
End If
Do Until ServerList.AtEndOfLine = True
strServer = ServerList.ReadLine
strPath = "\\" & strServer & "\bx$"
If (Fso.FolderExists(strPath)) Then
Set folderObj = Fso.GetFolder(strPath)
Else
ExcelSheet.ActiveSheet.Cells(Row, 2).Value = strServer
ExcelSheet.ActiveSheet.Cells(Row, 3).Value = "No BX$ share on server"
End If
Set fileContainer = folderObj.Files
For Each fileObj In fileContainer
strRt = Right(fileObj.Name, 3)
If strRt = "txt" Then
strFile = fileObj.Name
DiffADate = DateDiff("d",fileObj.DateCreated,Now)
NewAge = DiffADate
If (NewAge < Age=true) or (Age="") Then
strLatestFile = fileObj.Name
Age = NewAge
End If
End If
Next
strPath = strPath & "\" & strLatestFile
set fileObj = Fso.GetFile(strPath)
Set TextStream = fileObj.OpenAsTextStream(1)
ExcelSheet.ActiveSheet.Cells(Row, 1).Value = strServer
col = col + 1
ExcelSheet.ActiveSheet.Cells(Row, 3).Value = "failed..."
Do While Not textstream.AtEndOfStream
Str = textstream.ReadLine
posErr = InStr(Str, "^ ")
posEnd = InStr(Str, "b ended:")
posCom = InStr(Str, "completed on")
posStatus = InStr(Str, "completion status:")
If posCom <> 0 Then
Str = Mid(Str, 8, 35)
ExcelSheet.ActiveSheet.Cells(Row, 2).Value = Str
ExcelSheet.ActiveSheet.Cells(Row, 3).Value = "pending tape change"
End If
If posEnd = 3 Then
Str = Mid(Str, 12, 50)
ExcelSheet.ActiveSheet.Cells(Row, 2).Value = Str
End If
If posStatus = 5 Then
Str = Mid(Str, 24, 35)
ExcelSheet.ActiveSheet.Cells(Row, 3).Value = Str
End If
If posErr <> 0 Then
ExcelSheet.ActiveSheet.Cells(Row, 4).Value = LastStr
col = col + 1
End If
LastStr = Str
Loop
Str = ""
LastStr = ""
Row = Row + 1
Loop
If (Fso.FolderExists(strSaveFolder)) Then
strSaveAs = Date
strSaveAs = Replace(strSaveAs, "/", "-")
strSaveAs = "c:\backuplogs\" + strSaveAs + ".xls"
ExcelSheet.SaveAs strSaveAs
Else
MsgBox "No C:\backuplogs folder on this server", vbInformation
End If |
Partager