Its all well and good writing a lot of policies for PBM. but its only really helpful if you look at what your pulling out. If your using EPM you can get a bunch of reports out the box if you set up the SSRS reports. But i wanted a quick way to look at some results showing current 30 days ago results and the oldest results i have. (with is currently set to 90 days). With that i wrote the below script which lets me quickly gather what i want and update the top variables to set to Server and Policy.

The query uses SQLCMD so make sure you have that enabled. and update the top 3 SQLCMD variables for EPM server, Server you want to gather results for and the Policy.

EPMHistoryTable01

 

 

If you query a policy that goes against databases you get results like below:

 

 

EPMHistoryTable02

 

If you run a query against a server it will look:

EPMHistoryTable03

 

 

and the query:

/*

Query - EPM Policy History

*/
-- Connect to EPM Server
:CONNECT "EPMServerName"
-- Server to Query Policy Results from
:SETVAR Srv "INSTANCENAME"
-- Gather Results from Policy
:SETVAR Policy "DAILY - Database Space Used" 

-- Results Table
CREATE TABLE #r
    (
      [Db] VARCHAR(100) ,
      [LatestDate] DATE ,
      [LatestSize] INT ,
      [30DaysAgo] DATE ,
      [30DaysAgoSize] INT ,
      [90DaysAgo] DATE ,
      [90DaysAgoSize] INT,
    )


/* Queries */
USE MDW;
GO

-- Get Latest Date to populate table
DECLARE @latestPull DATE 
SET @latestPull = ( SELECT TOP 1  CONVERT(DATE, EvaluationDateTime)
                           
                   FROM     policy.PolicyHistoryDetail
                   WHERE    EvaluatedServer = '$(Srv)'
                            AND EvaluatedPolicy = '$(Policy)'
                   ORDER BY EvaluationDateTime DESC
                 );


INSERT INTO #r (Db,LatestDate,LatestSize) 
SELECT   SUBSTRING(EvaluatedObject,
                                  CHARINDEX('Databases', EvaluatedObject) + 10,
                                  LEN(EvaluatedObject)) AS 'Database', @latestPull,
			CONVERT(INT, ( CONVERT(CHAR(10), ResultDetail.query('/Operator/Function/ResultValue/text()')) ))
                   FROM     policy.PolicyHistoryDetail
                   WHERE    EvaluatedServer = '$(Srv)'
                            AND EvaluatedPolicy = '$(Policy)'
						AND CONVERT(DATE, EvaluationDateTime) = @latestPull 
                   ORDER BY EvaluatedObject ASC	



-- Define 30 days previous to latest date
DECLARE @30DaysAgo DATE;
SET @30DaysAgo = ( SELECT   DATEADD(DAY, -30, @latestPull)
                 );


--Add 30 Days Date
UPDATE  #r
SET     [30DaysAgo] = @30DaysAgo ,
        [30DaysAgoSize] = CONVERT(INT, ( CONVERT(CHAR(10), ResultDetail.query('/Operator/Function/ResultValue/text()')) ))
FROM    policy.PolicyHistoryDetail AS o
        JOIN #r ON #r.Db = SUBSTRING(o.EvaluatedObject,
                                     CHARINDEX('Databases', o.EvaluatedObject)
                                     + 10, LEN(o.EvaluatedObject))
WHERE   o.EvaluatedServer = '$(Srv)'
        AND o.EvaluatedPolicy = '$(Policy)'
        AND CONVERT(DATE, o.EvaluationDateTime) = @30DaysAgo
					

-- Define oldest day recorded
DECLARE @Oldest DATE;
SET @Oldest = (SELECT TOP 1  CONVERT(DATE, EvaluationDateTime)
                           
                   FROM     policy.PolicyHistoryDetail
                   WHERE    EvaluatedServer = '$(Srv)'
                            AND EvaluatedPolicy = '$(Policy)'
                   ORDER BY EvaluationDateTime ASC	
                 );


--Add 30 Days Date
UPDATE  #r
SET     [90DaysAgo] = @Oldest ,
        [90DaysAgoSize] = CONVERT(INT, ( CONVERT(CHAR(10), ResultDetail.query('/Operator/Function/ResultValue/text()')) ))
FROM    policy.PolicyHistoryDetail AS o
        JOIN #r ON #r.Db = SUBSTRING(o.EvaluatedObject,
                                     CHARINDEX('Databases', o.EvaluatedObject)
                                     + 10, LEN(o.EvaluatedObject))
WHERE   o.EvaluatedServer = '$(Srv)'
        AND o.EvaluatedPolicy = '$(Policy)'
        AND CONVERT(DATE, o.EvaluationDateTime) = @Oldest 




-- ADD TOTAL ROW

DECLARE @latestPullTotal INT
SET @latestPullTotal = (SELECT SUM([LatestSize]) FROM #r)
DECLARE @30DaysAgoTotal INT
SET @30DaysAgoTotal = (SELECT SUM([30DaysAgoSize]) FROM #r)
DECLARE @OldestPullTotal INT
SET @OldestPullTotal = (SELECT SUM([90DaysAgoSize]) FROM #r)

INSERT INTO #r
        ( [Db] ,
          [LatestDate] ,
          [LatestSize] ,
          [30DaysAgo] ,
          [30DaysAgoSize] ,
          [90DaysAgo] ,
          [90DaysAgoSize]
        )
VALUES  ( 'TOTAL' , -- Db - varchar(100)
          @latestPull , -- LatestDate - date
          @latestPullTotal , -- LatestSize - int
          @30DaysAgo , -- 30DaysAgo - date
          @30DaysAgoTotal , -- 30DaysAgoSize - int
          @Oldest , -- 90DaysAgo - date
          @OldestPullTotal  -- 90DaysAgoSize - int
        )






ALTER TABLE #r
	ADD 	[Last30DaysGrowth] AS ([LatestSize] - [30DaysAgoSize])
ALTER TABLE #r
	ADD		[Last90DaysGrowth] AS ([LatestSize] - [90DaysAgoSize])



SELECT * FROM #r


DROP TABLE #r

 

 

Advertisements