Logger Report for Monthly Vendor/Product Counts

Blog Post created by aaron.kramer@hpe.com on Jul 28, 2016

Short note on some previous content I authored:

My previous post on using simple regex in HPE ArcSight Logger is a discussion here:How to Find What You Want : Using simple regex in HPE ArcSight Logger

When I realized I didn't put that in my Blog I'll thought I would note it here in my Blog so more people can see it!


And now on to the current Blog post:

Recently I helped out on a Logger Report that gave some interesting summaries of events. The ask was to produce counts by month of vendor/product pairs.

Just count up how many events were from each Vendor/Product pair, each month, and show the counts in a report. This post details how I went from the requirements to the query. Although for some people this may be a simple report, I hope others can see what was done and how, and provide some guidance to getting more value from their HPE ArcSight ADP and Logger(s).


Four columns: Vendor, Product, Month, and # Events.

The first 2 are fairly easy, these values would come from deviceVendor and deviceProduct. I'll have to put in a portion of the query to not include events from ArcSight itself. Unless one is reporting on ArcSight internal activity, the report will contain just events from the enterprise, not ArcSight itself. This means I'll have to put in a 'deviceVendor != ArcSight' statement in the WHERE portion of the query.


The next column, 'Month', will come from the deviceReceiptTime. For this report, the requirement was to produce a column with the month name and year. I can use 3 functions from SQL to get what I want. The function MONTHNAME(events.arc_deviceReceiptTime) will get the name of the month. YEAR(events.arc_deviceReceiptTime) will get me the year, and then I can concatenate the two using the CONCAT function to get a combination of the two. I will also put a space in between to make in readable!

CONCAT(MONTHNAME(events.arc_deviceReceiptTime), " ", YEAR(events.arc_deviceReceiptTime)) as "Month",


The fourth column will be a count of the events, like this: count(events.arc_baseEventCount) as "Events"

To group the events by Vendor, Product, and then 'month', I will put the GROUP BY clause in there.

Finally, I will order the report by the Vendor/Product/Month triad.


Here's the final query:


events.arc_deviceVendor as "Vendor",

events.arc_deviceProduct as "Product",

CONCAT(MONTHNAME(events.arc_deviceReceiptTime), " ", YEAR(events.arc_deviceReceiptTime)) as "Month",

count(events.arc_baseEventCount) as "Events"


FROM events

WHERE events.arc_deviceVendor != "ArcSight"

GROUP BY events.arc_deviceVendor, events.arc_deviceProduct, CONCAT(MONTHNAME(events.arc_deviceReceiptTime), " ", YEAR(events.arc_deviceReceiptTime))

ORDER BY events.arc_deviceVendor, events.arc_deviceProduct, CONCAT(MONTHNAME(events.arc_deviceReceiptTime), " ", YEAR(events.arc_deviceReceiptTime))


And here is an image of the report from a test Logger I have in a lab:


In HPE ArcSight Logger, this report can be run in a few different ways, using different methods to specify the date range: $now - 2M is for 2 months, or $now - 65d is for going back 65 days.


Thanks for reading!

If you have comments or suggestions for future topics, let us know!