External logs collection and monitoring

Essentially I am looking for a way to externally store db
audit logs and to be able to parse the data or filter for specific events an
d
ids for review by a security team. Something less manual than copying trace
files from the server to another server and going over each using profiler
(we're talking about 30 servers here!)... but not necessarily as hands-off a
s
flagging and email alerting only.
In going through support docs and threads here a couple questions have also
arisen...
Does the table the trace dumps to have to be part of the local db? Is it
possible to have the trace dump to an external db server?
In order to have trace dump the output to a table, does this require setting
up a job using SQL Trace stored procedures or can it be done just by changin
g
the server's auditing configuration?Server side traces (i.e. without using the Profiler GUI) can only write to
trace files. You can load trace files into a sql table (for example on a
central "audit" server) using the sytem function fn_trace_gettable. See BOL
for details
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JMBickham" <JMBickham@.discussions.microsoft.com> wrote in message
news:AF4060D6-6986-4999-85E3-5B090EC39A53@.microsoft.com...
> Essentially I am looking for a way to externally store db
> audit logs and to be able to parse the data or filter for specific events
> and
> ids for review by a security team. Something less manual than copying
> trace
> files from the server to another server and going over each using profiler
> (we're talking about 30 servers here!)... but not necessarily as hands-off
> as
> flagging and email alerting only.
> In going through support docs and threads here a couple questions have
> also
> arisen...
> Does the table the trace dumps to have to be part of the local db? Is it
> possible to have the trace dump to an external db server?
> In order to have trace dump the output to a table, does this require
> setting
> up a job using SQL Trace stored procedures or can it be done just by
> changing
> the server's auditing configuration?sql

expression-based connection strings difficult to manage

Expression-based connection strings are great but they are specific to each report which makes them difficult to maintain. Shared expression-based connection strings are not supported.

I'm looking for a way around this. I'm hoping to write a custom data extension, and in the custom data extension, do all the "dynamic" logic that sets the connection string (which you normally would be doing in your expression-based connection string).

I know the above can be done. What I don't know is how, inside a custom data extension, to get the value of expressions like User!UserID? (I want the connection to run under stored credentials, but I want to customize the connection string and add a property to it based on the UserID who is logged into Report Manager.)

Create a assembly with a static method that takes a text parameter(userid) and returns the connectionstring and add that assembly (look in BOL for custom code or something like that on how to add it to a report and deploy the assembly to the report server) and then in the datasource it is something like this =Assembly.Class.Function(User!UserID).

I have done it myself, but in my case it was depending on the reportpath.|||

Interesting workaround, SteffoS. Thanks for the reply. That may be the best available. The only downside is that you have to actually use expression-based connection strings. And in doing that, there are two unfortunate problems:

1. Every report must call that expression instead of doing development as normal

2. (More importantly) you can't preview your dataset when it's tied to an expression-based connection string. The workaround to that is switching to a shared datasource during development, then making it an expression before you deploy, but that's a pain in the rear.

Anyone else have thoughts?

Expression Unique ID lookup

I've got a report that is pulling timesheet information for specific teams.
The team name is OC_NAME and the unique ID is CODE_UID. The timesheet table
just has an entry for the team's Unique ID which I'm returning in a dataset.
Can I do a lookup in Reporting Services Expression to change the CODE_UID
field to the OC_NAME data?
CREATE TABLE [dbo].[MSP_OUTLINE_CODES](
[CODE_UID] [int] NULL,
[OC_NAME] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULLHello Colin,
No, you could not use the Expression to lookup it. You need to join the
table in the dataset.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Exporting sql data to specific cells

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Quote:

Originally Posted by Edgar

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Have you experimented with the MS Query option in Excel? Underneath "Data" + "Import External Data" + "New Database Query" you can connect to your SQL Server datasource and query for the values you are looking for. There are some limitations however (i.e. can't run stored procedures that take parameters directly) but seems to cover a lot of the things people want to do in Excel.

|||To export data from SQL Server table to Excel file, create an Excel
file named testing having the headers same as that of table columns and
use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Bala|||Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar|||Does the data need to be fetched each time the spreadsheet is
recalcuated?|||

Quote:

Originally Posted by Edgar

Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar

If you use the External Data option I described, then you can exactly what you are looking for.

In your example, you want to put a value into c10. You use the "Import External Data" feature to connect / build / write your SQL Query. As part of this process you'll tell Excel what cell you want the data populated into, so select c10 (note: a column header is returned so you either need to put the results into c9 so your data is in c10, or put the results on a different sheet or far removed cell of the same sheet and then make c10 = the cell that the data is actually returned to.

|||I have done this a couple of different ways depending on the situation.
Here are a few ideas that might be helpful:

If there are not very many cells that would get updated then I would
create a custom excel function within the workbook that connects to SQL
Server, retrieves the value and then puts it in the cell. Using this
approach, the cell can move around the spreadsheet and you don't have
to change any of the code. This can also get more flexible as you
could pass in variables to the function. Make sure to set the function
to "volatile" or it will not recalc each time the spreadsheet is
recalc'd. Also note that this is solution can get annoying to the user
as it slows the worksheet calculation since each time it is
recalculated, a connection and query has to be made to SQL Server.
This can be aided by creating a connection to SQL server when the
spreadsheet is opened. If that is the case then I would make sure to
ask the user if they want to connect, store the response, and build it
into an if statement so that the function does time out a call to the
database server each time.

If there are a lot of cells to update, then I would suggest adding a
custom dropdown menu to the standard excel menu that has a button to
recalculate the "special" cells that get data from SQL Server. This is
more efficient in that you don't need to create, query, and then close
a connection for each cell - this requires a little more work to set up
the menu. Another issue is finding the cells that need to be updated.
You could do this with your custom menu too and store the result which
works well. I have also used cell comments to flag cells to receive
data since the comments are stored in a list that is easy to iterate
through.

Perhaps another solution would be to use the msft wizard under the data
menu and create a new database query - this has many limitations but is
very easy to set up.

A few to think about anyway, hopefully one of those is useful...|||The data is updated on a weekly basis. I mentioned I have a scalar
value. But I think I can fetch one row with 3 columns. The cells I
want to update on the Excel are: C4, C7 and C10.
The integer values are used in the excel formulas.

So far, I've tried using a DTS with activex to test just one particular
cell. But the code was updating the cell below C4 which I can't figure
out why. Here is the ActiveX code I'm using:

Function Main()

Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.add
Set oSheet = newBook.Worksheets(1)

'Specify column names.
oSheet.Range("C4").Value = "D"

DTSGlobalVariables ("FileName").Value = "C:\MyExcel.xls"

With newBook
.SaveAs DTSGlobalVariables("FileName").Value
.Save
End With

appExcel.quit

'dynamically specify the destination Excel file

set oPackage = DTSGlobalVariables.parent

' connection 2 is to the Excel file

set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Again, thank you for all your input and/or directions.

Edgar J.|||Hi Edgar,

You haven't mentioned why this approach won't work or isn't appropriate, so I'll elaborate.

First, add a new worksheet to your workbook. Call it whatever you want...for now I'll refer to it as "scaler_sheet". Also, as an example, I'll pretend your current worksheet you are referring to (the one you want the data in) is called "live_sheet". Make sure "scaler_sheet" is the active sheet...

From the "Data" Menu, choose "Import External Data" + "New DB Query". This will open MS Query. Choose "New Data Source" and then "Ok". Give your datasource a name and choose the "SQL Server" driver. Choose "Connect". This should open the SQL Server login box. Enter your server name or IP, login id, password. Click on the "Option" button and set your default db. Click "Ok". You should be back at the "Create New Data Source" dialogue. Leave "Choose Default Table" blank. Choose "Ok" and you can now tell Excel to use your new datasource (highlight it and click "Ok"). This will open MS Query...from here you can choose tables (if you want) or write your SQL directly ("Cancel" out of the table selection box and then click on the "SQL" button in the tool bar).

Depending on how complex your SQL is, MS Query may complain that it can represent your query graphically (no big deal). When your query results are displayed, choose "File" + "Return Data to Microsoft Excel". You'll be prompted to choose the cell where you want the data returned (i.e. scaler_sheet!A1). The data will be displayed with the column headers in A1, B1, and C1 and the data in A2, B2, and C2 respectively. Now, from your description above, you said you need your SQL data to appear in C4, C7, and C10. So, make this sheet the active sheet, and then reference the data from the appropriate cells from scaler_sheet (i.e. live_sheet!C4 = scaler_sheet!A2...live_sheet!C7 = scaler_sheet!B2...and live_sheet!C10 = scaler_sheet!C2).

I believe you will find this will work very well for you, and the data need only be refreshed when you want it to be.

Keith