|
pmarion
Starting Member
2 Posts |
 Posted - 2008-04-03 : 11:02:37
|
Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions. I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data. My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand. One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there. Thank you in advance.
SELECT convert(nvarchar(20), AlertDateTime,120) AlertDateTime, AlertName, AlertPriority, AlertCount, convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' + convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' + convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' + convert(varchar,((convert(bigint,SrcAddressInt) % 256))) SrcAddressInt, SourcePort, SourcePortName, convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' + convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' + convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' + convert(varchar,((convert(bigint,DestAddressInt) % 256))) DestAddressInt, DestPortName, dbo.SensorData1.ObjectName, SensorName, SensorInterfaceName, AlertTypeID, convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' + convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' + convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' + convert(varchar,((convert(bigint,SensorAddressInt) % 256))) SensorAddressInt, ProtocolID, Cleared, VulnStatus, dbo.SensorDataAVP.SensorDataID, dbo.SensorDataAVP.AttributeName, dbo.SensorDataAVP.AttributeDataType, dbo.SensorDataAVP.AttributeText, dbo.SensorDataAVP.AttributeValue, dbo.SensorDataAVP.AttributeBlob, ResponseTypeName, ResponseNamefrom dbo.SensorDataLEFT JOIN dbo.SensorDataAVP ON dbo.SensorDataAVP.SensorDataID = dbo.SensorData1.SensorDataIDLEFT JOIN dbo.SensorDataResponse ON dbo.SensorDataResponse.SensorDataID = dbo.SensorData1.SensorDataIDLEFT JOIN dbo.ObjectView ON dbo.ObjectView.ObjectName= dbo.SensorData1.ObjectNameWHERE convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDateAND convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' + convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' + convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' + convert(varchar,((convert(bigint,SrcAddressInt) % 256))) between @LowerIP and @UpperIPAND AlertName = @EventName |
|
group by / Hiding Report Items (Newbie)