Gathering info from MySQL Bugzilla DB into Excel

[marcus...@gmail.com]

If these questions have been answered elsewhere, the post is too long or you basically do not feel like reading it, please feel free to tell me so. I am typing it out, in hopes it helps someone. :-)

In all seriousness though, let me say that learning MySQL, SQL Server, Perl, Apache, Bugzilla, what ODBC is and Excel programming all at the same time, while in the long run will serve me well, is daunting right now. I am learning on my own, but thank God this support list is here, otherwise it would take me forever.

I am attempting to gather data into Excel (for charting or simple spreadsheets) from our MySQL Bugzilla database. The Bugzilla database is located on a newtwork server (installation and DB) and is basically farmed out, although my company (read "ME") has to manage it.

In order to be able to speak to the server, I had to create and ODBC connection, using MySQL connector (don't get me started on trying to understand the difference between User DSN, System DSN, ODB files, etc.). When attempting to create this connection, I had to connect to the actual server as is is not a localhost. I finally figured out that I had to designate the name of the server and then got an error that I could not conect.

Originally, I thought I could do something like the following;

GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY 'password!';

However, this did not work. I now believe it is because all of the privledges were set as "N". I then created a ROOT user with the same MySQL statement, using the IP address of my system (instead of %) and manually granted all the permissions, using MySQL Workbench queries. That worked. Now, I have 3 ROOT users. The one for "localhost", the one I created with "%" and the one I created with "my IP address".

I then attempted to use the Query Wizard in Excel 2010 to bring over what info I wanted to see in my spreadsheet. I now have the following questions about what I have done.

1. Does anyone know of a link to completely explain what a Pivot Table is?
2. If I switch all of the privledges for the ROOT user I created with "%", will I then be able to access the database through Excel, from any system, provided that system had the ODBC connection?
a. Basically, I need someone to be able to click a link and have the data they want, spit out into Excel. I won't be running these queries every time.
3. How do I delete all of the extra ROOT users I created, without deleting the main ROOT?
a. Is there a way to simply modify the existing ROOT account to work off of any other IP address?
b. Regarding the above, I am assuming it would be more secure, for me to create a user other than ROOT and grant that user the permissions needed to run the Excel queries.
4. If I want to run queries and dump the data into Excel, is it easier to run through the Query Wizard,, use VB code, use Excel code or a combination of all 3?
5. When I did connect to the database and attempted to get the data I wanted into Excel, I noticed that some of the data (assigned_to, product_id, component_id, etc.) are numbers. Is that because they need to be referenced by that number in another table? If so, how do I associate that data without the Excel query complaning that they aren't? How do I get the names of the items into my spreadsheet, instead of numbers?

I apologize this is long and I don't know if it will even get read, answered or even deleted. If anyone can only answer a part of it though, I would appreciate any info I could get. Thanks.

[Steve Wendt]

On 7/19/2012 5:49 PM, marcus...@gmail.com wrote:

> If these questions have been answered elsewhere, the post is too long
> or you basically do not feel like reading it, please feel free to
> tell me so.

Your questions seem to all be related to MySQL, so you will probably get
the best help in a MySQL forum. I certainly wouldn't recommend leaving
open access for root@%, that's risky. If this is for reporting purposes
only, I'd suggest creating a user that only has read (select) access,
and even then I'd be very cautious about who got direct access to all
the database information.

[Thorsten Schöning]

Guten Tag marcus...@gmail.com,
am Freitag, 20. Juli 2012 um 02:49 schrieben Sie:

> I apologize this is long and I don't know if it will even get read,
> answered or even deleted. If anyone can only answer a part of it
> though, I would appreciate any info I could get. Thanks.

What I don't understand is: What exactly are you trying to achieve and
why is the only way to achieve it let Excel speak directly to the
database? Besides that you would next have to deal with Bugzilla's
schema to find the data you need. Why not just use the search
capabilities of Bugzilla and export data as CSV or XML and import that
into your Excel tables?

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail:Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon.............030-2 1001-310
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hanover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Marcus Polk]

Steve,

I'll try a MySQL forum.

Thorsten,

Honestly, I was trying to do it all in Excel, as that's the way the previous admin apparently did it at my boss' old company and what he's been hammering in.

I took a look at the Bugzilla queries and can certainly export to a CSV, but I need to automate this thing so any of the bosses, to include this one, can simply click on a link, have the report run and everything come up in their nice little spreadsheet.

Thank you guys for taking the time to answer.

Marcus

On Jul 20, 2012, at 12:09 AM, Thorsten Schöning <tscho...@am-soft.de> wrote:

> Guten Tag marcus...@gmail.com,
> am Freitag, 20. Juli 2012 um 02:49 schrieben Sie:
>

