ESM 6.5 Report Perfomance

[ftavares]

Hi all,

we are facing some problems against Report & Archive in ESM 6.5 Solution.

Everytime we use a big time frame for report generation (like 30 days) we get trouble about get report's result.

In fact, i guess it falls in some kind of timeout, because when I go the Archive Folder the file just is not there.

Looking at logs' folder in ESM, I just get messages mentioning the query itself but I do not see any WARNING or ERROR message telling what happened with report generation process. I do not see any indicator of error.

So, is there some kind of paper with tips about debug this kind of error and, most important, is there some guide with server.properties parameters that I could use to improve my perfomance for reports & archive?

My report generation has 24 hours timeout and I have 38 days of event's retention.

Regards.

[jan.husselson]

Are you currently using Trends to form the dataset your report queries are running on? That should solve most of the performance issues you are facing. Using trends is explained in chapter 10 - Building reports of the Arcsight Console User Guide. (attached)

[ftavares]

Hi Jan, thanks for your reply. In fact, I am using Trends for some cases, but I would like to know I there is some group of settings that I can made in my ESM solution to improve it. As I said, sometimes I do not want to run a Trend, just run a straightful query/report in database. You know something in this line?

Thanks.

[jan.husselson]

I am not aware of such settings.

However when reports fail to run I have experienced that rebuilding a report by hand (Create report from template -> enter queries and such) has solved the 'timeouts' you speak of. It sounds strange as nothing actually changed, but I guess it's magic.

[hatemware]

I faced the same issue and i believe the problem could be one of 2 things:

1) sort_temp_limit parameter, in that case you will see below error in server.log (could be server.log.1 or server.log.2, etc ...so the best thing is to grep through server.log*):

Report could not be archived.

Reason: inetsoft.sree.RepletException: Failed to create report: com.arcsight.server.reports.ReportGenerationException: Encountered persistence problem while fetching data: Unable to execute query: Temporary sort space limit exceeded

check the following for more details

• https://protect724.hp.com/message/37156#37156
• https://protect724.hp.com/message/36655#36655
• ESM_AdminGuide_6.8c.pdf -> page 145

2) you are using heavy string manipulation variables inside your query which causes the QueringDB process to last for more than 10 hours so it got interrupted by an ArcSight self-safe mechanism with below error (solution is to remove these report killer variables from your query):

Failure during execution attempt: inetsoft.sree.RepletException: Failed to create report: com.arcsight.server.reports.ReportGenerationException: Encountered persistence problem while fetching data: Unable to execute query: Query execution was interrupted. [Thread = Thread-2126]

Please note that the second reason is a conclusion of mine (not 100% accurate as this goes with experience and documented anywhere).

BR,

Hatem Metwally

[ftavares]

Hi, great answer. I will research about these messages. I have counter-measured this using Lists and Trends, but your point was pretty good.

Regards.

[mhutchison]

I have had this same experience.

[kapilarattan]

Hey Hatem,

Did you find the solution to the issue you mentioned.

I am getting similar issues on my Arcsight 6.5 for report generation. The reports output is very fast when ran for small durations like 1h, 2h even 1day. but when I ran the report for a month(30 days) it just doesnt run and throws an exception

"Reason: inetsoft.sree.RepletException: Failed to create report: com.arcsight.server.reports.ReportGenerationException: Encountered persistence problem while fetching data: Unable to execute query: Query execution was interrupted"

Surprisinginly these reports were running fine for the past 4monthly reports and its only this month it has started showing up issues like this.

[hatemware]

Hi Kapila,

I think that this new behavior might be due to one of the following:

(1) the amount of logs increased in the last month - maybe you recently integrated new log sources into ArcSight that fall under the scope of the queries that built up your report

(2) or, one or more of the report queries were modified recently and are now including heavy string manipulation variables

One way is that you can grep through the logs using your report name, so you can spot all the start and end logs of the queries building your report, then subtracting the start and end time of each query, you can now know which query in specific caused that issue. Then you can try to replace string variables with normal fields or maybe you can give it a try and rely on global variables instead of local ones (not sure though).

As far as i know there is no defentive way except using trends on top of your query and shorten the period of the query and match it with the sampling rate of your trend.

BR,

Hatem

[ftavares · Accepted solution]

Hi guys, just for update: I saw many outages and timeouts messages along .log files, my problem was a huge size of my database. So, to correct the issues I basicly did:

- Filter out my connectors to exclude some unwanted events (reducing used storage size)

- Remodeled all my queries, providing more perfomance.

Now, I am running my reports OK.