Full Individual Report??

[jjj]

The following was found on the forum somewhere:
----

Here are the SQL statements I used to explore http traffic from a particular client:


SET search_path = reports, events;

SELECT W.time_stamp, R.host, L.uri
FROM n_http_req_line L
INNER JOIN n_http_evt_req R
ON L.request_id = R.request_id
INNER JOIN webpages W
ON L.request_id = W.request_id
WHERE W.c_client_addr = '***** IP ADDRESS ****'
ORDER BY W.time_stamp DESC;

Simply replace the ***** IP ADDRESS **** with the local ip address (eg. 192.168.1.x) that is of interest to you.

[joemailey]

The following was found on the forum somewhere:
----

Here are the SQL statements I used to explore http traffic from a particular client:


SET search_path = reports, events;

SELECT W.time_stamp, R.host, L.uri
FROM n_http_req_line L
INNER JOIN n_http_evt_req R
ON L.request_id = R.request_id
INNER JOIN webpages W
ON L.request_id = W.request_id
WHERE W.c_client_addr = '***** IP ADDRESS ****'
ORDER BY W.time_stamp DESC;

Simply replace the ***** IP ADDRESS **** with the local ip address (eg. 192.168.1.x) that is of interest to you.

You my friend are a genius, I've been after something like this for ages.
Will test this on Monday when I'm back to work.

[jjj]

So I figured I would start here....

Updated to 7.0 and now back-end SQL access is broken.... Anyone know what happened?

[dmorris]

So I figured I would start here....

Updated to 7.0 and now back-end SQL access is broken.... Anyone know what happened?

the schema changed.

you can now download this information in a CSV file through the reports UI anyway...

[Mathiau]

the csv just shows the same info on the GUI though, it doesnt add anything else? I love the new reporting and that it is reliable, but it seems to be providing alot of numbers that mean very little to me or dont tell me what i want to know.

[joemailey]

the csv just shows the same info on the GUI though, it doesnt add anything else? I love the new reporting and that it is reliable, but it seems to be providing alot of numbers that mean very little to me or dont tell me what i want to know.

What is missing?
CSV shows everything including time stamps etc.

[Mathiau]

i am going into Reports / View Reports / Hosts - choose a host

then i go to web filter, cause i want to see all the sites the people have viewed or the URL for sites they tried to get to as my main fight is stopping websites that allow people to sign into IM's like meebo.com or imo.im.

now say under top 10 sites of violated sites it has categories for some like Proxies



Proxy Sites

357 hits

all it says, i check the CVS file, says the same thing in it, nothing extra, has slice and value.

i want to see, like you could in 6.2 an actual list of the sites they went to.