URL Logging and resetting it

[HWA]

Hello again,

As before, this product is great! It's working *almost* perfectly for us. There's one more thing we need.

Here's a function (feature?) we'd like to see, and I would assume most people who use URL tracking would, too. I've looked through the manual, and although there is a way to manually do it, I'd like it to be automatic.

Can we use a script of some flavor to save the URL log and then clear it?

I'd prefer to save it to a file using the name YYYYMMMDD.url.log or something like that. This makes it easily sortable by date, and ensures it won't conflict with any other exported information. We can then write a very easy command script to move the file off the server to a file store somewhere.

There may be something I missed, as I'm sure you folks thought of this, and if so, please feel free to point me towards a spot in the manual, but I couldn't find it.

We're running the latest release.

Thanks very much...

[alan]

To clear the URL log, you can click "Clear URL log" button within Configuration Manager, or replace the file fsurl.dbf under FirstSpot\datasource directory with the original blank one from the original zip trial package (you can back it up first using different filename).
_________________
~ Patronsoft Limited ~

[alan]

Due to a server migration in our web site, your last posting is lost.

So is everything okay?
_________________
~ Patronsoft Limited ~

[HWA]

Sorry the last one was lost. I was monitoring the replies, and saw the number didn't change, so it took a while to find this :)

Anyway.....The question was:

If we setup a script to perform the copy/replace on the .dbf file, will that mess up the servers function in any way, or will FS still operate correctly?

I don't want to set up a script, and then find out that FS lost contact with the database or something silly and stopped logging.

Thanks...this is still a great product, and we recommend it to customers who may have need for this type of solution.

[alan]

For this file fsurl.dbf (which will really an ODBC table fsurl), it is fine. For other tables though (e.g. fsusr), it might affect the data consistency.
_________________
~ Patronsoft Limited ~

[HWA]

OK, that will work for saving data, and replacing it with an empty file, but is there a way to automate the export step, so we can look at the database as a flat file (i.e. in Notepad, Excel or something simple) like the export gives us?

Normally, we simply import the .csv files into Excel for sorting and searching. This works very well and also gives us an easy way to verify URL's, so we can block certain sites, or at least report who's going where. For hotel guests it's not that important, but for the staff, it is.

BTW: I'm working on a .cmd script to rename the data file, and I'll post it here for anyone else who want to do this.

[alan]

To extract data from dbf file, you can:

1) use Microsoft Access
2) use ODBC interface

See http://patronsoft.com/forum/viewtopic.php?t=1514 for a guideline to manipulate dbf file.

Also, I think you can open dbf file directly from Excel. Just don't change anything (to avoid corruption).
_________________
~ Patronsoft Limited ~

[HWA]

OK, I've figured it out. In reality, it's a very short file, but I've put in comment lines to make it easier to figure out - and modify. There are a couple req's, so read to the bottom before trying to use it.

Here's the .cmd file:

echo off
rem
rem The next line creates a small file with the date information in it. Depending on
rem your system settings, the date format may change. Our date format looks like
rem Fri 12/31/2007, and is in MM/DD/YYYY format
rem
echo %date% > date.out
rem
rem This line sets the "FileYear" variable to equal the year
rem
for /F "tokens=4 delims=/ " %%y in (date.out) do set FileYear=%%y
rem
rem This line sets the "FileMonth" variable to equal the month
rem
for /F "tokens=2 delims=/ " %%y in (date.out) do set FileMonth=%%y
rem
rem This line sets the "FileDay" variable to equal the day
rem
for /F "tokens=3 delims=/ " %%y in (date.out) do set FileDay=%%y
rem
rem This is the rename line. Renaming a file is much quicker than copying
rem
rename fsurl.dbf %FileYear%_%FileMonth%_%FileDay%_URL.log
rem
rem Now we copy the source file to the fsurl.dbf
rem
copy fsurl.dbf.src fsurl.dbf
rem
rem Now we move (copy, then delete) the backup log file
rem
copy %FileYear%_%FileMonth%_%FileDay%_URL.log c:\URLLog
del %FileYear%_%FileMonth%_%FileDay%_URL.log
rem
rem Now, finally, we clean up and delete the date.out file we created in the beginning.
rem
del date.out

