ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins. Read more for important details.
ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins.Read more for important details.
Vice Admiral
Vice Admiral
3661 views

Average ArcSight event size

Jump to solution

Hi,

I am trying to spec my database for a new installation of ArcSight.

I have an old installation running that has been auditing for some time.

I would like to be able to query my existing database to obtain a true value for the average ArcSight event size (once arcsight has *enriched* the message).

Would anyone know how i can query oracle to obtain this value?

Cheers.

0 Likes
1 Solution

Accepted Solutions
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

This query should give you what you're looking for over the preceeding 2 weeks with the average size in bytes:

SELECT to_char(p.upper_bound-1,'MM/DD/YYYY') as dates,round(p.num_rows/1000000,2) as num_rows, p.data_size*1024*1024/p.num_rows as avg_event_size
FROM arc_partition p, arc_resource r
WHERE r.id = p.id AND p.upper_bound < sysdate AND p.lower_bound > sysdate-15
ORDER BY p.upper_bound;

View solution in original post

0 Likes
6 Replies
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

This query should give you what you're looking for over the preceeding 2 weeks with the average size in bytes:

SELECT to_char(p.upper_bound-1,'MM/DD/YYYY') as dates,round(p.num_rows/1000000,2) as num_rows, p.data_size*1024*1024/p.num_rows as avg_event_size
FROM arc_partition p, arc_resource r
WHERE r.id = p.id AND p.upper_bound < sysdate AND p.lower_bound > sysdate-15
ORDER BY p.upper_bound;

View solution in original post

0 Likes
Vice Admiral
Vice Admiral

superb answer! thank you very much Chris. It worked a treat!

Average size looks to be around 600bytes with raw events being maintained in the message and anomilies removed, would this seem sensible?

0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

Thanks!  Glad to help!

That query is also useful for putting the average number of events for the past 2 weeks on a report for execs

Is the 600 for your compressed or uncompressed partitions?  I have around 550 bytes uncompressed, and 170 bytes compressed:

DATES      MILL_NUM_ROWS AVG_SIZE
---------- ------------- ----------
01/18/2010         72.35 163.554667
01/19/2010         77.24 167.963309
01/20/2010         68.75 178.158782
01/21/2010         67.91 177.150824
01/22/2010         66.58 174.263945
01/23/2010         53.35 175.280239
01/24/2010         63.52  144.85602
01/25/2010         83.96 161.395279
01/26/2010         68.63 170.805957
01/27/2010         68.75 170.624287
01/28/2010         67.34 177.887321
01/29/2010         54.63 536.071672
01/30/2010         43.73  547.86755
01/31/2010         41.23 527.295412

Does that help ya?

0 Likes
Vice Admiral
Vice Admiral

Yeah, that was really useful.

Nice to know that we've got similar event sizes!

That 600 bytes is uncompressed and at this point in the design of the solution we have 50% of the connectors maintaining raw events so i guess that could explain the slightly large event size?

Thanks again.

-Tom

0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

Yeah, that's most likely the reason it's bigger.  If you think about it, the raw event is going to be 1/2 to 3/4 the size of the normalized event stored in the DB.

For our setup, we only keep the raw event for security-related connectors (IDS/IPS) and syslog data.

0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

Hi, I've been using another query to get the average size of events per device type (it can vary a lot between a firewall with aggregation turned on and linux-based syslog with raw events, for instance).

Here's my query :

