Posted in
I found this script buried in the barracuda forums. It grabs the stats (XML) and creates a CSV file and formats it.
The following are the instructions to use the VBScript later in this post:
1. Create a text file and rename it to Barracuda Stats to CSV – Pivot Tables.vbs or whatever you want – as long as it ends in .vbs.
2. Copy the VBScript code below and edit the URL to your stats page between the quotes for the variable sMyBarraCudaStatsPage.
a. For example: sMyBarracudaStatsPage=”http://microsoft-barracuda:8000/cgi-bin/stats.cgi?”
b. Replace microsoft-barracuda with the name of your barracuda.
3. Save the changes and close Notepad.
4. Double Click Barracuda Stats to CSV – Pivot Tables.vbs or whatever you name the .VBS.
5. When it’s done collection a message will popup stating: “Barracuda Stats Collection Complete.”
6. In the same folder or location that the .vbs script is located a .csv file will be created named: BarracudaStatsPivotTable.csv.
7. Open the file with Excel.
8. Scroll down until you see the header row for the hourly statistics, just below Thishour. The header row is Row, Column, Data.
9. Select everything from the header row to the last hour – 14:00.
10. Once this is highlighted select the Data menu, PivotTable and PivotChart Report option from the list.
11. Click Finish.
12. From the Pivot Table Field List window drag Row to where it says Drop Row Fields Here. Do the same for Column and Data.
13. Click the Chart Wizard button that looks like a 3 colored chart lines and you now have a chart of the hourly stats.
14. To hide the pivot chart buttons right mouse click on one of them and select Hide PivotChart Field Buttons.
15. You can right mouse click on the chart and change the Chart Type to 3D or another type of chart. I also like to right mouse click and go into Chart Options and add the Major gridlines for the X & Y axis.
16. To create a chart for the date statistics click on the BarracudaStatsPivotTable tab in the work sheet to go back to the data.
17. Perform the same steps as above for the hourly stats but select the header row and the date statistic rows to highlight those.
VBScript is as follows:
———- Cut and paste below this line ———
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 | Option Explicit Dim objXMLDoc, _ ojbNodeList, _ strData, _ bFirstIteration, _ objNodeList, _ sMyBarracudaStatsPage, _ x '// Put the URL to your stats page in between the quotes below. ' For example: sMyBarracudaStatsPage="http://microsoft-barracuda:8000/cgi-bin/stats.cgi?" ' Where "microsoft-barracuda" would be the name of your Barracuda. sMyBarracudaStatsPage="Put the URL to your stats page here" Set objXMLDoc = CreateObject("Microsoft.XMLDOM") objXMLDoc.async = False objXMLDoc.load(sMyBarracudaStatsPage) Set objNodeList = objXMLDoc.getElementsByTagName("*") bFirstIteration = True Dim objFSO, _ cForReading, _ cForWriting, _ oTheFile, _ sCategory, _ sNodeTagName, _ bHourHeadersSet, _ bDateHeadersSet cForReading = <span style="color: #ffa34f;"><strong>1</strong></span> cForWriting = 2 '// Instantiate the file system object. Set objFSO = CreateObject("Scripting.FileSystemObject") '// Open the file to begin processing. Set oTheFile = objFSO.OpenTextFile("BarracudaStatsPivotTable.csv",cForWriting,True) For x = <span style="color: #ffa34f;"><strong>1</strong></span> to (objNodeList.Length - <span style="color: #ffa34f;"><strong>1</strong></span>) If InStr(objNodeList.Item(x).Text," ")<>0 Then '// Capture the category. sCategory=objNodeList.Item(x).tagName '// Capitalize the first letter of the category name. sCategory = uCASE(Left(sCategory,<span style="color: #ffa34f;"><strong>1</strong></span>)) & Right(sCategory,Len(sCategory)-<span style="color: #ffa34f;"><strong>1</strong></span>) '// Writer the headers at the beginning of the Hourly statistics. If Left(sCategory,4)="Hour" Then If bHourHeadersSet=False Then oTheFile.WriteLine "Row, Column, Data" bHourHeadersSet=True End If '// Remove the text Date_ from the date. sCategory = Replace(sCategory,"Hour_","") '// Format the time as hh:mm. sCategory=Left(sCategory,2) & ":" & Right(sCategory,2) End If '// Insert the header row for the Date statistics. If Left(sCategory,4)="Date" Then If bDateHeadersSet=False Then oTheFile.WriteLine "Row, Column, Data" bDateHeadersSet=True End If '// Remove the text Date_ from the date. sCategory = Replace(sCategory,"Date_","") '// Format the date as mm/dd/yyyy. If Len(sCategory)=8 Then sCategory=Left(sCategory,2) & "/" & Mid(sCategory,<span style="color: #ffa34f;"><strong>3</strong></span>,2) & "/" & Right(sCategory,4) ElseIf Len(sCategory)=7 Then sCategory=Left(sCategory,<span style="color: #ffa34f;"><strong>1</strong></span>) & "/" & Mid(sCategory,2,2) & "/" & Right(sCategory,4) End If End If Else '// Capitalize the first letter of the node's tag name. sNodeTagName = uCASE(Left(objNodeList.Item(x).tagName,<span style="color: #ffa34f;"><strong>1</strong></span>)) & Right(objNodeList.Item(x).tagName,Len(objNodeList.Item(x).tagName)-<span style="color: #ffa34f;"><strong>1</strong></span>) '// Write the Category, Description, Value into the file. oTheFile.WriteLine sCategory & "," & sNodeTagName & "," & objNodeList.Item(x).Text Select Case Left(sNodeTagName,4) Case "Hour" '// Set this flag so that the headers are only inserted once for the ' hours data. bHourHeadersSet=True End Select End If Next oTheFile.Close WScript.Echo "Barracuda Stats Collection Complete." |
