Information: Forum is in read-only mode
For details and other support options see https://www.adiscon.com/news/support-forum-set-to-read-only-mode/

DB Mapping

General discussions here

Moderator: alorbach

Google Ads


DB Mapping

Postby mhammett » Sun Apr 03, 2011 3:28 pm

I setup rsyslog as per the instructions on their site.

I setup LogAnalyzer as per the instructions on their site.

The statistics section was populated, but the events were not. I did some investigating and the MySQL schema was in mixed case versus lower case. I couldn't just modify the default MonitorWare mapping, so I worked to create a new one.

Not every field in the database has a home in the DB Mapping, nor does every field in the DB Mapping have a sister field in the schema. A couple fields in the MonitorWare default mapping aren't present in the schema, either.

No matter what I do, I cannot get the EventID field to stay populated. Anytime I try to continue, it erases EventID from the field, then complains that it doesn't exist.

I'll attempt to forge ahead, but I would appreciate any insight.

mysql> describe SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | | NULL | |
| Message | text | YES | | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
mhammett
New
 
Posts: 6
Joined: Sun Jan 24, 2010 5:08 am

Urgent Question?

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

Re: DB Mapping

Postby mhammett » Sun Apr 03, 2011 3:38 pm

I forged ahead without the EventID entry and it created the mapping, but still no events. I put the EventID back in and it will pass it now, only it changes EventID to 1.
mhammett
New
 
Posts: 6
Joined: Sun Jan 24, 2010 5:08 am

Re: DB Mapping

Postby mhammett » Mon Apr 04, 2011 12:49 am

I found out that the problem was that I didn't provide the user with alter privileges.
mhammett
New
 
Posts: 6
Joined: Sun Jan 24, 2010 5:08 am

Re: DB Mapping

Postby mhammett » Mon Apr 04, 2011 12:51 am

When I went to delete the DBMapping I created that I didn't need, I encountered the following error, while in debugging mode.
Code: Select all

Critical Error occured

Errordetails:   
Automatic redirect to the page was aborted, as an internal error occured. Please see the error details above and contact our support forums if you need assistance.



Ideas?
mhammett
New
 
Posts: 6
Joined: Sun Jan 24, 2010 5:08 am

Re: DB Mapping

Postby alorbach » Mon Apr 04, 2011 9:46 am

Hi,

are you trying to delete one of the inbuild database mappings? Because these are hardcoded and cannot be deleted.
However LogAnalyzer does automatically add missing database fields according to their mapping if they are missing.
Does LogAnalyzer has full access to the UserDB Database? If not it might not be able to delete configuration records which could explain the deletion error.

Perhaps you should take a look to this Stepbystep guide on how to use LogAnalyzer and EventLog monitoring:
http://www.mwagent.com/step-by-step-gui ... dowslinux/

best regards,
Andre Lorbach
alorbach
Site Admin
 
Posts: 1627
Joined: Thu Feb 13, 2003 11:55 am

Re: DB Mapping

Postby mhammett » Mon Apr 04, 2011 1:43 pm

You would be correct. The LogAnalyzer user didn't have delete privileges.

I think installation guides should always include the minimum required privileges.
mhammett
New
 
Posts: 6
Joined: Sun Jan 24, 2010 5:08 am

Re: DB Mapping

Postby alorbach » Mon Apr 04, 2011 3:16 pm

mhammett wrote:You would be correct. The LogAnalyzer user didn't have delete privileges.

I think installation guides should always include the minimum required privileges.


This should be added indeed. The LogAnalyzer user should have full access to the UserDB anyway, it may be needed for database upgrades in future.

best regards,
Andre Lorbach
alorbach
Site Admin
 
Posts: 1627
Joined: Thu Feb 13, 2003 11:55 am

Re: DB Mapping

Postby joelserrano » Fri Jan 24, 2014 3:20 pm

mhammett wrote:I forged ahead without the EventID entry and it created the mapping, but still no events. I put the EventID back in and it will pass it now, only it changes EventID to 1.


Hi,