select 3 + avg(
nvl(vsize(arc_event.EVENT_ID),0)+1 + nvl(vsize(arc_event.END_TIME),0)+1 + nvl(vsize(arc_event.MANAGER_RECEIPT_TIME),0)+1 + nvl(vsize(arc_event.CAT_DESCRIPTOR_ID),0)+1 + nvl(vsize(arc_event.DVC_DESCRIPTOR_ID),0)+1 + nvl(vsize(arc_event.AGT_DESCRIPTOR_ID),0)+1 + nvl(vsize(arc_event.AGT_RECEIPT_TIME),0)+1 + nvl(vsize(arc_event.BASE_EVENT_COUNT),0)+1 + nvl(vsize(arc_event.DEST_TRANS_ADDRESS),0)+1 + nvl(vsize(arc_event.DEST_ADDRESS),0)+1 + nvl(vsize(arc_event.DEST_GEO_ID),0)+1 + nvl(vsize(arc_event.DEST_PORT),0)+1 + nvl(vsize(arc_event.DEST_TRANS_PORT),0)+1 + nvl(vsize(arc_event.DEST_ZONE),0)+1 + nvl(vsize(arc_event.NAME),0)+1 + nvl(vsize(arc_event.EVENT_TYPE),0)+1 + nvl(vsize(arc_event.GENERATOR),0)+1 + nvl(vsize(arc_event.PRIORITY),0)+1 + nvl(vsize(arc_event.RAW_EVENT),0)+1 + nvl(vsize(arc_event.SRC_ADDRESS),0)+1 + nvl(vsize(arc_event.SRC_GEO_ID),0)+1 + nvl(vsize(arc_event.SRC_PORT),0)+1 + nvl(vsize(arc_event.SRC_TRANS_ADDRESS),0)+1 + nvl(vsize(arc_event.SRC_TRANS_PORT),0)+1 + nvl(vsize(arc_event.SRC_ZONE),0)+1 + nvl(vsize(arc_event.TRANS_PROTOCOL),0)+1 + nvl(vsize(arc_event.START_TIME),0)+1 + nvl(vsize(arc_event.CUSTOMER),0)+1 + nvl(vsize(arc_event.EXTERNAL_ID),0)+1 + nvl(vsize(arc_event.MESSAGE),0)+1 + nvl(vsize(arc_event.APP_PROTOCOL),0)+1 + nvl(vsize(arc_event.VULNERABILITY),0)+1 + nvl(vsize(arc_event.BYTES_IN),0)+1 + nvl(vsize(arc_event.BYTES_OUT),0)+1 + nvl(vsize(arc_event.SESSION_ID),0)+1 + nvl(vsize(arc_event.CRYPTO_SIGNATURE),0)+1 +
nvl(vsize(arc_event.CONCENTRATOR_AGENTS),0)+1 + nvl(vsize(arc_event.CONCENTRATOR_DEVICES),0)+1 + nvl(vsize(arc_event.MODEL_CONFIDENCE),0)+1 + nvl(vsize(arc_event.SEVERITY),0)+1 + nvl(vsize(arc_event.RELEVANCE),0)+1 + nvl(vsize(arc_event.PERSISTENCE),0)+1 + nvl(vsize(arc_event.LOCALITY),0)+1 +
nvl(vsize(arc_event.ASSET_CRITICALITY),0)+1 + nvl(vsize(arc_event.AGT_SEVERITY),0)+1 + nvl(vsize(arc_event.DVC_EVENT_CATEGORY),0)+1 + nvl(vsize(arc_event.DVC_SEVERITY),0)+1 + nvl(vsize(arc_event.DVC_ACTION),0)+1 + nvl(vsize(arc_event.DVC_RECEIPT_TIME),0)+1 + nvl(vsize(arc_event.DVC_DOMAIN),0)+1 +
nvl(vsize(arc_event.DVC_EVENT_CLASS_ID),0)+1 + nvl(vsize(arc_event.DVC_DIRECTION),0)+1 + nvl(vsize(arc_event.DVC_PAYLOAD_ID),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING1),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING2),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING3),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING4),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING5),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_STRING6),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_NUMBER1),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_NUMBER2),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_NUMBER3),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_DATE1),0)+1 + nvl(vsize(arc_event.DVC_CUSTOM_DATE2),0)+1 + nvl(vsize(arc_event.FILE_NAME),0)+1 + nvl(vsize(arc_event.FILE_PATH),0)+1 +
nvl(vsize(arc_event.FILE_ID),0)+1 + nvl(vsize(arc_event.FILE_TYPE),0)+1 + nvl(vsize(arc_event.FILE_SIZE),0)+1 + nvl(vsize(arc_event.FILE_CREATE_TIME),0)+1 + nvl(vsize(arc_event.FILE_MODIFICATION_TIME),0)+1 + nvl(vsize(arc_event.FILE_PERMISSION),0)+1 + nvl(vsize(arc_event.FILE_HASH),0)+1 + nvl(vsize(arc_event.OLD_FILE_NAME),0)+1 + nvl(vsize(arc_event.OLD_FILE_PATH),0)+1 + nvl(vsize(arc_event.OLD_FILE_ID),0)+1 + nvl(vsize(arc_event.OLD_FILE_TYPE),0)+1 + nvl(vsize(arc_event.OLD_FILE_SIZE),0)+1 +
nvl(vsize(arc_event.OLD_FILE_CREATE_TIME),0)+1 + nvl(vsize(arc_event.OLD_FILE_MODIFICATION_TIME),0)+1 + nvl(vsize(arc_event.OLD_FILE_PERMISSION),0)+1 + nvl(vsize(arc_event.OLD_FILE_HASH),0)+1 + nvl(vsize(arc_event.REQUEST_URL),0)+1 + nvl(vsize(arc_event.REQUEST_METHOD),0)+1 + nvl(vsize(arc_event.REQUEST_CONTEXT),0)+1 + nvl(vsize(arc_event.REQUEST_CLIENT_APPLICATION),0)+1 + nvl(vsize(arc_event.REQUEST_COOKIES),0)+1 + nvl(vsize(arc_event.RULE_THREAD_ID),0)+1 + nvl(vsize(arc_event.ORIGINATOR),0)+1 + nvl(vsize(arc_event.DEST_HOST_NAME),0)+1 + nvl(vsize(arc_event.DEST_DNS_DOMAIN),0)+1 + nvl(vsize(arc_event.DEST_NT_DOMAIN),0)+1 + nvl(vsize(arc_event.DEST_USER_NAME),0)+1 + nvl(vsize(arc_event.DEST_USER_ID),0)+1 + nvl(vsize(arc_event.DEST_MAC_ADDRESS),0)+1 + nvl(vsize(arc_event.DEST_TRANS_ZONE),0)+1 + nvl(vsize(arc_event.DEST_PROCESS_NAME),0)+1 + nvl(vsize(arc_event.DEST_SERVICE_NAME),0)+1 +
nvl(vsize(arc_event.DEST_USER_PRIVILEGES),0)+1 + nvl(vsize(arc_event.DEST_ASSET_ID),0)+1 + nvl(vsize(arc_event.SRC_HOST_NAME),0)+1 + nvl(vsize(arc_event.SRC_DNS_DOMAIN),0)+1 + nvl(vsize(arc_event.SRC_NT_DOMAIN),0)+1 + nvl(vsize(arc_event.SRC_USER_NAME),0)+1 + nvl(vsize(arc_event.SRC_USER_ID),0)+1 + nvl(vsize(arc_event.SRC_MAC_ADDRESS),0)+1 + nvl(vsize(arc_event.SRC_TRANS_ZONE),0)+1 + nvl(vsize(arc_event.SRC_PROCESS_NAME),0)+1 + nvl(vsize(arc_event.SRC_SERVICE_NAME),0)+1 + nvl(vsize(arc_event.SRC_USER_PRIVILEGES),0)+1 + nvl(vsize(arc_event.SRC_ASSET_ID),0)+1 + nvl(vsize(arc_event.FLEX_NUMBER1),0)+1 + nvl(vsize(arc_event.FLEX_NUMBER1_LABEL),0)+1 + nvl(vsize(arc_event.FLEX_NUMBER2),0)+1 +
nvl(vsize(arc_event.FLEX_NUMBER2_LABEL),0)+1 + nvl(vsize(arc_event.FLEX_STRING1),0)+1 + nvl(vsize(arc_event.FLEX_STRING1_LABEL),0)+1 + nvl(vsize(arc_event.FLEX_STRING2),0)+1 + nvl(vsize(arc_event.FLEX_STRING2_LABEL),0)+1 + nvl(vsize(arc_event.FLEX_DATE1),0)+1 + nvl(vsize(arc_event.FLEX_DATE1_LABEL),0)+1 + nvl(vsize(arc_event.LBL_DESCRIPTOR_ID),0)+1
) "data bytes", arc_event_device.dvc_product "product", arc_event_device.dvc_vendor "vendor",
count(*)
from arc_event sample(0.01),arc_event_device
where arc_event_device.DVC_DESCRIPTOR_ID=arc_event.DVC_DESCRIPTOR_ID
group by arc_event_device.dvc_vendor,arc_event_device.dvc_product
;



Pay attention : this doesn't take the index size into account ! (I think the poster's query I'm replying to doesn't either...). If you want to make capacity planning, you have to make a lot of additional assumptions (about resources, your active list usage, topology information, etc...), you also have to make mathematical error correction and correlations. In definitive, the model is quite complex. It's way too heavy to describe here. It was a full project on its own here...

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.