This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Is it possible to get a results from following sql script in Report Writer?

Gob over 14 years ago

Hi. I want to create a Report, but I cannot get a results in Report Writer.

It's a very simple script.

IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL
DROP TABLE #t_out
create table #t_out (Cap Varchar(255), Outage int)

insert #t_out (Cap,Outage)
select 'asdasdasd',100

select *
from #t_out

DROP TABLE #t_out

0 MarieB over 14 years ago

Hi Gob--
I need a little more information in order to help you.
--what business need are you trying to fill with this report?
--from where is the data that you are putting into the db in the report originating?
Let me know,
Thanks,
M
Cancel
Vote Up 0 Vote Down

Cancel
0 Gob over 14 years ago in reply to MarieB

Hi MarieB. Thank you for your answer. I want to create a Summary Report from this topic
This report provides data on the outages duration for each node in selected time period.
This is a full version of my script. It work perfectly in MS SQL Management Studio.
---!!!!!!!!!!!!!!!!!!!!!!!!!!
set dateformat dmy
---!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @period smallint
declare @periodbegin datetime
declare @periodend datetime
declare @Sensitivity smallint

--Add here Sensitivity (min), period(days) or periodbegin/periodend==========
set @Sensitivity=25
set @period='31'
set @periodbegin=''
set @periodend=''
--======================================================================
if @period!=''
begin
set @periodbegin=(GetDate()-@period)
set @periodend=getdate()
end
if @periodend='' set @periodend=getdate()

--============================create temp table==========================
IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL
DROP TABLE #t_out
create table #t_out (Caption Varchar(255), OutageDurationInMinutes int)
--=======================================================================
insert #t_out (Caption,OutageDurationInMinutes)

--===================fill in (begin)===================
SELECT
Nodes.Caption,
------------------------------Add here another column-------------------
--address,
------------------------------------------------------------------------
DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime
WHERE
         EndTime.EventTime > StartTime.EventTime AND
         EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND
         EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime))
AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON
StartTime.NetworkNode = Nodes.NodeID
WHERE
(StartTime.EventType = 1) AND
(StartTime.NetObjectType = 'N') AND (
---------------------------------period---------------------
EventTime > @periodbegin
AND EventTime < @periodend
-----------------------------------------------------------------------------------
)and (DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime
WHERE
EndTime.EventTime > StartTime.EventTime AND
EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND
EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)))
--------------------------------------Sensitivity (min)-----------------------
                            > @Sensitivity
---------------------------------------Add here another filter---------------------
--                                             and caption like '%pix%'
-------------------------------------------------------------------------------------
ORDER BY caption asc
--===================fill in (END)===================

select caption, sum(OutageDurationInMinutes) as OutageDurationInMinutes,
cast(floor (sum(OutageDurationInMinutes)/60) as varchar(3))+':'+cast(sum(OutageDurationInMinutes)-60*floor (sum(OutageDurationInMinutes)/60) as varchar(2)) as OutageDurationInHours
from #t_out
group by caption

DROP TABLE #t_out
Cancel
Vote Up 0 Vote Down

Cancel
0 MarieB over 14 years ago in reply to Gob

Hi Gob--
Thanks for the addtional info. I'm going to mark this thread for the product manager and NPM dev to see about getting it addressed.
M
Cancel
Vote Up 0 Vote Down

Cancel
0 ondrej.salplachta over 14 years ago in reply to Gob

Hello,
there is problem with inner operations like CREATE TABLE or INSERT.
Report writer isn't able to handle these results.
Fix is very easy: put as first statement in your SQL query:
SET NOCOUNT ON
Which prevents to return results from inner operations (e.g.: "Command(s) completed successfully.").
So your SQL should look like:
SET NOCOUNT ON
---!!!!!!!!!!!!!!!!!!!!!!!!!!
set dateformat dmy
---!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @period smallint
Cancel
Vote Up 0 Vote Down

Cancel
0 Gob over 14 years ago in reply to ondrej.salplachta

Thanks a lot! It's working!
Cancel
Vote Up 0 Vote Down

Cancel
0 TXNOGAS over 14 years ago

What would be the format of periodbegin= & periodend=???
I've tried the following???
periodbegin='02/14/2010' & periodend='02/15/2010'
periodbegin='2/14/2010' & periodend='2/15/2010'
periodbegin='02142010' & periodend='02152010'
periodbegin='2142010' & periodend='2152010'
Cancel
Vote Up 0 Vote Down

Cancel
0 cdh over 14 years ago in reply to TXNOGAS

I actually hijacked this report too! :) (Thanks Gob!)
I created 2 reports - Outage Duration by Device - Last 7 Days and Last 31 Days. The only field I modified was:

--Add here Sensitivity (min), period(days) or periodbegin/periodend==========
set @Sensitivity=25
set @period='31'
set @periodbegin=''
set @periodend=''
I changed it to '7'.
Not a SQL expert by any means, but that appears to have done the trick.
Cancel
Vote Up 0 Vote Down

Cancel
0 charpham over 14 years ago in reply to Gob

Hi Gob... I am not really a SQL expert, but I do find this report very worthwhile. The one thing I would like to do is to modify this report so only certain devices are displayed (i.e. routers and switches and not firewalls) based upon a custom property field (created in custom property editor) called "device_type" I would like to display another custom property field called "Site_location". Is this possible to do this with your SQL script?... note this is very similar to the Network Node down Summary that you created that no longer works with 9.5.1--- Any help would greatly be appreciated..

chuck harpham
Cancel
Vote Up 0 Vote Down

Cancel
0 Gob over 14 years ago in reply to TXNOGAS

Hi All.

What would be the format of periodbegin= & periodend=???

Date format is dmy . If you want to use a custom period you should set @period to nothing. For example.
set @period=''
set @periodbegin='20-02-2009'
set @periodend='01-03-2009'
charpham , you can use any columns from Nodes table for your filter. Including your custom properties. Which columns do you can to use? Try this simple query
select top 10 * from nodes
in MS SQL Management studio or in Report Writer.
Then find this line in my script and correct it for self.

---------------------------------------Add here another filter---------------------
--and (device_type='router' or device_type='switch') and device_type<>'firewall'

-------------------------------------------------------------------------------------
Cancel
Vote Up 0 Vote Down

Cancel
0 charpham over 14 years ago in reply to Gob

thanks.. This worked regarding the filters... The one I would like to do is add two more "columns" to the table "Site_Location" and Device_Type" but I am not sure how this can be done.. so it would look something like this...

Caption Site_Location Device_Type OutageDurationInHours OutageDurationInMinustes
router XXXX las vegas WAN 1:4 64

thanks again for your help
Cancel
Vote Up 0 Vote Down

Cancel