Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Report Writer - Custom SQL

I'm looking for some help with a custom sql report I'm trying to setup.

I wanted to run my custom sql report on the 1^st of every month and have it compile data from the previous month (or even previous 30 days). Does anyone happen to know how I could call out the previous month in the code?

I know there is an option for this in the basic option but I had to customize a few things for this report.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

mark_wiggans

Here is one from the Content Sharing Zone that addresses the last 30 days...

FormerMember

Here is a good source for the different time frame selects:

http://databaseadvisors.com/pipermail/dba-sqlserver/2003-September/001109.html

***snippet of my SQL report, which calculates 2hrs Utilizations for the Last Month

-- Reporting time frame - Last Month
-- ReportPeriod specifies for which month the report should be generated, 1 = Last Month, 2 = Month before Last Month
DECLARE @ReportPeriod int
Set @ReportPeriod=1

SELECT

Bandwidth='1.) Utilization/120min >= 80% for Bandwidth <=2Mbit/s',
A_BWpre.Region,
A_BWpre.SPID,
A_BWpre.NodeName,
A_BWpre.VRF,
A_BWpre.Recv_Bandwidth,
A_BWpre.SummaryDateTime,
A_BWpre.AVERAGE_of_Recv_Percent_Utilization

FROM

(SELECT

  Nodes.Caption AS NodeName,
  Interfaces.VRF AS VRF,
  AVG(Case InBandwidth When 0 Then 0
   Else (In_Averagebps/InBandwidth) * 100
   End) AS AVERAGE_of_Recv_Percent_Utilization,
  Nodes.Region AS Region,
  Nodes.SPID AS SPID,
  Interfaces.InBandwidth AS Recv_Bandwidth,
  ---- Average over 2hrs ....*12)/12
  Convert(DateTime,Floor(Cast(DateTime as Float)*12)/12,0) AS SummaryDateTime

FROM
  (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
  -- Last Month
  DatePart(m, DateTime) = DatePart(m, DateAdd(m, -@ReportPeriod, getdate())) AND
  DatePart(yyyy, DateTime) = DatePart(yyyy, DateAdd(m, -@ReportPeriod, getdate()))

FormerMember

Here an example for Last XX days

--------- TRANSMIT ---------------

--VAR Definition

-- Reporting time frame - Last XX days
-- ReportPeriod specifies for how many days back the report should be generated
DECLARE @ReportPeriod int
Set @ReportPeriod=7

SELECT

Bandwidth='1.) Utilization/120min >= 80% for Bandwidth <=2Mbit/s',
A_BWpre.Region,
A_BWpre.SPID,
A_BWpre.NodeName,
A_BWpre.VRF,
A_BWpre.Xmit_Bandwidth,
A_BWpre.SummaryDateTime,
A_BWpre.AVERAGE_of_Xmit_Percent_Utilization

FROM

(SELECT

  Nodes.Caption AS NodeName,
  Interfaces.VRF AS VRF,
  AVG(Case OutBandwidth When 0 Then 0
   Else (Out_Averagebps/OutBandwidth) * 100
   End) AS AVERAGE_of_Xmit_Percent_Utilization,
  Nodes.Region AS Region,
  Nodes.SPID AS SPID,
  Interfaces.OutBandwidth AS Xmit_Bandwidth,
  ---- Average over 2hrs ....*12)/12
  Convert(DateTime,Floor(Cast(DateTime as Float)*12)/12,0) AS SummaryDateTime

FROM
  (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
  -- last XX days
  ( DateTime >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())- @ReportPeriod,0) ) AND
  ( DateTime < DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)   ) AND

savell

Here's an SQL where statement I use for some of our monthly reports (returns date range for previous month)..

WHERE
( DateTime >= dateadd(mm,datediff(mm,0,getdate())-1,0) AND DateTime
<dateadd(mm,datediff(mm,0,getdate()),0) )

Dave.

Edit: very similar to the method in the above post now I look at it!

pchaves

Thanks Everyone for your help. I was able to get this working...finally.

THANKS AGAIN!

cnorborg

I hate replying to a post that is already solved, but I didn't see the method I use to try and figure out how the Solarwinds guys accomplish things in reports.

For example, in this case you want to know how to do a report for the previous month. There are a ton of Orion reports that are for "Last Month" already and they work quite well, wouldn't it be great to know how they did it? You can quite easily!!

Just open any report in Report Writer that has the feature you want in it, in this case one of the reports that has a timeframe of "Last Month". Lets say the Availability report "Availability: Last Month" for instance. Once you have the report opened, go to the "Report" menu and choose "Show SQL".

At this point, another tab will open in the report you were just looking at that will show the exact SQL that is used to produce that report.

Makes it easy to learn off the professionals, a great way to get ideas on how to accomplish something!!

savell

It's a good tip and something I also use. However it's unfortunately not quite that simple in some instances.

Have a look at how the SQL looks when Report Writer generates the report - any datetime ranges are generated at the time the report is run (i.e. an absolute date range is generated rather than a relative one).

So you end up with something like the following for yesterday's data....

WHERE
( DateTime BETWEEN 39819 AND 39820 )

This is fine if the SQL is run today - but if the same statement is run tomorrow, you may be disappointed with the result...

Dave.

FormerMember

Try these. I got them from Support a while back.

Replace the generated absolute statement like...

WHERE
( DateTime BETWEEN 39819 AND 39820 )

with...

For 'Last 24 Hours':

WHERE ColumnName >= GetDate () -1

For 'Last 7 Days':

WHERE DateTime >= GetDate () -7

For 'Last 30 Days':

WHERE DateTime >= GetDate () -30

For 'Last Month':

WHERE MONTH(ColumnName) = (MONTH(GETDATE()-DAY(GETDATE())))
AND YEAR(ColumnName) = YEAR(GETDATE()-DAY(GETDATE()))

For 'This Month':

WHERE MONTH(ColumnName) = (MONTH(GETDATE())) AND YEAR(ColumnName) = YEAR(GETDATE())

Replace 'ColumnName' with the name of the column that contains the DateTime information. In most cases, it is DateTime, but some may vary. You would want to check the table columns to get the appropriate column name.

BakerD

In one of my reports on the Time Frame tab I set it to last 30 days. Then on the Filter Results tab I have two lines that narrow it down to working hours for me of 8am - 6pm.

Records where Time of Day (24 hour format) is less than 08:00

Records where Time of Day (24 hour format) is greater than or equal to 18:00

I use this ina filter to give me WAN utilization for the last 30 days during working hours.