Anonymous_User Absent Member.
Absent Member.
350 views

Several repeated records with SQL Server Collector


Good afternoon, I have some Microsoft SQL Server 6.1r2 collectors
installed because some applications send the events to a SQL Server
database. Everything is find except for the fact that the execution of
the query stored in the sqlquey.base file shows me a lot of repeated
records. If I execute the query in the database directly, it generates
no repeated records so I do not know what the problem is. I have tried
using TOP 1, DISTINCT, GROUP BY, etc instructions with no success.
Please help me.


--
apinzon
------------------------------------------------------------------------
apinzon's Profile: http://forums.novell.com/member.php?userid=101459
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
6 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector


I am having a similar issue with a Symantec Endpoint Protection
Collector i wrote. my problem is that the sql query seems to always
start from the beginning of the table in the SQL Database. Due to this I
keep running out of disk space on the server. I have tried setting
offsets based on date/time or event unique IDs but this doesnt seem to
help.

How did you fix your issue ?


--
kademolu1
------------------------------------------------------------------------
kademolu1's Profile: http://forums.novell.com/member.php?userid=104108
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector


Hi there,

The current shipping SEP Collector only supports parsing the files
directly from the SEP endpoint agents, because that's what was requested
by a particular customer that did not use the SEP backend bits.

We're currently working on adding support for syslog output from SEP,
and the one or two customers we've talked to about this seemed to think
that would be sufficient.

Is there any particular reason you're using the DB integration instead
of syslog? Do you know of any feature differences between the data you
can capture from one vs. the other?

Oh, and my advice to apinzon applies to you as well - we need to see
your sqlquery.base and your conn.addParser() method.


--
DCorlette
------------------------------------------------------------------------
DCorlette's Profile: http://forums.novell.com/member.php?userid=4437
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector


Hi,

Thanks for your reply. I am using DB integration mainly because the
Schema and events are better documented than in the syslog log format. I
started writing a syslog collector but because I could find no
documented list of the event codes i had to drop it.

I now have the DB collector working fairly properly, i was using an
Index that was in hex format previously as my offset and that didnt
work, I am now using a Timestamp value that works better.

My collector currently parses Virus and Port Security events
(unauthorised USB use etc) and the only issues i am having is the delay
in polling for new events. It takes at least 5Mins for a new event to
show up in the dashboard.

If the syslog collector is ready i can help in Beta testing it, we have
a fairly large SEP deployment and rather strict Security monitoring so
we would be an ideal use case.


--
kademolu1
------------------------------------------------------------------------
kademolu1's Profile: http://forums.novell.com/member.php?userid=104108
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector


OK, so there are several parts to this, and it's not clear from your
post what you've actually done.

First, you have to construct your query correctly. TOP N is part of it
- you absolutely need that in your query, and the N part should probably
be '%d' and that should be tied to the MaxRows parameter, which has some
nice automatic sizing behavior.
But you also need to have a WHERE clause which specifies your offset -
something like:
WHERE RowNumber > %s ORDER BY RowNumber
The '%s' will be replaced by the offset stored in the Event Source.

Can you post:
1) The contents of your sqlquery.base file
2) The method you're passing to conn.addParser()


--
DCorlette
------------------------------------------------------------------------
DCorlette's Profile: http://forums.novell.com/member.php?userid=4437
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector

Hi,
in a recent PoC the customer used my MySQL DB for Symantec Threat Reporter.
We used the following sqlqeury:
SELECT
alerts.Idx Id
, alertmsg.alert EventName
, clientuser.clientuser DestUserName
, virus.category Severity
, virus.virusname Virus
, alerts.description Message
, inventory.computer DestHostName
, inventory.ip_address DestIP
, alerts.Filepath FileName
, alerts.alertdatetime TimeStamp
FROM alerts
,alertmsg
, clientuser
, virus
, actualaction
, inventory
WHERE alerts.alert_idx = alertmsg.alert_idx
and alerts.clientuser_idx = clientuser.clientuser_idx
and alerts.virusname_idx = virus.virusname_idx
and alerts.actualaction_idx = actualaction.actualaction_idx
and alerts.computer_idx = inventory.computer_idx
and alerts.Idx > %s
ORDER BY alerts.Idx
LIMIT %d


Norbert

>>> On 28.07.2011 at 04:56, DCorlette<DCorlette@no-mx.forums.novell.com>

wrote:

