Auditing Reports

We have a many reports in many folders. We are now at the point that we need
some kind of tool to audit all these reports for following:
Security
connectionStrings
Data Sources
ReportLocations
I saw in ReportServer database there are all kind of tables which has all of
these information, but I was wondering if there is a solution out there to
query those tables to achive this goal.
Thanks
VipulHi,
Ofcourse you can use those system tables, but with care. each table is
useful for collecting information. Infact the table name are self explanatory
and you can query it for info.
Amarnath
"Vipul Shah" wrote:
> We have a many reports in many folders. We are now at the point that we need
> some kind of tool to audit all these reports for following:
> Security
> connectionStrings
> Data Sources
> ReportLocations
> I saw in ReportServer database there are all kind of tables which has all of
> these information, but I was wondering if there is a solution out there to
> query those tables to achive this goal.
> Thanks
> Vipul
>|||Do you know of any available sample queries to query those tables?
"Amarnath" wrote:
> Hi,
> Ofcourse you can use those system tables, but with care. each table is
> useful for collecting information. Infact the table name are self explanatory
> and you can query it for info.
> Amarnath
>
> "Vipul Shah" wrote:
> > We have a many reports in many folders. We are now at the point that we need
> > some kind of tool to audit all these reports for following:
> > Security
> > connectionStrings
> > Data Sources
> > ReportLocations
> >
> > I saw in ReportServer database there are all kind of tables which has all of
> > these information, but I was wondering if there is a solution out there to
> > query those tables to achive this goal.
> >
> > Thanks
> > Vipul
> >

Auditing Data Changes

I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?

Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?

The idea behind type 1 changes is that the changes AREN'T tracked. That's the point of type-1 SCDs. Type 2s ARE tracked by their very nature, so reporting on them should be easy. Type 2 records that have changed have "expired" records either indicated by some current flag or by some end date field.|||

Hi Jrp210,

I know of no articles or white papers on this, although some probably exist.

In my mind, this is ETL metadata. In larger ETL, I currently build a database that tracks row counts for the source data and then for different types of changes (including no change). It provides a the means for a quick "sanity-check" at the end of each load: I add up the rows coming out of the SCD Wizard outputs and see if this total matches the rows going in.

If I'm understanding you, you would like to take this to another level and store enough information to identify the rows that have changed, along with which type of change was made.

To tackle this scenario, I would probably use a small field appended to each destination dimension and fact table - tinyint would probably do the trick. If you make this field Null-able, I would start by updating this field - setting it to Null - before each load. For instance, let's say you have a table called dbo.DimStuff that looks like this:

StuffSK int identity(1,1),

StuffBK varchar(50),

StuffName varchar(50)

You could add another field:

StuffChangeType tinyint NULL

When your SSIS package starts, you could include an ExecuteSQL Task with the following statement:

UPDATE dbo.DimStuff

SET StuffChangeType = NULL

This would set all the StffChangeType values to Null prior to executing the load. Then you could add a Derived Column to each output of the Slowly Changing Dimension Wizard that adds a column named StuffChangeType and assigns an appropriate value (maybe 1 for updates, 2 for historical, 3 for new, etc.)

This would add change type data for each row changed - and let you know which rows were not changed.

You could then execute a query like:

SELECT StuffBK, StuffChangeType

FROM dbo.DimStuff

This would give you a picture of the business key and change types. You could qualify with a where clause:

WHERE StuffChangeType IsNotNULL

to only get changes (and change types) since the last load.

You could also do a nice summary with a query like:

SELECT StuffChangeType,Count(*)AS'ChangeTypeCount'

FROM dbo.DimStuff

WHERE StuffChangeType IsNotNULL

GROUPBY StuffChangeType

Hope This Helps,

Andy

|||But Andy, if you want to track Type 1 changes, make them Type 2. When using Type 2 SCDs, use begin and end dates to identify current and historical rows. The auditing is done for you by the nature of SCDs. That's the point of them in the first place. If you want to keep track of which LOAD processed the change, add another column, SessionKey, or something like that.|||

Hi Phil,

I think he's asking for something different from SessionID, and for something more than just Type 2 tracking. To me, it sounds as if he has a business requirement to update certain data - or perhaps lacks a business requirement to track changes on that data - and still wants to track the fact that it changed. I don't think SCDs cover that, and I think the solution I offered does. It's not the only way, but I think it will accomplish what he's after.

If not, perhaps it will help someone else in the future!

Thanks,

Andy

|||Along the path you're thinking Andy, the Kimball Webcast on the first page of this forum (towards the top) talks about auditing. In there, an audit entry is recorded and an identity value is returned that is used within the applicable pieces within SSIS. This value would correspond to the "session" of this load, for this package, etc... Talks about row counts, and other things. Very valuable.|||

Hi Phil,

Yep - that's good stuff.

I don't think that's what the original question was about, but it's definitely good stuff.

Thanks!

Andy

|||

Thanks for the responses. The SCD does do what it is intended to do but I probably should clarify a bit.

Perhaps an example will be the best way to explain.

For the sake of the example I have the following fields in my table:

CompanyKey

CompanyName

CompanyTicker

StartDate

EndDate

