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.

SQL web/report writer at odds over my query...

rcannon2 over 9 years ago

I'm very new to SQL. I've gotten the hang of it, but it's likely that this isn't a problem with solarwinds at all, but with my code. That said, I'll explain.

For each one of our alert categories we have 3 alerts, tiers 1 2 and 3. I'm looking to make identical reports for each of those categories wherein I have a list of the fired alerts from the last 7 days, but in the event that more than one tier fired for the same node only the top tier is included. This is to weed out unnecessary duplicates as when each tier is hit it's going to be relevant to the same event.

So I got my queries together and working just fine in sql management studio. They also work just fine in the legacy report writer. However when I go to the web report to make a custom chart and select sql, then paste the same query over solarwinds tells me it is not a valid query. I went looking around for somewhere that broke down what solarwinds considers valid, but I have had no luck finding something comprehensive enough to get me to the root of my problem.

If anyone could help me out, or at least point me in the direction of a comprehensive guide of what solarwinds considers a valid query that would be appreciated.

0 zackm over 9 years ago

can you paste your query in here so we can see it?
Cancel
Vote Up 0 Vote Down

Cancel
0 njoylif over 9 years ago in reply to zackm

another option (haven't tested in web report but worked in writer) is to create stored procedure with your SQL that works and just call it from the SW report.
Cancel
Vote Up 0 Vote Down

Cancel
0 rcannon2 over 9 years ago in reply to njoylif

This is a good idea. Gonna post my query anyway because I'm really wondering why it's not working in the web but working in report writer.
Cancel
Vote Up 0 Vote Down

Cancel
0 rcannon2 over 9 years ago in reply to zackm

with VOLweekly as
(
select
'Name' = al.ObjectName,
'Date' = cast(al.LogDateTime as date),
'Day' = DateName(dw,al.LogDateTime),
'Time' = Right(al.LogDateTime, len(al.logdatetime)-11),
ad.AlertID,
al.objectid,
al.message
from solarwindsorion.dbo.AlertLog al
inner join
(
select
AlertDefID,
'AlertID' = case
when charindex('1',Alertname) > 0 then 'Vol 1'
when charindex('2',Alertname) > 0 then 'Vol 2'
when charindex('3',Alertname) > 0 then 'Vol 3'
end
from solarwindsorion.dbo.AlertDefinitions
where
charindex('Volume Utilization',Alertname) > 0
) ad
on ad.AlertDefID = al.AlertDefID
where
charindex('Alert Triggered',al.Message) > 0
and charindex('Email',al.Message) = 0
)
select distinct
Name,
Date,
Day,
Time,
AlertID
from VOLweekly rpt
where not exists
(
select 1 from VOLweekly chk
where
chk.Name = rpt.name
and chk.AlertID > rpt.AlertID
)
and Date > dateadd(dd,-8,(cast(getdate() as date)))
order by date
Cancel
Vote Up 0 Vote Down

Cancel
0 njoylif over 9 years ago in reply to rcannon2

I'm impressed it worked in writer...I came up w/ the stored procedure thing b/c I couldn't get case to work in writer...that might be why it doesn't work in web...not sure.
Cancel
Vote Up 0 Vote Down

Cancel