Re: [Perfparse-users] SQL Command

[Ben C. <bcl...@pe...>]

Hi Richard,

This is exactly why PP was developed, so I hope we can help you.

You may want to select a date format.  See the MySQL manual for date 
format.  My Office 2000 does not understand 'YYYY-MM-DD hh:mm:ss'.  Use 
your local format.  See DATE_FORMAT here:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

You now have a choice of two tables.  One for plugin output with one 
record for each sample.  The other is for performance data.  There may 
be zero, one or many performance lines for each raw output.  A LEFT 
OUTER JOIN will be required to get all data.

I don't have a PP DB to hand, but belive the tables are:

perfdata_metric_bin
perfdata_raw

use 'EXPLAIN TABLE t' where t is your table to get the fields avaiable.

Look at the above manual for syntax, but you'll need something like:

echo "SELECT DATE_FORMAT(cdata, "..."), text, state, ... FROM 
perfdata_raw LEFT OUTER JOIN perfdata_raw JOIN perfdata_metric_bin ON 
perfdata_raw.ctime = perfdata_metric_bin.ctime WHERE .... " | mysql -u 
user -ppassword -d database -H host > file_for_input_to_excel

However, to make your life easy, you may want to use a tool to get the 
data.  Like phpAdmin.

Hope this is of some help, let us know where you get to.

Ben


Ric...@eq... wrote:
> Hi All,
> My management has requested that I put together a report from the data 
> gathered by PerfParse into an Excel spreadsheet. I have the PHP script 
> that can generate Excel using the PEAR plugin 
> "Spreadsheet_Excel_Writer." But I am not very good at SQL and was hoping 
> I could find an SQL query that would suit my needs. What I want to do is 
> have an SQL query that when given a server / service will request:
> 
> 1. Date / Time
> 2. Performance Data
> 3. State
> 4. Output from the plugin.
> What I need is an SQL Query that will output like this: "2005-04-11 
> 07:30:00","13","0","USERS OK - 13 users currently logged in" Could you 
> assist with the correct SQL query? Thanks,
> 
> 
> Richard Masci
> (OSS/I&S/CAS)Network Facing Tools
> 
> ------------------------------------------------------- SF email is 
> sponsored by - The IT Product Guide Read honest & candid reviews on 
> hundreds of IT Products from real users. Discover which products truly 
> live up to the hype. Start reading now. 
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click 
> _______________________________________________ Perfparse-users mailing 
> list Per...@li... 
> https://lists.sourceforge.net/lists/listinfo/perfparse-users