I have a script that polls a MySQL database (Nagios, specifically) for the date/time of events. I want to count how many events occur in each hour based upon all of the data my script returns.
Here is what the results look like: (each on their own line)
2010-03-01 03:20:26
2010-02-28 19:07:26
2010-02-28 00:50:37
2010-02-27 17:07:35
2010-02-27 17:06:35
Here is the bash script that I use to get what I'm looking for:
cat temp |awk '{print $2 $0}' | cut -d: -f1 | sort | uniq -c |sort -k2 -n
Here are some sample results:
21 00
32 01
31 02
46 03
34 04
12 05
11 06
8 07
107 08
56 09
16 10
55 11
50 12
33 13
23 14
34 15
11 16
18 17
14 18
25 19
9 20
5 21
38 22
20 23
So, you can see from the above results that the little bash command parses my PHP script' s output and returns how many occurrences in column 1 and what hour of the day they occurred at in column 2.
I need to incorporate this into my PHP script.
Here is my code right now that retrieves it from the Nagios DB:
$query2= "SELECT * FROM nagios_statehistory WHERE ".
"object_id='$tosearch' AND output='CRITICAL - Socket timeout " .
"after 10 seconds' ORDER BY state_time DESC";
$result2 = mysql_query($query2) or die (mysql_error());
while($row = mysql_fetch_array($result2)) {
$outtie = substr( $row['state_time'] , 0 , 10);
if ($outtie !== $lastout) {
//bolds the first occurrence of each day
echo "<b>";
}
/* print the actual time. This is what I'm looking
* to count the occurrences of */
echo $row['state_time'];
$lastout = substr( $row['state_time'] , 0 , 10);
//disable bold and insert a line break
echo "</b><br />";
}