ArcSight Logger - Reports & Queries

Table of Contents:

Primer

The first concept to understand about a Report is that every report is built on top of a SQL query. If you are building a report from the ground up, you will have to write a new sql query or adapt an existing one to your needs.

Before we get into running and building a report, we will take a look at a query. For this tutorial, we will take apart a fairly complex query that uses some dynamic SQL to do some neat stuff.

• This tutorial is based on a query with 2 parameters, and 1 report. You can go through these in ArcSight to see how they work.
  • Query: XD - Top Sources of Login Events DYNAMIC
    • Parameter 1: campusWhereSnippet
    • Parameter 2: authResult
  • Report: XD - Top Sources of All Login Attempts

Queries

(Note: This assumes you have a basic knowledge of SQL. If you don't I highly recommend w3schools. Especially their "try it" functionality.)

To create a new query, go through the menus: Reports > (Left Nav: Design) > Queries > Click the little + icon

This will create a new report for you. Name it whatever you want (look at other reports for a naming convention). In the SQL frameset, click Edit with load in new window checked. This will bring up a new window for you to enter SQL. Click the SQL tab, because the Design tab sucks. Now we are ready to write some SQL!

Here is an example of some SQL:

SELECT
   events.arc_sourceAddress,
   events.arc_sourceHostName,
   sum(IF (events.arc_categoryOutcome = '/Failure', 1, 0)) as totalFailures,
   sum(IF (events.arc_categoryOutcome = '/Success', 1, 0)) as totalSuccess,
   sum(IF (events.arc_categoryBehavior = '/Authentication/Verify', 1, 0)) as totalAttempts,
   count(DISTINCT events.arc_destinationAddress) as totalTargets
FROM
   events
WHERE
   events.arc_categoryBehavior = '/Authentication/Verify'
   <%campusWhereSnippet%>
GROUP BY
    events.arc_sourceAddress
ORDER BY
    <%authResult%> DESC

Easy Right? LOL, just kidding! Lets go through it, one line at a time:

• sum(IF (events.arc_categoryOutcome = '/Failure', 1, 0)) as totalFailures,
  • The If statement returns 1 if true, and 0 if not. Thus, we are getting the sum of all the events that were a failed login, and calling those "totalFailures"
• count(DISTINCT events.arc_destinationAddress) as totalTargets
  • This counts up each distinct destination address and calls them total "totalTargets." Since the GROUP BY clause is by sourceAddress, we can tell how many unique targets a given IP has hit.
• <%campusWhereSnippet%>
  • This isn't even valid SQL! What the hell is going on here?
    • You're right. It isn't. The <% %> syntax is for ArcSight's parameters. See below digression into parameters...

When you are done writing your SQL click OK. If your sql is valid, the window will close, or take you to a sample SQL output (which may be a blank result set, because in this mini return environment, it is time boxed to the last hour or less).

Be sure to click SAVE in the upper left when you are done editing your query. If you don't, navigating to another part of ArcSight will wipe out your edits.

Parameters

Parameters allow you to execute different bits of SQL at report run time. This "dynamic sql" is very powerful. The user can select options from a dropdown list, and the query will execute whatever SQL associated with the selected option.

Here's an example:

Lets create a parameter by going to: Reports > (Left Nav: Design Heading) > Parameters

Click the little + icon to make a new one.

• For this example, Name: campusWhereSnippet
  • name must match the parameter used in your query (but without the markup):  <%campusWhereSnippet%>
• Prompt: On or Off Campus...
  • What the user will see as the title of this dropdown menu.

Right off the bat, check "mandatory" and "visible" boxes. Choose "combo" with multi-select off.

Now in the upper right frameset, choose Pre Defined. Each entry you make here will be an entry in the drop down box a user will see at report run time. "Display name" will be what the user sees when running the report. "Value" will be the snippet of SQL run when a user selects this entry.

Now lets write an entry:

Name: On Campus

Value:

AND (
   events.arc_sourceAddress LIKE '130.64.%'
   OR events.arc_sourceAddress LIKE '10.%'
   OR events.arc_sourceAddress LIKE '192.168.%'
   OR events.arc_sourceAddress LIKE '172.16.%'
   OR events.arc_sourceAddress LIKE '172.17.%'
   OR events.arc_sourceAddress LIKE '172.18.%'
)

The entry above will filter by on campus IP addresses only.

We can add another entry:

Name: All

Value:

AND 1=1

The entry above is just a dummy. It allows us to put in an "All" category that won't filter by anything. Thus, "All" IP's will be included in the search.

Take a moment to learn about the parameters <%authResult%> and <%campusWhereSnippet%> by looking at the code in ArcSight.

Thus, we have created a parameter that allows the user to execute dynamic SQL on the fly. The neat thing is, that when a user runs the report, all of this happens behind the scenes, and they don't have to muck with it. Finally, on to Reports...

Creating Reports

Reports > (Left Nav: Design Heading) > New Report

Creating a new report is a fairly straightforward process. Click New Report to get started. The most important part of your report is selecting which query it will run on. Do so by selecting the query you wrote from the unlabeled dropdown menu near the top. Also, be sure to select detailed from the Report Contents dropdown. Also, HTML is a good choice for report format (you can view the report right in ArcSight). Blank or Wide Format are good format choices. You can now customize your report by messing around with the options below. But you shouldn't have to if you've written clear, simple parameters.

Running Reports

Find the report you want by clicking on any of the Report links under "Foundation Reports," "Device Monitoring Reports," "User Reports."

You can run it simply by clicking on the report name. If you want to customize it before you run it, click the little pencil. Click save if you want to keep your changes. If it's a one-off report you can simply click run.

This will bring up a page where you can select a couple options for the report before running it. Select values for any dropdowns. You are required to time-box your search.

If you aren't sure about Storage Groups, shift-select them all. This will ensure you are pulling data from all sources.

If you aren't sure about Devices, shift-select all of them that don't start with NOT. This will ensure you are pulling data from all sources.