I've arrived to the same situation here...

This is my database syslog table:

Code: Select all
mysql> desc SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| ID                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| CustomerID         | bigint(20)       | YES  |     | NULL    |                |
| ReceivedAt         | datetime         | YES  |     | NULL    |                |
| DeviceReportedTime | datetime         | YES  |     | NULL    |                |
| Facility           | smallint(6)      | YES  |     | NULL    |                |
| Priority           | smallint(6)      | YES  |     | NULL    |                |
| FromHost           | varchar(60)      | YES  |     | NULL    |                |
| Message            | text             | YES  |     | NULL    |                |
| NTSeverity         | int(11)          | YES  |     | NULL    |                |
| Importance         | int(11)          | YES  |     | NULL    |                |
| EventSource        | varchar(60)      | YES  |     | NULL    |                |
| EventUser          | varchar(60)      | YES  |     | NULL    |                |
| EventCategory      | int(11)          | YES  |     | NULL    |                |
| EventID            | int(11)          | YES  |     | NULL    |                |
| EventBinaryData    | text             | YES  |     | NULL    |                |
| MaxAvailable       | int(11)          | YES  |     | NULL    |                |
| CurrUsage          | int(11)          | YES  |     | NULL    |                |
| MinUsage           | int(11)          | YES  |     | NULL    |                |
| MaxUsage           | int(11)          | YES  |     | NULL    |                |
| InfoUnitID         | int(11)          | YES  |     | NULL    |                |
| SysLogTag          | varchar(60)      | YES  |     | NULL    |                |
| EventLogType       | varchar(60)      | YES  |     | NULL    |                |
| GenericFileName    | varchar(60)      | YES  |     | NULL    |                |
| SystemID           | int(11)          | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)

mysql>



I've created a new database mapping called "rsyslog" and when I all add the mappings, EventID is always set to 1.

What I've ended up doing is to update the mapping directly through the database:

Before:
Code: Select all
mysql> select * from logcon_dbmappings;
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | DisplayName | Mappings                                                                                                                                                                                                                                                                                         |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | rsyslogd    | uID=>ID,timereported=>DeviceReportedTime,FROMHOST=>FromHost,IUT=>InfoUnitID,syslogfacility=>Facility,syslogseverity=>Priority,syslogtag=>SysLogTag,NTEventLogType=>EventLogType,sourceproc=>EventSource,category=>EventCategory,user=>EventUser,id=>1,misc_systenid=>SystemID,msg=>Message |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


Update the EventID mapping:
Code: Select all
mysql> update logcon_dbmappings set Mappings = 'uID=>ID,timereported=>DeviceReportedTime,FROMHOST=>FromHost,IUT=>InfoUnitID,syslogfacility=>Facility,syslogseverity=>Priority,syslogtag=>SysLogTag,NTEventLogType=>EventLogType,sourceproc=>EventSource,category=>EventCategory,user=>EventUser,id=>EventID,misc_systenid=>SystemID,msg=>Message' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>


And now everything is correct:

Code: Select all
mysql> select * from logcon_dbmappings;
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | DisplayName | Mappings                                                                                                                                                                                                                                                                                         |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | rsyslogd    | uID=>ID,timereported=>DeviceReportedTime,FROMHOST=>FromHost,IUT=>InfoUnitID,syslogfacility=>Facility,syslogseverity=>Priority,syslogtag=>SysLogTag,NTEventLogType=>EventLogType,sourceproc=>EventSource,category=>EventCategory,user=>EventUser,id=>EventID,misc_systenid=>SystemID,msg=>Message |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>



Any idea why EventID is always set to 1?

Thanks in advanced.

Best regards,
Joel.
joelserrano
New
 
Posts: 6
Joined: Thu Jun 25, 2009 3:57 pm

Re: DB Mapping

Postby joelserrano » Fri Jan 24, 2014 3:43 pm

Doing some further testing....

1. If I use the dbmapping I just created (with the EventID set to 1) I can't see any events.
2. If I use the modified dbmapping from above (with EventID set to "EventID" manually by sql update query) I can't see any events either.

