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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
<% local form, viewlibrary, page_info = ...
htmlviewfunctions = require("htmlviewfunctions")
html = require("acf.html")
%>
<style type="text/css">
#content table { border-collapse: collapse; width: 100%; }
#content table td { white-space: normal; padding-right:20px; }
</style>
<script type="text/javascript">
if (typeof jQuery == 'undefined') {
document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery-latest.js"><\/script>');
}
</script>
<script type="text/javascript">
if (typeof $.tablesorter == 'undefined') {
document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.js"><\/script>');
}
</script>
<%
local tablesortercolumns = {}
if form.value.types then
for i,t in ipairs(form.value.types.value) do
if string.match(t, "undefined") then
tablesortercolumns[#tablesortercolumns+1] = (i-1)..":{sorter: false}"
end
end
end
%>
<script type="text/javascript">
$(function(){
$("#adhocresult").tablesorter({headers: {<%= table.concat(tablesortercolumns, ", ") %>}, widgets: ['zebra']});
});
</script>
<% if form.value.result then %>
<% local header_level = htmlviewfunctions.displaysectionstart(form.value.result, page_info) %>
<% if #form.value.result.value == 0 then %>
<p>No results, try adjusting query</p>
<% else %>
<table id="adhocresult" class="tablesorter">
<thead>
<tr>
<% for i,name in ipairs(form.value.names.value) do %>
<th><%= html.html_escape(name) %></th>
<% end %>
</tr>
</thead>
<tbody>
<% for i,row in ipairs(form.value.result.value) do %>
<tr>
<% for j,name in ipairs(form.value.names.value) do %>
<td><%= html.html_escape(row[name]) %></td>
<% end %>
</tr>
<% end %>
<tbody>
</table>
<% if viewlibrary.check_permission("downloadadhocquery") then %>
<% local query = cfe({ type="hidden", value=form.value.query.value }) %>
<% local viewtype = cfe({ type="hidden", value="stream" }) %>
<% htmlviewfunctions.displayitem(cfe({type="form", value={query=query, viewtype=viewtype}, label="Download query result", option="Download", action="downloadadhocquery" }), page_info, 0) %>
<% end %>
<% end %>
<% htmlviewfunctions.displaysectionend(header_level) %>
<% form.value.names = null %>
<% form.value.types = null %>
<% form.value.result = null %>
<% end %>
<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %>
<p>This form accepts a Postgresql SELECT statement and displays the results. Examples:
<ul>
<li>This statement will return the total bytes transferred by each user for the pre-purge weblog history<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog GROUP BY clientuserid ORDER BY total DESC</pre>
<li>This statement limits the above statement to a specific range of dates (just yesterday)<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog WHERE logdatetime >= 'yesterday' and logdatetime < 'today' GROUP BY clientuserid ORDER BY total DESC</pre>
<li>This statement will return the number of requests and blocks by hour over the course of the entire usage history<pre>SELECT extract(hour from date) AS hour, sum(numrequest) AS numrequest, sum(numblock) AS numblock FROM usagestat GROUP BY extract(hour from date) ORDER BY hour</pre>
</ul>
The available database tables and descriptions are listed below.</p>
<% htmlviewfunctions.displayform(form, page_info, htmlviewfunctions.incrementheader(header_level)) %>
<% local header_level2 = htmlviewfunctions.displaysectionstart(cfe({label="Available Database Tables"}), page_info, htmlviewfunctions.incrementheader(header_level)) %>
<% local header_level3 = htmlviewfunctions.displaysectionstart(cfe({label="pubweblog and pubweblog_history"}), page_info, htmlviewfunctions.incrementheader(header_level2)) %>
<p>These tables contain the pre-purge and historical access logs respectively. The definition of the table is as follows:</p>
<pre>
(
sourcename character varying(40),
clientip inet NOT NULL,
clientuserid character varying(64) NOT NULL,
logdatetime timestamp(3) without time zone NOT NULL,
uri text NOT NULL,
bytes bigint NOT NULL,
reason text,
score integer,
shortreason text,
badyesno int,
deniedyesno int,
bypassyesno int,
wordloc text,
goodwordloc text,
selected boolean,
id int,
)
</pre>
<% htmlviewfunctions.displaysectionend(header_level3) %>
<% htmlviewfunctions.displaysectionstart(cfe({label="dbhistlog"}), page_info, header_level3) %>
<p>This table contains the database history, including such information as which log files were loaded and how many entries they contained. The definition of the table is as follows:</p>
<pre>
(
logdatetime timestamp(3) without time zone NOT NULL,
msgtext text
)
</pre>
<% htmlviewfunctions.displaysectionend(header_level3) %>
<% htmlviewfunctions.displaysectionstart(cfe({label="source"}), page_info, header_level3) %>
<p>This table contains the list of log file sources. The definition of the table is as follows:</p>
<pre>
(
sourcename character varying(40) NOT NULL,
method character varying(100) NOT NULL,
userid character varying(32),
passwd character varying(255),
source character varying(255) NOT NULL,
tzislocal boolean,
enabled boolean
)
</pre>
<% htmlviewfunctions.displaysectionend(header_level3) %>
<% htmlviewfunctions.displaysectionstart(cfe({label="usagestat"}), page_info, header_level3) %>
<p>This table contains a historical record of pages requested and blocked by hour. The definition of the table is as follows:</p>
<pre>
(
sourcename character varying(40) NOT NULL,
date timestamp(0) without time zone NOT NULL,
numrequest integer,
numblock integer
)
</pre>
<% htmlviewfunctions.displaysectionend(header_level3) %>
<% htmlviewfunctions.displaysectionend(header_level2) %>
<% htmlviewfunctions.displaysectionend(header_level) %>
|
