Working with Report Writer

In Orion Platform 2020.2.6, the Report Writer tool is removed from the SolarWinds Platform. Migrate the reports to the web and use the web-based reports.

Existing Reports

You can open and modify any of the built-in reports in Report Writer. When modifying an existing report, it is best to find a report that already has most of the elements you require and start from there. Doing so will help to minimize the time and complexity of creating your custom report. For example there is a prebuilt NetFlow report for “Top 10 Traffic Destinations by Domain”. The Top N domains resource in Orion NetFlow Traffic Analyzer is one of many top N resources including Endpoints, Applications, Conversations, and Countries. So, if we want to create a report for the Top N traffic by Country, it is reasonable to start with a similar report, such as the domain report.

Once we begin altering this report to create the Top Country report, save the report with a new name. This way you can rest assured that you have not altered the Top Domain report. If you get hopelessly lost at some point, you can simply stop creating the Top Country report and go back to the Top Domain report and start over.

The General tab in Report Writer is very simple. You can assign the report to a group, create a new group by using a non-existent group name, edit the report title and subtitle as well as add notes in the description field..

Next we move to the Select Fields tab and alter the report to show what we want to see, Top Countries.

This tab allows you to select objects from the SolarWinds Platform database without having to dig through the database or create SQL queries. When you modify fields in this tab, report writer creates a SQL query in the background. By clicking on Report > Show SQL from the Report Writer Menu bar, the query is run and shown in a new tab called SQL. The results are also displayed. Depending on how much data you have and the complexity of the query, it can take a couple of minutes to run the full query.

The field that was used to create the Top N Domain source report was NetFlow Endpoints > Transmitters > Domain. By selecting Country instead of Domain, we will alter the query partially. Take a look at the SQL query below with Country chosen in the place of Domain.

SELECT TOP 10

FlowCorrelation_Source_FlowCorrelation.Country AS Country,

Nodes.Caption AS NodeName,SUM(NetflowSummary.TotalBytes) AS SUM_of_Bytes_Transferred FROM (NetflowSummary LEFT OUTER JOIN FlowCorrelation FlowCorrelation_Source_FlowCorrelation ON (NetflowSummary.SourceIPSort = FlowCorrelation_Source_FlowCorrelation.IPAddressSort))  INNER JOIN Nodes ON (NetflowSummary.NodeID = Nodes.NodeID)WHERE ( DateTime BETWEEN 40313.4583333333 AND 40314.5 ) AND 

((FlowCorrelation_Source_FlowCorrelation."Domain" IS NOT NULL) AND

  (FlowCorrelation_Source_FlowCorrelation."Domain" <> ''))

AND((EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=InterfaceIDRx AND NetFlowSources.Enabled=1)))

GROUP BY FlowCorrelation_Source_FlowCorrelation.Country, Nodes.Caption

ORDER BY 3 DESC

This query can be broken down into three sections:

1. Data selection – Begins at the SELECT TOP 10 statement

2. Data filtering – Begins at the WHERE statement

3. Grouping and ordering – begins at the GROUP BY statement

 

So far the query is selecting Country data, but if you look further into the above query, the query still refers to Domain. This is because we still need to go into the remaining tabs and clean up references to Domain..

By clicking on the Domain references we can change them to Country, just as we did in the Select Fields tab. The shot below shows this change being made.

Now here is the filtering section of the query after changing the Filter to refer to Countries.

(FlowCorrelation_Source_FlowCorrelation.Country IS NOT NULL) AND (FlowCorrelation_Source_FlowCorrelation.Country <> '')

The first item in the filtering section of the SQL is the applicable time frame. This is specified in the Report Writer Time Frame tab and converted to the SQL Object Linking and Embedding (OLE) time. SQL OLE time is a standard way of represent time in days since an arbitrary day zero, Midnight, December 30, 1899. Counting dates as a time since day zero offers the advantage of the date being represented as just a number. So, it can be processed without regard to time increments, such as minutes and seconds. This eliminated converting and processing seconds to minutes, minutes to hours, days to months, and so forth. The date can be translated into the OLE date, manipulated and then converted back to a date. As the OLE date can have an infinite number of decimal places, it can accurately measure any time increment. When a query is run which contains an OLE date, the date is updated on the fly to the proper date range.

Consider the following examples. When we look at the SQL for a report using the time frame of “Yesterday”, note the OLE date referenced.

WHERE ( DateTime BETWEEN 40300 AND 40301 )

Now, when we run the query the next day, here are the results of the same “Yesterday” date.

WHERE

( DateTime BETWEEN 40301 AND 40302 )

If we change the date to “Today”, which will include a partial day as today has not completed, we can see the decimal notation for fractions of a day.

WHERE

( DateTime BETWEEN 40302 AND 40302.5833333333 )

Historical reports will often use the OLE format. Also note that reports for the current state of items, such as the “Current Volume Statistics” will have no reference to time, they will only query for the current values requested.

The remaining tabs in the Report Writer interface are fairly simple and well documented in the appropriate Administrator Guide for your product.