Hi Tarbat
Got your report running each day now working well for me. Added it to a bat file that also adds the date to the file name. So thanks very much.
However, can you advise if I can add the time first or last recieved to each entry?
Many thanks
Graham
Do you mean the time first/last received for the AIRCRAFT or the FLIGHT on that day.
For the aircraft, there's 2 fields you could add - Aircraft.LastTime and Aircraft.FirstTime:
.output data.html
.mode list
.header OFF
SELECT DISTINCT "<HTML><HEAD><Title>Radarbox Log - Yesterday</Title>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "<STYLE type='text/css'> BODY { background: #000000; color: #FFFFFF; font-family: Arial; }</STYLE>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "</HEAD><BODY>" AS FIELD_1 FROM Aircraft;
SELECT DISTINCT "<Table Border='1' Cellpadding='4' Cellspacing='1'>" AS FIELD_1 FROM Aircraft;
.mode html
.header ON
SELECT DISTINCT Aircraft.LastTime AS "Last Time",Aircraft.FirstTime AS "First Time",Aircraft.Registration AS "Reg",Flights.Callsign AS "Flight ID",Flights.Route AS "Route",Aircraft.AircraftTypeSmall AS "ICAO Type",Aircraft.Airline AS "Airline",Aircraft.AircraftTypeLong AS "Aircraft",substr(Flights.EndTime,1,16) AS "Flight Ended",Aircraft.ModeS AS "Mode S" FROM Aircraft LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS) WHERE ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND (Flights.StartTime IS NULL)) OR ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))) ORDER BY Aircraft.Registration,Flights.EndTime,Aircraft.ModeS;
.mode list
.header OFF
SELECT DISTINCT "</Table></BODY></HTML>" AS FIELD_1 FROM Aircraft;
.exit
Complete list of fields available in MyLog:
Aircraft.Comment,
Aircraft.LastTime,
Aircraft.FirstTime,
Aircraft.AircraftTypeLong,
Aircraft.AircraftTypeSmall,
Aircraft.Airline,
Aircraft.ModeS,
Aircraft.ModeSCountry,
Aircraft.Registration,
Aircraft.ADSB,
Flights.Callsign,
Flights.EndTime,
Flights.ModeS,
Flights.MsgCount,
Flights.Registration,
Flights.Session,
Flights.StartTime,
Flights.StartPosition,
Flights.EndPosition,
Flights.StartAltitude,
Flights.EndAltitude,
Flights.StartGS,
Flights.EndGS,
Flights.Route
Tarbat - while I know C - I am unclear about SQL...
Will SQL accept $1 for a date - to be included in the title line, and possibly the output filename?
When I tried to download the scripts etc. from the link in the original thread, using a Vista System, the download stopped at approx 80% - multiple tries got a fairly consistent filesize.
Any ideas?
I was in your area - just outside Aberdeen airport yesterday afternoon, under the flight path - a fair amount of traffic, but it seems very little with ADS-B enabled - is this usual for your area?
Returning south today, will look at running the system True Mobile again!!!
Quote from: jgrloit on March 20, 2009, 10:15:33 AMWill SQL accept $1 for a date - to be included in the title line, and possibly the output filename?
Not sure. Details of what can be used in the SQL CLI are at http://www.sqlite.org/sqlite.html
Quote from: jgrloit on March 20, 2009, 10:15:33 AMWhen I tried to download the scripts etc. from the link in the original thread, using a Vista System, the download stopped at approx 80% - multiple tries got a fairly consistent filesize. Any ideas?
No idea, others downloaded okay. I've re-attached the scripts, etc. as a new file on this post.
Quote from: jgrloit on March 20, 2009, 10:15:33 AMI was in your area - just outside Aberdeen airport yesterday afternoon, under the flight path - a fair amount of traffic, but it seems very little with ADS-B enabled - is this usual for your area?
Unfortunately, yes. Most local carriers (Loganair, Flybe, BMI, etc.) aren't ADS/B equipped. This tracemap taken over the last 3 days illustrates the problem - plenty of ADS/B traffic up to Glasgow/Edinburgh, but little north of that.
(http://farm4.static.flickr.com/3624/3370436384_57e821c4a5.jpg) (http://www.flickr.com/photos/tarbat/3370436384/sizes/o/)
THanks Tarbat that link worked this time.
Will read up on the other ideas - using the date as the filename in the script or Batch!!!
Had the RB running ALL the way from Murcar at 11:15 to Hexham at 18:30 today.
True Mobile - Had Very good coverage at times all the way to Manchester - while North to Perth at one point!!!
Thanks Tarbat. This does the trick.
jgrloit, I use a shareware app I downloaded called NAMEDATE this can be added to the tarbat's sql batch file and and can be setup to rename the report.html file to include the date and time etc in the lame.
Ceers
Graham
Another Question
Occasionally get DB locks. Do you know what causes this and how to get around it?
SQL error near line 4: database is locked
SQL error near line 5: database is locked
SQL error near line 6: database is locked
SQL error near line 7: database is locked
SQL error near line 10: database is locked
SQL error near line 13: database is locked
GRaham
No way round it, other than closing Radarbox down when you run the SQL. It just means that RB was updating the database.
I've never had the database locked message when running the scripts, although I do occasionally get it when using SQLite Maestro.
Possibly running the script too close to the date rollover, midnight so there is some automatic RB updating running?
Tarbet - have you ever used Greater in SQLITE - looking at a script to list maximum height for flights in the Flight table.
Quote from: jgrloit on March 21, 2009, 01:45:13 PMTarbat - have you ever used Greater in SQLITE - looking at a script to list maximum height for flights in the Flight table.
I've used other aggregate functions such as COUNT. I would think the MAX function should work. For list of them, see http://www.sqlite.org/lang_aggfunc.html
Tarbat
Following from a few post above have a few bugs with your reporting script that gives first and last time. Have it running each night just after midnight but I have a couple of bugs. Firstly it is reporting back two days of traffic. The day previous and the day before that. Secondly it is not picking up all traffic for the day previous. Was looking for a particular aircraft and couldn't find it in the log but it is in the Airnav report. Any ideas?
Thanks
Graham
Sorry, can't see why that should happen, unless the actual Flight Data in MyLog is wrong in some way. Might be best to wait for v2.10 which uses this same SQL code for its Log Report.
I guess it could be a problem with Daylight Saving. Try running it after 1am. I don't normally have much in the air after midnight where I am :(
Hi Tarbat
Been running this script against the v3 beta version. However the db now always seems to be locked, have to close down the system to get out a report. Of course I can do this but prefer it is automatically run just after midnight.
Are you finding this?
The email at midnight in V3 now seems to work each night for me, whereas with v2.x it did not. Maybe an option is to have the "save to file" with an auto function like the email send.
Graham
Yes, I sometimes get the database locked message. Maybe you can support my suggestion to Airnav to enable us to run our own SQL code within Radarbox itself. Have an option on the log reporting screen to paste SQL code into a text box and run it. Shouldn't get the database locked problems then.
Tarbat
Count me in on that one also
I've got the db locked everytime I've run your script against v3. Only been able to get data once system has been shut down.
Certainly will support you on that one. Also making more options available for reporting. Either with SQL code or tick boxes to enable people design their own reports. I see there was another thread to get data out for a specific data. So it is something users want.
Graham
Something like this:
(http://farm4.static.flickr.com/3660/3681806626_f369ee744f_m.jpg) (http://www.flickr.com/photos/tarbat/3681806626/sizes/o/)
Blimey that was quick. Have you a direct line into the dev team ;-)
That looks very much like an update?
No, just my suggestion on how it could be implemented.
Looks just what we have been looking for, stops us having to use 3rd party tools for data manipulation