>> I apologize this is long and I don't know if it will even get read,
>> answered or even deleted. If anyone can only answer a part of it
>> though, I would appreciate any info I could get. Thanks.
>

> What I don't understand is: What exactly are you trying to achieve and
> why is the only way to achieve it let Excel speak directly to the
> database? Besides that you would next have to deal with Bugzilla's
> schema to find the data you need. Why not just use the search
> capabilities of Bugzilla and export data as CSV or XML and import that
> into your Excel tables?
>
> Mit freundlichen Grüßen,
>
> Thorsten Schöning
>
> --
> Thorsten Schöning E-Mail:Thorsten....@AM-SoFT.de
> AM-SoFT IT-Systeme http://www.AM-SoFT.de/
>
> Telefon.............030-2 1001-310
> Fax...............05151- 9468- 88
> Mobil..............0178-8 9468- 04
>
> AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
> AG Hanover HRB 207 694 - Geschäftsführer: Andreas Muchow
>

> _______________________________________________
> support-bugzilla mailing list
> support-...@lists.mozilla.org
> https://lists.mozilla.org/listinfo/support-bugzilla
> PLEASE put support-...@lists.mozilla.org in the To: field when you reply.

[Thorsten Schöning]

Guten Tag Marcus Polk,
am Freitag, 20. Juli 2012 um 10:28 schrieben Sie:

> I took a look at the Bugzilla queries and can certainly export to a
> CSV, but I need to automate this thing so any of the bosses, to
> include this one, can simply click on a link, have the report run
> and everything come up in their nice little spreadsheet.

Providing some kind of macro in Excel which queries Bugzilla using
http or whatever for a searched query and its CSV-exported data seems
easier to me, that dealing with databases directly.

[Marcus Polk]

Thorsten,

Thank you for the suggestion.

The problem is, it's all something I'm not familiar with.

I will Google info on your suggestion though.

Thanks again,

Marcus

[Thorsten Schöning]

Guten Tag Marcus Polk,
am Montag, 23. Juli 2012 um 05:08 schrieben Sie:

> The problem is, it's all something I'm not familiar with.

Maybe hiring a consultant is an option, I would suspect some of them
having something ready to use:

http://www.bugzilla.org/support/consulting.html

Else the following looks like what I meant:

http://blog.sos-company.com/?p=3

Public Function HTTPGet(ByVal URL As String) As String
Dim xmlhttp As Object
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

xmlhttp.Open "GET", URL

' Indicate the header length
xmlhttp.setRequestHeader "User-Agent", _
"mozilla/4.0 (compatible; MSIE 6.0)"
xmlhttp.Send

Dim counter As Double

' Wait 1 minute for the server's answer
TimeOut = Date + Time + TimeSerial(0, 0, 60)
While (Not xmlhttp.readyState = 4) And (Date + Time < TimeOut)
DoEvents
Wend

If xmlhttp.readyState = 4 Then
If xmlhttp.Status = 200 Then
HTTPGet = xmlhttp.ResponseText
ElseIf xmlhttp.Status <> 200 Then
MsgBox "Server error: " & xmlhttp.Status
End If
ElseIf xmlhttp.readyState <> 4 Then
MsgBox "Ready State error: " & xmlhttp.readyState
End If
End Function

Sub SheetUpdate()
Dim sQueryString As String
' This is the URL to the CSV file
sQueryString = "https://bugzilla.redhat.com/buglist.cgi?bug_status=NEW&bug_status=ASSIGNED&classification=Red%20Hat&component=openldap&field-1-0-0=classification&field-1-1-0=product&field-1-2-0=component&field-1-3-0=bug_status&product=Red%20Hat%20Enterprise%20Linux%205&query_format=advanced&remaction=&type-1-0-0=anyexact&type-1-1-0=anyexact&type-1-2-0=anyexact&type-1-3-0=anyexact&value-1-0-0=Red%20Hat&value-1-1-0=Red%20Hat%20Enterprise%20Linux%205&value-1-2-0=openldap&value-1-3-0=NEW%2CASSIGNED&ctype=csv"
' Send a HTTP Query to the bugzilla server
sAnswer = HTTPGet(sQueryString)
' The answer is a CSV text, split it into lines, and then into cells
aLines = Split(sAnswer, vbLf)
' Loop on the lines
For i = LBound(aLines) To UBound(aLines)
aElements = Split(aLines(i), ",")
' Loop on the elements of each line
For j = LBound(aElements) To UBound(aElements)
ActiveSheet.Range("B10").Offset((i - LBound(aLines)),
(j - LBound(aElements)))._
Formula = aElements(j)
Next j
Next i
End Sub