> OK, so there are several parts to this, and it's not clear from your
> post what you've actually done.
>
> First, you have to construct your query correctly. TOP N is part of it
> ‑ you absolutely need that in your query, and the N part should

probably
> be '%d' and that should be tied to the MaxRows parameter, which has some
> nice automatic sizing behavior.
> But you also need to have a WHERE clause which specifies your offset ‑
> something like:
> WHERE RowNumber > %s ORDER BY RowNumber
> The '%s' will be replaced by the offset stored in the Event Source.
>
> Can you post:
> 1) The contents of your sqlquery.base file
> 2) The method you're passing to conn.addParser()
>




0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Several repeated records with SQL Server Collector


Hi Nobert,

Thanks for your reply. A lot of the tables in your sql query no longer
exist in the new Symantec Endpoint Protection Database.

My sqlquery.base currently looks like this:

SELECT TOP %d
V_alerts.Idx SymId
,v_alerts.time_stamp TestOffset
,V_ALERTS.ALERTDATETIME DateTimeStamp
,alertmsg.alert EventName
,V_ALERTS.USER_NAME DestUserName
,V_ALERTS.DESCRIPTION Message
,v_SEM_COMPUTER.COMPUTER_NAME DestHostName
,v_SEM_COMPUTER.IP_ADDR1_TEXT DestIP
,VIRUS.VIRUSNAME Virus
,VIRUS.CATEGORY Severity
,V_ALERTS.FILEPATH FileName
FROM v_alerts
,alertmsg
,HPP_ALERTS
,v_SEM_COMPUTER
,VIRUS
WHERE v_ALERTS.ALERT_IDX = ALERTMSG.ALERT_IDX
and v_alerts.IDX = HPP_ALERTS.IDX
and v_ALERTS.USN = HPP_ALERTS.USN
and V_ALERTS.COMPUTER_IDX = v_SEM_COMPUTER.COMPUTER_ID
and V_ALERTS.VIRUSNAME_IDX = VIRUS.VIRUSNAME_IDX
and V_ALERTS.time_stamp > %s
order by TestOffset

Unfortunately, I have had to create two separate collectors, one for
the virus / threat events and another for USB/IPS events. (Mostly likely
because my SQL skills are not as good as i would have hoped)

Both collectors point to the same database but report on different
events and this works fine ATM. Like i mentioned before the only issue
is that it takes about 6 minutes to get an event.


Norbert Klasen;2125141 Wrote:
> Hi,
> in a recent PoC the customer used my MySQL DB for Symantec Threat
> Reporter.
> We used the following sqlqeury:
> SELECT
> alerts.Idx Id
> , alertmsg.alert EventName
> , clientuser.clientuser DestUserName
> , virus.category Severity
> , virus.virusname Virus
> , alerts.description Message
> , inventory.computer DestHostName
> , inventory.ip_address DestIP
> , alerts.Filepath FileName
> , alerts.alertdatetime TimeStamp
> FROM alerts
> ,alertmsg
> , clientuser
> , virus
> , actualaction
> , inventory
> WHERE alerts.alert_idx = alertmsg.alert_idx
> and alerts.clientuser_idx = clientuser.clientuser_idx
> and alerts.virusname_idx = virus.virusname_idx
> and alerts.actualaction_idx = actualaction.actualaction_idx
> and alerts.computer_idx = inventory.computer_idx
> and alerts.Idx > %s
> ORDER BY alerts.Idx
> LIMIT %d
>
>
> Norbert
>
> >>> On 28.07.2011 at 04:56,

> DCorlette<DCorlette@no-mx.forums.novell.com>
> wrote:
>
> > OK, so there are several parts to this, and it's not clear from your
> > post what you've actually done.
> >
> > First, you have to construct your query correctly. TOP N is part of

> it
> > ‑ you absolutely need that in your query, and the N part should

> probably
> > be '%d' and that should be tied to the MaxRows parameter, which has

> some
> > nice automatic sizing behavior.
> > But you also need to have a WHERE clause which specifies your offset

> ‑
> > something like:
> > WHERE RowNumber > %s ORDER BY RowNumber
> > The '%s' will be replaced by the offset stored in the Event Source.
> >
> > Can you post:
> > 1) The contents of your sqlquery.base file
> > 2) The method you're passing to conn.addParser()
> >



--
kademolu1
------------------------------------------------------------------------
kademolu1's Profile: http://forums.novell.com/member.php?userid=104108
View this thread: http://forums.novell.com/showthread.php?t=438571

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.