The InventoryLog Report

A year ago I blogged on the “InventoryLog” table and how it can be used to help troubleshoot hardware inventory issues. I also got a comment asking about how I wrote the query/report I showed in that post. I was reminded today about that comment and figured I should post something…a year later is better than never right?

I should (or could) probably blog about a number of things to help explain the query I just created to dynamically build the report query (which I’m going to post below) but I’m not going to do that before posting this one. There is one part of this query that does require you to have setup a “Tally Table”; I did blog on that already so have a look and make sure you create one beforehand.

The SQL code below you can run while connected to your CAS database and it will dynamically build the query you’ll need to copy/paste into the report (rdl) file (which I’ll get to later). I’ve highlighted a couple lines which you’ll need to look at and update to work in your environment – your DB name and the name/location of your tally table (in “database.schema.table” format).

USE [CM_CAS]; -- You need to change this!

/********************************************************************************************
Purpose: This script will build a query that will get and consolidate the InventoryLog data
         from all primary sites using linked server queries.

Modification History:
Date            Version    Who                  What
05/30/2019      1.0        Benjamin Reynolds    Initial Creation

Notes:
 - You need to create a Tally Table and update the script to use the tally table.
   - I create my Tally Table as "dbo.TallyTable" in a database named "DBA", so if you don't
     do the same update the line "DBA.dbo.TallyTable" to use your table name/location. (Line 99)
 - This script will only print out the query you can use if you've already got linked servers
   created for each primary site.
 - The query this builds will only work as is in the rdl file since some parameters were created
   in Report Builder. To use in SQL uncomment out the first two DECLAREs and assign values.
 - The dates in this assume that @@DATEFIRST is set to 7.
********************************************************************************************/

SET NOCOUNT ON;

DECLARE  @ID int
        ,@CMDBName nvarchar(6)
        ,@LnkdSrvrName nvarchar(512)
        ,@PrimarySqlText nvarchar(max) = N''
        ,@ErrTxt nvarchar(max);

DECLARE @Servers table ( ID int PRIMARY KEY CLUSTERED
                        ,CMDB nvarchar(6)
                        ,LinkedServerName nvarchar(512)
                        ,LinkedServerCreate tinyint
                        );

INSERT @Servers
SELECT  srv.ID
       ,srv.ConfigMgrDatabase AS [CMDB]
       ,COALESCE(lnk.name,lk2.name,SUBSTRING(srv.SQLInstance,1,CHARINDEX(N'.',srv.SQLInstance,1)-1)) AS [LinkedServerName]
       ,CASE WHEN COALESCE(lnk.name,lk2.name) IS NULL THEN 1
             ELSE 0
        END AS [LinkedServerCreate]
  FROM dbo.ServerData srv
        LEFT OUTER JOIN sys.servers lnk
          ON SUBSTRING(srv.SQLInstance,1,CHARINDEX(N'.',srv.SQLInstance,1)-1) = lnk.name
        -- this is so we can do a COALESCE above so we don't get multiple records using an OR in the JOIN
        LEFT OUTER JOIN sys.servers lk2
          ON srv.SQLInstance = lk2.data_source
 WHERE srv.ServerRole = N'Peer';

IF (SELECT SUM(LinkedServerCreate) FROM @Servers) > 0
GOTO LinkedServersNeedCreating;

WHILE EXISTS (SELECT * FROM @Servers)
BEGIN
    SELECT  TOP 1 @ID = ID
           ,@CMDBName = CMDB
           ,@LnkdSrvrName = LinkedServerName
      FROM @Servers
     ORDER BY ID;
    
    SELECT @PrimarySqlText += N'SELECT  N'''+@CMDBName+N''' AS [DatabaseName]
       ,inv.LogText
       ,prd.StartOfPeriod
       ,prd.EndOfPeriod
       ,prd.PeriodNum
       ,COUNT(1) AS [NumRecords]
       ,COUNT(DISTINCT MachineID) AS [NumDistinctMachines]
  FROM ['+@LnkdSrvrName+N'].'+@CMDBName+N'.dbo.InventoryLog inv
       INNER JOIN TimePeriods prd
          ON inv.LogTime BETWEEN prd.StartOfPeriod AND prd.EndOfPeriod
 WHERE inv.LogTime >= (SELECT StartOfPeriod FROM TimePeriods WHERE PeriodNum = @TimePeriods)
 GROUP BY  inv.LogText
          ,prd.StartOfPeriod
          ,prd.EndOfPeriod
          ,prd.PeriodNum';

    IF (SELECT COUNT(1) FROM @Servers WHERE ID > @ID) > 0
    SELECT @PrimarySqlText += NCHAR(13) + N'UNION ALL ' + NCHAR(13);
    ELSE
    SELECT @PrimarySqlText += NCHAR(13) + N') dta
       INNER JOIN SiteCounts cnt
          ON dta.DatabaseName = cnt.ConfigMgrDatabase;'

    DELETE @Servers
     WHERE ID = @ID;
END;

PRINT N'--DECLARE @TimePeriods tinyint = 4;
--DECLARE @SiteID nvarchar(8) = N''CAS00001'';
DECLARE @SparklineStart datetime;
-- This assumes @@DATEFIRST is = 7:
SELECT @SparklineStart = DATEADD(day,1,DATEADD(day,(1-DATEPART(weekday,GETUTCDATE()))+(@TimePeriods*-7),CONVERT(date,GETUTCDATE())));

;WITH TimePeriods AS (
SELECT  @SparklineStart-((N-1)*-7) AS [StartOfPeriod]
       ,DATEADD(ms,-2,@SparklineStart-((N-1)*-7)+7) AS [EndOfPeriod]
       ,@TimePeriods-(N-1) AS [PeriodNum]
  FROM DBA.dbo.TallyTable -- You may need to change this
 WHERE N  0
)
SELECT  cnt.SiteCode
       ,dta.LogText
       ,dta.StartOfPeriod
       ,dta.EndOfPeriod
       ,dta.PeriodNum
       ,dta.NumRecords
       ,dta.NumDistinctMachines
       ,cnt.AssignedCount
  FROM (
';
PRINT @PrimarySqlText;
GOTO EndScript;

LinkedServersNeedCreating:
SELECT @ErrTxt = COALESCE(@ErrTxt + ', ', '') + LinkedServerName
  FROM @Servers
 WHERE LinkedServerCreate = 1
 ORDER BY ID;
PRINT N'ERROR: LinkedServers Need to be Created in order for this script/query to work!';
PRINT N'';
PRINT N'       Create a Linked Server for the following Servers before running this script again:';
PRINT N'';
PRINT @ErrTxt;

EndScript:
GO

Now that you have the query ready, you need to download this rdl file. Then update the query in “DataSet1” (copy and paste the query that is printed out from the above query – DO NOT use the above query in the report!).
To do that, right click on “DataSet1” and choose “Dataset Properties”:
HWINV Log Info - Update DataSet
Next, paste the query created (it will be printed out in text in SSMS) into the section that says “Query”:
HWINV Log Info - Dataset Properties

Now, update the “DataSource1” by right clicking on “DataSource1” and choose “Data Source Properties”:
HWINV Log Info - DataSource Properties

Then add your CAS DB as the data source – either “Browse” to one that is there or create a new one.
HWINV Log Info - Data Source Properties

You can now go ahead and pretty up the report all you want – I didn’t really do that since I was looking for something quick.

I hope this helps those of you who were waiting on this! 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s