The SCD is setup to create historical changes for the name and ticker. This is done with StartDate and EndDate fields. Phil, you are right that I will have a date in the EndDate field if a change comes through for a CompanyName or CompanyTicker. But what I don't know unless I compare the two rows is what exactly changed. Now this is fairly easy to eyeball given this simple scenario. But in my real life scenario I have many fields that could have changed and its not easy to tell which one(s) did. I would like to create a way to determine which of the fields did change.

Hope this makes sense.

attaching subscription database woes

I'm trying to get to a point where I can implement structural changes to a
merge replication system. My plan is to ship copies of a subscription
database to enduser outside the corp firewall and write some code to load
the new subscription database. I've done the following:
1. create a subscription db on the same server as the publisher
2. use the 'copy a subscription database' menu choice to create a copy of
it.
3. move the copy to target server.
4. in the replication folder on the target server select: 'attach
subscription database', select the msf file, specify a name for the db.
5. run sp_addMergeSubscription on the publication server as follows:
exec sp_addmergesubscription
@.publication = 'rep_test_hub',
@.subscriber = 'k-server',
@.subscriber_db = 'rep_test_sub2',
@.subscription_type = 'pull',
@.sync_type = 'none'
6. on the publisher - drill down to the publisher list, refresh it and note
the addition of 'rep_test_sub2'. At this point the status is 'deactivated'
7. synch with the publisher on subscriber
The above works fine when both pub and sub are on the same box. When I try
this on another pc in a peer-peer network I get the following error:
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 203
Date: 1/20/2006
Time: 1:20:32 PM
User: N/A
Computer: k-HOME
Description:
SubSystem Message - Job
'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
(0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not connect
to Distributor 'K-SERVER'. Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'.
Why is the synch attempt using an 'anonymous logon'?
I've checked my SQL-Agent properties and it is setup for 'SQL-Server
Authentication' in the connection tab on both systems. Both server have
identical 'sa' passwords. The startup account is 'system account' on the
sub PC and a domain admin account on the hub PC. The sub PC is not part of
the domain.
Any suggestions?
Thanks.
Run SQL Server agent under a local admin account on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"astro" <astro@.bcmn.com> wrote in message
news:U2bAf.38900$7S.35782@.tornado.rdc-kc.rr.com...
> I'm trying to get to a point where I can implement structural changes to a
> merge replication system. My plan is to ship copies of a subscription
> database to enduser outside the corp firewall and write some code to load
> the new subscription database. I've done the following:
> 1. create a subscription db on the same server as the publisher
> 2. use the 'copy a subscription database' menu choice to create a copy of
> it.
> 3. move the copy to target server.
> 4. in the replication folder on the target server select: 'attach
> subscription database', select the msf file, specify a name for the db.
> 5. run sp_addMergeSubscription on the publication server as follows:
> exec sp_addmergesubscription
> @.publication = 'rep_test_hub',
> @.subscriber = 'k-server',
> @.subscriber_db = 'rep_test_sub2',
> @.subscription_type = 'pull',
> @.sync_type = 'none'
> 6. on the publisher - drill down to the publisher list, refresh it and
> note the addition of 'rep_test_sub2'. At this point the status is
> 'deactivated'
> 7. synch with the publisher on subscriber
> The above works fine when both pub and sub are on the same box. When I
> try this on another pc in a peer-peer network I get the following error:
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 203
> Date: 1/20/2006
> Time: 1:20:32 PM
> User: N/A
> Computer: k-HOME
> Description:
> SubSystem Message - Job
> 'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
> (0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not
> connect to Distributor 'K-SERVER'. Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'.
> Why is the synch attempt using an 'anonymous logon'?
> I've checked my SQL-Agent properties and it is setup for 'SQL-Server
> Authentication' in the connection tab on both systems. Both server have
> identical 'sa' passwords. The startup account is 'system account' on the
> sub PC and a domain admin account on the hub PC. The sub PC is not part
> of the domain.
>
> Any suggestions?
> Thanks.
>
>

Attach or append an Office document to a report

I have an immediate need to embed or attach an Office document (Word, Excel and Power Point) into or to a report.
Say that I have 5 rows of data and each row has a physical file that I want to include in the report just like you would an image.
Can someone point me in the right direction? Is this even possible?
Thanks,
Ryan Pedersen
--= Posted using GrabIt =--
--= Binary Usenet downloading made easy =--
-= Get GrabIt for free from http://www.shemes.com/ =-The closest you can get is to add a "Jump to URL" navigation action and
generate a URL that points to the Office documents. The target location of
the documents can be a network share, or you can just directly upload the
Office documents as resources on the report server and then generate a URL
that points back to the report server virtual directory and uses RS URL
access to identify the resource stored in the report server database.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Pedersen" <rpedersen@.gensx.com> wrote in message
news:%23L%23qmELtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I have an immediate need to embed or attach an Office document (Word, Excel
>and Power Point) into or to a report.
> Say that I have 5 rows of data and each row has a physical file that I
> want to include in the report just like you would an image.
> Can someone point me in the right direction? Is this even possible?
> Thanks,
> Ryan Pedersen
>
> --= Posted using GrabIt =--
> --= Binary Usenet downloading made easy =--
> -= Get GrabIt for free from http://www.shemes.com/ =-
>