Now, the requirements:
- Copy the original source .DBF file (fsurl.dbf) from the distribution folder, and make sure you rename it to "fsurl.dbf.src" Be careful not to just overwrite the existing file. Rename it BEFORE you copy it.
- Create a folder named "URLLog" in the root of your "C:" drive (you can change the script to put them anywhere you want)
- Once you create the .CMD file (mine is named "URLBackup.cmd" but you can name it whatever you want), set up a scheduled task to run it one minute before (or after) you reset the usage counters (which is optional, of course).
- Test the "Date" function. Run "echo %date%" from the command line and make sure you're getting the expected result. If you're not, then the script will need to be modified.

Once you create the file, if it were me, I'd run it to make sure it works as expected before setting up the scheduled task. The only thing I can see that would cause a problem is a carriage return in the file where I didn't put one.

Of course, you can modify the script to do whatever you want, wherever you want. I just put it in the "C:\Program Files\FirstSpot\datasource" folder, and let it run from there.

Feel free to ask questions, and I'd love to know if anyone else has this need. I feel it should be built in, as it's a pretty simple function.

v/r
Scott
Hatfield Wass & Associates
(support@hwatech.com)

[gsgriffin]

Scott,

I had the same need but with a url database that grows by about 20MB per week. I needed to create a solution that allowed non-admin types to review the data. I put together a simple PHP page that would load the data, create an HTML page, and then save that file to a log directory. This page is scheduled to run just before an automated shutdown of FS and then a simple script to copy the original datasource file over the working on. I do this automatically once a week at 3am in the morning. Now there is a directory on a shared drive that those with access can go to and pull up any of the "archived" url logs...just saved as an html page each.

Your solution works very well, but an approach that will keep you out of the loop and reduce your work is to turn it into HTML and let those that want the information get it without you doing anything.
_________________
Greg

[HWA]

However, as I'm not all THAT familiar with writing scripts, perhaps you could help by posting a solution? That would be helpful.

Thanks,
Scott

[gsgriffin]

Here. Try this at your own risk...as I'm sure the forum would like to have said.

Copy the entire code below (in between the <?php and ?>) into a blank page of Notepad and save as a .php file. Sometimes Notepad wants to save it as a .txt. You may even have to open a different PHP file, replace the entire contents with the lines below and then save as a new file name. Save it in the FirstSpot/cfgmgr/ folder. You can run it anywhere using the x.x.x.x:5787/filename.

You will also want to create a .csv file using Excel or whatever you like. Doing so will replace the MAC numbers recorded by the URL log with your text description of the computer. I found this handy in easily being able to see which computer was used. The .csv file should be called "CompList.csv" and also placed in the cfgmgr folder. Here's the file I have that you can place into Excel or Notepad to start yours. Keep the format exactly the same. Save as a .csv.

Co-Leader,00-1A-4D-3C-A2-D8,0.0.0.0
Campus Services,00-01-02-A2-0C-D7,10.20.7.18
DTS Dorm,00-00-E8-E5-27-5F,10.20.7.16
Dining Room 1 ,00-50-BF-43-32-7D,10.20.7.11
Dining Room 2 ,00-D0-B7-49-86-60,10.20.7.12
Dining Room 3,00-00-E8-EC-34-17,10.20.7.13


Here's the PHP code...a freebe


<?
//include_once("secureLogin.php");
include_once("..\\authserv\\common_functions.php");
include_once("cmvar.php");

// edited 3 September, 2003
//DSN=FirstSpot;DefaultDir=C:\PROGRAM FILES\FIRSTSPOT\DATASOURCE;
//DriverId=533;
//FIL=dBase 5.0;
//MaxBufferSize=2048;
//PageTimeout=5;
//SELECT fsurl.NAME, fsurl.LOGTIME, fsurl.MAC, fsurl.URL FROM fsurl WHERE (fsurl.URL Like '%xxx%') OR (fsurl.URL Like '%porn%') OR (fsurl.URL Like '%sex%') OR (fsurl.URL Like '%tit%') OR (fsurl.URL Like '%boob%') OR (fsurl.URL Like '%ass%' And fsurl.URL Not Like '%embassy%' And fsurl.URL Not Like '%passport%' And fsurl.URL Not Like '%asset%') OR (fsurl.URL Like '%hardcore%') OR (fsurl.URL Like '%fuck%') OR (fsurl.URL Like '%teen%' And fsurl.URL Not Like '%christian%') OR (fsurl.URL Like '%puss%') ORDER BY fsurl.NAME

// create COM object

$settingobj = new COM("FSCONFIG.Cfgmgr") or die("urlreview2.php FSCONFIG.Cfgmgr Object cannot be created!");
$dsn_file = $settingobj->getKey( "Datasource", "gateway");
$dsn_source=fopen($dsn_file, "r") or die("Cannot open DSN source file!");
$dsn="";


