Microsoft SQL Server Archives

In this example, I am combining the data from three tables. Two of the tables have different columns and no relation or join.

I am managing a soccer team. I have a list of weeks in the soccer season. And I also have a list of players. In addition, I also have a list of weeks in which each player have confirmed.

DECLARE@WeeksTABLE

(

       WeekIdintIDENTITYPRIMARYKEY

       ,WeekDateDATETIME

)

DECLARE@PlayersTABLE

(

       PlayerIdintIDENTITYPRIMARYKEY

       ,PlayerNamevarchar(50)

)

DECLARE@ConfirmationsTABLE

(

       ConfirmationIdintIDENTITYPRIMARYKEY

       ,PlayerIdint

       ,WeekIdint

)

Using Transact SQL, I want to create a roster that combines all of the players and all of the weeks and specifies whether a player has confirmed participation for that week.

-- declare result table

DECLARE@RosterTABLE

(

       WeekIdint

       ,WeekDatedatetime

       ,PlayerIdint

       ,PlayerNamevarchar(50)

       ,IsConfirmedvarchar(3)

)

To combine all Players and all Weeks, you simply perform a select without a join.

-- combine all weeks and all players to create roster

INSERTINTO@Roster

(

       WeekId

       ,WeekDate

       ,PlayerId

       ,PlayerName

)

SELECT

       w.WeekId

       ,w.WeekDate

       ,p.PlayerId

       ,p.PlayerName

FROM

       @Weeksw

       ,@Playersp

Once you have your roster, you can perform a quick update to add whether the player has confirmed for each of the weeks.

-- perform multiple update for confirmations'

UPDATE

    @Roster

SET

    IsConfirmed=CASEWHENc.PlayerIdISNOTNULLTHEN'Yes'ELSE'No'END

FROM

    @Rosterr

    [email protected]=c.PlayerIdANDr.WeekId=c.WeekId

And here is your roster! Let’s play some soccer. : )

A customer of mine was looking for a way to verify that servers with multiple IP Addresses (mostly web severs) were configured to use a specific IP for all default communications.

SkipAsSource is a Boolean flag which allows adding new IP Addresses that should not be used for outgoing packages unless explicitly set for use by outgoing packets.

This property is not available out-of-the-box in ConfigMgr 2012 R2. It is also not populated in the root\CIMv2 namespace; however, with a bit of help from MSDN, James Kehr, and WUtils.com I found the WMI Namespace, Class, and Property (root\StandardCIMv\MSFT_NETIPAddress\SkipAsSource)

Below is a step-by-step configuring guide to capture and report SkipAsSource in ConfigMgr 2012.

Enable Inventory

To enable the custom inventory modify the Default Client Settings.

In the ConfigMgr console, navigate to Administration \ Overview \ Client Settings

Right-Click Default Settings and select properties

Select Hardware Inventory, Set Classes, Add, enter root\StandardCIMv2 for the WMI namespace, enable Recursive, and select Connect

Select the MSFT_NetIPAddress class by browsing for it or entering MSFT_NETIPAddress in the inventory class. Select OK.

Enable / select the following properties: CreationClassName, Name, SystemCreationClassName, SystemName, Caption, Description, EnabledState, InterfaceAlias, IPAddress, IPv4Address, IPv6Adress, SkipAsSource (those in bold are required)

On a SCCM client, run the Machine Policy Retrieval & Evaluation Cycle, wait about 2 minutes, then run a Hardware Inventory Cycle.

View Inventory

Back on the SCCM Console, navigate to Assets and Compliance \ Overview \ Devices

Right-Click the SCCM client used in testing and select Start \ Resource Explorer

If the inventory has completed processing, there will be a new node named MSFT_NetIPAddress which contains the new WMI properties.

Notice the values for Skip As Source, the IP Address, and the Interface Alias. Great info!

Create a Report

In Microsoft SQL Server Management Studio, connect to the CM_<SiteCode> database and run the query:

select ResourceID, InterfaceAlias0, IPAddress0, Name0 , SkipAsSource0 from CM_LAB.dbo.v_GS_MSFT_NETIPADDRESS

This will show all of the related data in the database and the same query can be used in SQL Server Reporting Services.

Create a new SQL Reporting Service report with the T-SQL above. I did this the easy way by using another report as a starting point, opening the report in Report Builder, save it as a new name, replace the existing Dataset T-SQL and drag the new columns into the Tablix.

The report file (Computers with IP SkipAsSource Info.rdl) can be found on my public OneDrive.

References

http://msdn.microsoft.com/en-us/library/hh872425(v=vs.85).aspx

http://wutils.com/wmi/root/standardcimv2/properties/skipassource.html

http://blogs.technet.com/b/heyscriptingguy/archive/2013/01/24/use-powershell-to-change-ip-behavior-with-skipassource.aspx

https://support.microsoft.com/kb/975808

Thanks, Curtis Petree for the challenge!

Using the DATEDIFF function allows you to easily calculate week days in SQL, because it both removes the time from a date and converts the date into a number for easy mathematical calculations.

Calculating Most Recent Monday

DECLARE @MostRecentMonday DATETIME = DATEDIFF(day, 0, GETDATE() – DATEDIFF(day, 0, GETDATE()) %7)

PRINT @MostRecentMonday