3. If I use my rsyslog default "SystemEvents" table selecting as type "MonitorWare" I can see all events perfectly.

So, I've ended up deleting the just created database mapping as it is useless and using the default "MonitorWare" did the trick.

I don't understand why thouth...Is there any place I can double check?

I've enabled the syslog debug:

With my custom dbmapping the query is:
Code: Select all
Jan 24 14:30:51 syslog loganalyzer - - - LogStream|SetFilter: SetFilter combined = ''.
Jan 24 14:30:51 syslog loganalyzer - - - LogStreamDB|CreateMainSQLQuery: Created SQL Query:<br>SELECT ID, DeviceReportedTime, Facility, Priority, FromHost, SysLogTag, InfoUnitID, Message FROM `SystemEvents` ORDER BY ID DESC LIMIT 100


Using "MonitorWare" dbmapping the query is:
Code: Select all
Jan 24 14:31:13 syslog loganalyzer - - - LogStream|SetFilter: SetFilter combined = ''.
Jan 24 14:31:13 syslog loganalyzer - - - LogStreamDB|CreateMainSQLQuery: Created SQL Query:<br>SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, processid, infounitid, message FROM `SystemEvents` ORDER BY id DESC LIMIT 100


The only difference I see is the upper and lower case column names, and that MonitorWare includes "processid" but neither explain why my dbmapping didn't work and the MonitorWare works perfectly!

Any clues?

Best regards,
Joel.
joelserrano
New
 
Posts: 6
Joined: Thu Jun 25, 2009 3:57 pm

Re: DB Mapping

Postby dan0765 » Mon Mar 24, 2014 5:29 pm

Hi...just had this problem myself and figured out it is caused by a field name clash.

If you click the "Fields" tab in Admin Center you will notice that the FieldID associated with SYSLOG_EVENT_ID is called "id".

Now if you hover over any of the clickable links within the "DBMappings" dialogue, the URL displayed in your taskbar will look something like this:

....dbmappings.php?op=edit&id=1

the value used in "id=" (1 in this example) actually relates to the ID of whatever DB mapping you are currently editing.

Basically, what seems to be happening is that the php script is confusing the "id" passed in via the URL with the "id" associated with SYSLOG_EVENT_ID. So, if you happen to be editing a DB mapping with an ID of "1" then you will find SYSLOG_EVENT_ID keeps getting set to "1". If you are editing a DB mapping with an ID of "2" then the SYSLOG_EVENT_ID will keep getting set to "2" and so on...

I fixed it by changing the name of the FieldID associated with SYSLOG_EVENT_ID to something a bit more unique. This is defined in file /include/constants_logstream.php under your installation directory. You will see a line that looks like this:

define('SYSLOG_EVENT_ID', 'id');

I just changed this line as follows:

define('SYSLOG_EVENT_ID', 'evtid');

Seems to have done the trick!
dan0765
New
 
Posts: 6
Joined: Wed Feb 12, 2014 11:12 am

Re: DB Mapping

Postby alorbach » Thu Mar 27, 2014 9:57 am

This is a good catch and definitely a BUG. I will look into this, so we can get a propper fix for this.
Changed SYSLOG_EVENT_ID will break logs from Windows Eventlog.

best regards,
Andre Lorbach
alorbach
Site Admin
 
Posts: 1627
Joined: Thu Feb 13, 2003 11:55 am

Re: DB Mapping

Postby jaimet561 » Thu Jul 14, 2016 11:21 pm

Hi everyone,

I want to know how to create a Database Mapping? is it done through the Browser's interface or is there a config file that we must change?
I have a database named CSVLOGS and i want adiscon to read from it and show the fields. How can i do this?
OH, table name is postgres_logs. (Yes, i have the logs of postgres in there)

Can anyone help me please.

Thank you
jaimet561
Avarage
 
Posts: 20
Joined: Fri Jun 10, 2016 4:10 pm

Google Ads



Return to General

Who is online

Users browsing this forum: No registered users and 2 guests

cron