Reading custom MySQL table

You need additional help with phplogcon, then write into this forum.

Google Ads


Reading custom MySQL table

Postby JeanBombeur » Mon Mar 11, 2013 2:45 pm

Hi, I can't figure out how to read a custom MySQL table ; I followed this : http://loganalyzer.adiscon.com/articles ... oganalyzer

My DB mapping :
Code: Select all
ID => ID, Date_log => Date_log, Source => Source, Niveau => Niveau, EventID => EventID, IP => IP, Login => Login, Message => Message


My custom fields :
Code: Select all
Date_log    Date_log    SYSLOG_Date_log
EventID    EventID    SYSLOG_EventID
ID    ID    SYSLOG_ID
IP    IP    SYSLOG_IP
Login    Login    SYSLOG_Login
Message    Message    SYSLOG_Message
Niveau    Niveau    SYSLOG_Niveau
Source    Source    SYSLOG_Source


My custom view :
Code: Select all
uID, ID, Date_log, Source, Niveau, EventID, IP, Login, Message

I can't remove this "uid" at the beginning of this view, don't know why.

Can someone tell me what shoudl be in the "logcon_views" table of the LogAnalyzer DB when filled with a custom view ?

With all this, I get an error when parsing logs :

Debug
LogStream|SetFilter: SetFilter combined = ''.
Error
LogStreamDB|PrintDebugError: Invalid SQL: SELECT , ID, Date_log, Source, Niveau, EventID, IP, Login, Message FROM pc ORDER BY DESC LIMIT 100
Detail error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ID, Date_log, Source, Niveau, EventID, IP, Login, Message FROM pc ORDER ' at line 1
Error Code: 1064


Any idea ?
JeanBombeur
New
 
Posts: 5
Joined: Thu Jun 28, 2012 2:30 pm

Urgent Question?

  • Pulling out your Hair?
  • Wasting Time and Money?
  • Deadline Approaching?

Re: Reading custom MySQL table

Postby tmarkuk » Thu Jan 16, 2014 12:19 pm

I believe I have exactly the same error.

MySql table (SystemEvents) as follows :

Code: Select all
| ID                 | int(10) unsigned |
| ReceivedAt         | datetime         |
| DeviceReportedTime | datetime         |
| Facility           | smallint(6)      |
| Priority           | smallint(6)      |
| HostName           | varchar(255)     |
| FromHost           | varchar(255)     |
| FromHostIP         | varchar(20)      |
| Message            | text             |
| RawMessage         | text             |
| SysLogTag          | varchar(60) 
 


Fields configured as follows :

Code: Select all
|Facility            |Facility            |SYSLOG_Facility    
|FromHost    |FromHost    |SYSLOG_FromHost
|FromHostIP    |FromHostIP    |SYSLOG_FromHostIP
|HostName    |HostName    |SYSLOG_HostName
|ID                    |ID                    |SYSLOG_ID
|Message            |Message            |SYSLOG_Message
|Priority            |Priority            |SYSLOG_Priority
|RawMessage    |RawMessage    |SYSLOG_RawMessage
|Received At    |ReceivedAt    |SYSLOG_ReceivedAt
|SysLogTag    |SysLogTag    |SYSLOG_SysLogTag


dbmappings :

Code: Select all
ID => ID, Received At => ReceivedAt, Device Reported Time => DeviceReportedTime, Facility => Facility, Priority => Priority, HostName => HostName, FromHost => FromHost, FromHostIP => FromHostIP, Message => Message, RawMessage => RawMessage, SysLogTag => SysLogTag


View
Code: Select all
Column 1:    uID    SYSLOG_UID    
Column 2:    ID    SYSLOG_ID    
Column 3:    Facility    SYSLOG_Facility    
Column 4:    FromHost    SYSLOG_FromHost    
Column 5:    Received At    SYSLOG_ReceivedAt    
Column 6:    Priority    SYSLOG_Priority    
Column 7:    Message    SYSLOG_Message    


The uID field is always top in the view, whether I delete it or move it down, it always returns. I have updated to the latest stable release this morning but this has not had any impact. The debug out shows :

