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 |