if (!feof($dsn_source))
$line=fgets($dsn_source,512);

while (!feof($dsn_source)){
$line=fgets($dsn_source,512);
if (trim($line)!="")
$dsn=$dsn.$line;
}

$dsn=str_replace("\r\n",";",$dsn);
$userDB=odbc_connect($dsn,"","");
if (isset($order))
{
switch ($order)
{
case "name" :
$orderby="ORDER BY NAME, LOGTIME";
break;
case "computer" :
$orderby="ORDER BY MAC, LOGTIME";
break;
case "url":
$orderby="ORDER BY URL, LOGTIME";
break;
default :
$orderby="";
break;
}
} else {
$orderby="";
}
if($userDB)
{
// user log
$firstspotURLlogtable=$settingobj->getKey( "Datasource_URL_Tablename", "gateway");
$query1 = "SELECT fsurl.NAME, fsurl.LOGTIME, fsurl.MAC, fsurl.URL FROM fsurl ".$orderby;

$result=odbc_exec($userDB,$query1);


$thisfilename = 'CompList.csv';

if (file_exists($thisfilename))
// line 50
{
$theselines = file($thisfilename);
$total_lines = count($theselines);
for ($l = 1; $l < $total_lines; $l++)
{
$this_line = trim($theselines[$l-1]);
$lineparts = explode(",",$this_line);
$macnumber[$l] = trim($lineparts[0]);
$computername[$l] = trim($lineparts[1]);
}
}

function GetComputerName($MacNum)
{
global $macnumber;
global $computername;
$foundposition = array_search($MacNum, $macnumber);

If ($foundposition != '')
return $computername[$foundposition];
else
return $MacNum;
}

// Query Normal Login Log
$htmlpage = "<html>
<head>
<title>User Log</title>
<style type='text/css'>
<!--
table { font-family: Arial, sans-serif; font-size: 10pt; }
-->
</style>
</head>
<body>
<p>Select ORDER of report:
<a href='completeurl.php?order=date'>By Date</a>,
<a href='completeurl.php?order=name'> By Name</a>,
<a href='completeurl.php?order=url'> By URL</a>,
<a href='completeurl.php?order=computer'>Group By Computer</a>
<br>
<hr>
<table width='900' border='0' cellspacing='0' cellpadding='0'>
<tr>

<td width='70' valign='top'>User Name</td>
<td width='150' valign='top'>Computer</td>
<td width='200' valign='top'>Login Time</td>
<td width='100' valign='top'>URL Visited</td>
</tr>";

while(odbc_fetch_row($result)){
$name=odbc_result($result,"NAME");
$logintime=odbc_result($result,"logtime");
//line 100
$macIP=odbc_result($result,"MAC");
$urlsite=odbc_result($result, "URL");


// $macIP_str=make_mac_name($macIP);
$macIP_str=str_replace(":","-", strtoupper($macIP));

// added the following to put in real names for the computer rather than a MAC address
// gets the values from the CompList.txt file
if(isset($macnumber))
$ComputerName = GetComputerName($macIP_str);
else
$ComputerName = $macIP_str;

$htmlpage .= "<tr>

<td width=\"100\">".$name."</td>
<td width=\"150\">".$ComputerName."</td>
<td width=\"200\">".$logintime."</td>
<td width=\"300\">".$urlsite."</td>\n</tr>\r\n";
}

$htmlpage .= "</table></body></html>";
odbc_close($userDB);
echo $htmlpage;
if(isset($archive))
{
$archdir='c:/Program Files/FirstSpot/authserv/logs/';
$maindir='c:/Program Files/FirstSpot/cfgmgr/';

$urlfilter=$maindir."URLreview.php";
$completeurl=$maindir."CompleteURL.php";

$date = getdate(date("U"));
$today = $date['mday']."-".$date['mon']."-".$date['year'];
$urlfile=fopen($archdir.$today." Complete URL Listing Archive.html","w+");
fwrite($urlfile,$htmlpage);
fclose($urlfile);
echo "The above information has been archived";
}

}else{
echo "connection failed!";
}

?>
_________________
Greg

[HWA]

Awesome Greg, thanks a bunch....

Scott

[gsgriffin]

I just noticed that there are some remarked lines at the top of the code that have explicit language in them. I apologize for that! That was left over from the script that I had put together to filter for porn sites for the organization that was using this. They wanted to make sure that porn was not being viewed. Please delete those lines that have // at the beginning so those words don't come back to haunt you.
_________________
Greg