Code: Select all
LogStreamDB|PrintDebugError: Invalid SQL: SELECT , ID, Facility, FromHost, ReceivedAt, Priority, Message FROM `SystemEvents` ORDER BY DESC LIMIT 100
Detail error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ID, Facility, FromHost, ReceivedAt, Priority, Message FROM `SystemEvents` ORDER' at line 1
Error Code: 1064


The issue seems to be the 'ORDER BY DESC LIMIT 100', I believe 'ORDER BY' should be followed by a field before it is ordered and limited, e.g. 'ORDER BY ReceivedAt DESC LIMIT 100'.

In the Apache2 error log I see the following :

Code: Select all
Undefined index: uID in /var/www/classes/logstreamdb.class.php on line 1729
Undefined index: uID in /var/www/classes/logstreamdb.class.php on line 1770


I see these errors if I try to view the events or if I try to remove the uID field from the views. For reference those lines are :

Code: Select all
 $sqlString = "SELECT " . $dbmapping[$szTableType]['DBMAPPINGS'][SYSLOG_UID];
$sqlString .= " ORDER BY " .  $dbmapping[$szTableType]['DBMAPPINGS'][$szSortColumn] . " DESC";


I tried creating new DBmapping, the same as above but with 'uID (SYSLOG_UID) > ID', this has slightly improved things by changing the SQL query to :

Code: Select all
LogStreamDB|CreateMainSQLQuery: Created SQL Query:
SELECT ID, Facility, FromHost, ReceivedAt, Priority FROM `SystemEvents` ORDER BY ID DESC LIMIT 100
Error    
LogStreamDB|PrintDebugError: Invalid SQL: SELECT ID, Facility, FromHost, ReceivedAt, Priority FROM `SystemEvents` WHERE ID <= ORDER BY ID DESC LIMIT 100
Detail error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY ID DESC LIMIT 100' at line 1
Error Code: 1064


So the uID field seem to be a requirement, but unfortunately all the rows returned are empty, apache shows the following error :

Code: Select all
Undefined index: ID in /var/www/classes/logstreamdb.class.php on line 597


I'd appreciate any guidance on how to get around this, the system seems to require uID in some form but I don't seem to be able to provide what it is looking for.
tmarkuk
New
 
Posts: 3
Joined: Wed Jul 10, 2013 4:46 pm

Re: Reading custom MySQL table

Postby friedl » Thu Jan 16, 2014 12:38 pm

In your DPMappings in LogAnalyzer, please try mapping the uID field to the ID field in your database. Consequently, in your view, just use the uID field there.
friedl
Adiscon Support
 
Posts: 67
Joined: Wed Sep 13, 2006 2:31 pm

Re: Reading custom MySQL table

Postby tmarkuk » Thu Jan 16, 2014 1:12 pm

Hi,

I have tried creating a new dbmapping as noted in my original post :

I tried creating new DBmapping, the same as above but with 'uID (SYSLOG_UID) > ID', this has slightly improved things by changing the SQL query to :

Code: Select all
LogStreamDB|CreateMainSQLQuery: Created SQL Query:
SELECT ID, Facility, FromHost, ReceivedAt, Priority FROM `SystemEvents` ORDER BY ID DESC LIMIT 100
Error   
LogStreamDB|PrintDebugError: Invalid SQL: SELECT ID, Facility, FromHost, ReceivedAt, Priority FROM `SystemEvents` WHERE ID <= ORDER BY ID DESC LIMIT 100
Detail error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY ID DESC LIMIT 100' at line 1
Error Code: 1064


As noted it has improved things, I get an empty result back which is better than no result at all, but it is still creating a bad SQL query.

So the uID field seem to be a requirement, but unfortunately all the rows returned are empty, apache shows the following error :

Code: Select all
Undefined index: ID in /var/www/classes/logstreamdb.class.php on line 597

tmarkuk
New
 
Posts: 3
Joined: Wed Jul 10, 2013 4:46 pm

Google Ads



Return to Help

Who is online

Users browsing this forum: No registered users and 1 guest

cron