Highlighted
Established Member.. raymond.doty
Established Member..
1521 views

Useful ESM 6 MySQL queries?

Wrote a query that helped me out quite a bit.  Given that the trend tables take up arc_system_data tablespace in ESM6, we didn't realize this and filled up the space, had to identify the trends which were filling it up and fix them.  Was manually running 3 separate queries (and then a find within ESM) in an attempt to find out what trends were what...  This saved a little time.

This should link the appropriate tables together and give you all the info you need to solve this problem, if you run into it

Just login to MySQL as your arcsight user and run...

SELECT round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB", table_name, arc_trend.id as "Resource ID", arc_resource.name as "Resource Name" FROM information_schema.TABLES LEFT JOIN (arc_trend,arc_resource) ON (arc_trend.table_id=UPPER(mid(table_name,11,6)) AND arc_trend.id=arc_resource.id) WHERE table_name like 'arc_trend_%' order by round(((data_length + index_length) / 1024 / 1024), 2) desc;

Example output:

+------------+------------------+---------------------------+--------------------------------------------------+

| Size in MB | table_name       | Resource ID               | Resource Name                                    |

+------------+------------------+---------------------------+--------------------------------------------------+

|   60095.00 | arc_trend_uc1m3d | ^ZXTZFD4BABCX2OEcmdsw6A== | Windows Hourly Events                            |

|   37420.00 | arc_trend_car6kg | ^set1AD4BABCyKM17bNCz2w== | Firewall Hourly Events                           |

|   15485.70 | arc_trend_bc60pv | ^hbgbED4BABChS6l94bh0pg== | Failed Windows Logons Hourly                     |

|    1867.88 | arc_trend_hu2lj6 | ^im191z0BABCNzk6Z0uOf7A== | ArcSight-General System Health_Hourly            |

Labels (1)
9 Replies
deathbywedgie1 Frequent Contributor.
Frequent Contributor.

Re: Useful ESM 6 MySQL queries?

Thanks, Ray. I have bookmarked this for our ESM 6 roll-out, which is about to begin.

0 Likes
Established Member.. raymond.doty
Established Member..

Re: Useful ESM 6 MySQL queries?

Not a problem, just remember to set aside enough space in your arc_system_data for trends *during the install* if you use them in a high EPS environment.  I think we estimated that we would need somewhere between 1.5-2tb to be on the safe side for trending (at about 20k EPS with heavy trend usage - I think we estimated a comprehensive windows trend with 10 string fields and 25m events was something like 50gb in size).  I have an open case with support to find the command to resize, can't find it in the documentation.

Good luck!

0 Likes
Rajesh1 Absent Member.
Absent Member.

Re: Useful ESM 6 MySQL queries?

Hi Ray,

Did you get any update from arcsight support regarding above issue?

0 Likes
Established Member.. raymond.doty
Established Member..

Re: Useful ESM 6 MySQL queries?

Yes they indicated the storage space is actually stored in the my.cnf.  Its the standard configuration line for the innodb storage.

This line:

# DO NOT change the following line!!!

innodb_data_file_path = ibdata1:10M;ibdata2:1G:autoextend:max:2000G

I basically modified the last parameter (2000G, from 512M) and restarted the database.  Worked fine.

Although I guarantee you will want to consult support before you do this, as they were extremely hesitant to give out the instructions.

0 Likes
sliver1 Absent Member.
Absent Member.

Re: Useful ESM 6 MySQL queries?

Hi , I have an issue with running out of space. Ultimately expanding the allocated space is the way to go, but at the moment I want to identify the offending Trend  and alter the retention period.

When running the mySQL command I get : "ERROR 1046 (3D000): No database selected"

:

Found my problem .... have to tell it to "use arcsight"   !!!

0 Likes
Established Member.. raymond.doty
Established Member..

Re: Useful ESM 6 MySQL queries?

Try inputting

use arcsight;

before you, input the SQL statement.

0 Likes
marioc Frequent Contributor.
Frequent Contributor.

Re: Useful ESM 6 MySQL queries?

Hi Ray, have you experienced issues with large trends ?   We have been told by support that trends larger than 1 Gb can affect performance.

Regards

Mario

0 Likes
Established Member.. raymond.doty
Established Member..

Re: Useful ESM 6 MySQL queries?

Yes, trending is our #1 issue at high throughput.  I can't put a precise number on it, but our issue is more specific to scale (3000+ EPS) and query functionality (grouping, sorting, aggregate functions, unique counts, etc).

0 Likes
marioc Frequent Contributor.
Frequent Contributor.

Re: Useful ESM 6 MySQL queries?

Hi Ray, thank you for your reply.  Have you determined at what trend size ESM 6.5 SP1 start having problems ?

Support keeps telling us that large trends can cause issues but they don't know at what specific size the issues starts.

Regards

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.