WOL TOOL (C#)

 

Scenario

 

We are supporting about 40 schools with their it systems, all computers are joined to the domain, we are running SCCM environment which we use to deploy software and operating systems to pc’s, inventory, patching etc.

In our Active Directory forest every school has its own OU.

We have one standalone SCCM server (Primary Site), and one distribution point located in every school.

I would make a tool for our support persons, which let them start, logoff, restart and shutdown pc’s in all class rooms.

It was important to have opportunities to take action in every room separately.

We don’t have VLAN’s for every room, so it is impossible to make the tool based on ip addresses. Our support persons don’t like the idea of finding mac addresses of all pc’s and save it to the txt file. A Mac address is necessary to run magic packages – Wake On Lan(WOL).

I have found out that all computer names are stored in AD and mac-address with names in sccm database. I just had to figure how to create and match two lists - one from AD with names and one from sccm database with mac addresses and computer names.

I asked our supporters to add the description for each computer in AD with the name of the room where the computer is placed.

Based on it I could create tree view with pc name and room name.

Basically, the program is based on windows scheduled task - it is a little GUI with an opportunity of setting scheduled task with triggers for each school and classroom.

 

Tool overview

 

On the main page is the list of the name of tasks. Tasks can be enabled, disabled, or we can set status to expired.

 

 

 

Main side

 

Program is writing the tasks to the Task Scheduler

 

windows Task scheduler

 

But the end user doesn’t need to think about it….

I made three buttons, one to edit task, one to add the new task, and finally one to delete task.

 

bottons

 

When we click the green button, we will be redirected to a new form where we can specify all task information like name description, task type…

 

General

 

 

 

Task 001

 

  1. There are three tabs - we need to add information to all of them.

  2. We can enable or disable task (sometimes useful in exam period or holidays)

  3. We need to specify the action

  4. We can execute task once immediately (if we would like to restart all computers in classroom before the new class is coming)

 

 

Computers

 

When we move to “Computers” tab, we get a list of computers which will be affected by the action, if we have added them to the list.

 

comp list

 

  1. We can add or remove computers, if we choose to add the new computers, we will be redirected to the new form with tree view of the computers (it is mirroring our AD description for computer. Tree view list description text up to dash (-) if we write in computer description fx “Lokale 8 – green room” it will be shown as “Lokal 8” thanks to the user who has the opportunity to add some extra information which is not necessary to display on the list)

 

tree view 001

 

  1. In the textbox we have to choose the name of the school OU and reload the list.

 

Triggers

 

 

triggers

 

  1. In triggers tab, we have to schedule time and date of task execution. Optionally, we can set expiry date.

Specification

 

Program needs two views in SCCM SQL database, one user with permission to Database and AD User with permissions to Computers.

 

SQL Views

 

Program is running SQL query where it picks up data from three views, two of which we create ourselves (ODKinvOSBIOSRAM and ODKinvNetCard).

In my query I just select all computers where name is starting with school initials and “PC”:

AND PCID LIKE @schoolname+'PC%'

You can just remove it then you get all computers, I don’t want to run it on the laptops since we have different naming standards for laptops and PC.

 

 

SQL call

 

To create Views, open SQL Management Studio and run Query (you need to change value - CM_CM1 so it matches your site-database name ) :

  1. ODKinvOSBIOSRAM

USE [CM_CM1]

GO

/****** Object: View [dbo].[ODKinvOSBIOSRAM]   Script Date: 13-01-2017 10:54:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[ODKinvOSBIOSRAM]

AS

SELECT     dbo.v_R_System.ResourceID AS SMSID, dbo.v_R_System.Name0 AS PCID, dbo.Computer_System_DATA.Model00 AS Model,

                     dbo.PC_BIOS_DATA.SerialNumber00 AS BiosSN, dbo.System_Enclosure_DATA.ChassisTypes00 AS ChassisType, dbo.Operating_System_DATA.Caption00 AS OSYS,

                     dbo.Operating_System_DATA.CSDVersion00 AS OSSP, dbo.Operating_System_DATA.Version00 AS OSver, dbo.v_R_System.Active0 AS SMSstatus,

                     dbo.v_R_System.Creation_Date0 AS FirstContact, dbo.WorkstationStatus_DATA.LastHWScan AS LastContact,

                     dbo.v_R_System.User_Domain0 + '\' + dbo.v_R_System.User_Name0 AS PriUser, dbo.PC_Memory_DATA.TotalPhysicalMemory00 / 1024 AS RAM,

                     dbo.Computer_System_DATA.NumberOfProcessors00 AS CPUantal, dbo.Computer_System_DATA.UserName00 AS PriUser2, dbo.System_Enclosure_DATA.MachineID,

                     dbo.WorkstationStatus_DATA.MachineID AS Expr1, dbo.Computer_System_DATA.MachineID AS Expr2

FROM         dbo.Operating_System_DATA FULL OUTER JOIN

                     dbo.PC_Memory_DATA FULL OUTER JOIN

                     dbo.WorkstationStatus_DATA FULL OUTER JOIN

                     dbo.System_Enclosure_DATA FULL OUTER JOIN

                     dbo.v_R_System ON dbo.System_Enclosure_DATA.MachineID = dbo.v_R_System.ResourceID ON

                     dbo.WorkstationStatus_DATA.MachineID = dbo.v_R_System.ResourceID FULL OUTER JOIN

                     dbo.PC_BIOS_DATA ON dbo.v_R_System.ResourceID = dbo.PC_BIOS_DATA.MachineID FULL OUTER JOIN

                     dbo.Computer_System_DATA ON dbo.v_R_System.ResourceID = dbo.Computer_System_DATA.MachineID ON

                     dbo.PC_Memory_DATA.MachineID = dbo.v_R_System.ResourceID ON dbo.Operating_System_DATA.MachineID = dbo.v_R_System.ResourceID

GO

  1. ODKinvNetCard

USE [CM_CM1]

GO

/****** Object: View [dbo].[ODKinvNetCard]   Script Date: 13-01-2017 10:55:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[ODKinvNetCard]

AS

SELECT     dbo.Netcard_DATA.MachineID AS SMSid, dbo.Netcard_DATA.Description00 AS Netcard, dbo.Network_DATA.IPAddress00 AS IPadress,

                     dbo.Network_DATA.IPSubnet00 AS IPsubnet, dbo.Network_DATA.DefaultIPGateway00 AS IpGateway, dbo.Network_DATA.DHCPEnabled00 AS DHCP,

                     dbo.Netcard_DATA.MACAddress00 AS MAC

FROM         dbo.Netcard_DATA INNER JOIN

                     dbo.Network_DATA ON dbo.Netcard_DATA.MachineID = dbo.Network_DATA.MachineID AND dbo.Netcard_DATA.DeviceID00 = dbo.Network_DATA.Index00

WHERE     (dbo.Netcard_DATA.MachineID IN

                         (SELECT DISTINCT MachineID

                           FROM         dbo.Netcard_DATA AS Netcard_DATA_2)) AND (dbo.Netcard_DATA.Description00 IN

                         (SELECT DISTINCT Description00

                            FROM         dbo.Netcard_DATA AS Netcard_DATA_1

                           WHERE     (Description00 NOT LIKE '%miniport%') AND (Description00 NOT LIKE '%direkte%') AND (Description00 NOT LIKE '%Eicon%') AND

                                                   (Description00 NOT LIKE '%direct%') AND (Description00 NOT LIKE '%Deterministic%') AND (Description00 NOT LIKE '%Wireless%') AND

                                                   (Description00 NOT LIKE '%asynkron%') AND (Description00 NOT LIKE '%async%') AND (Description00 NOT LIKE '%infra%') AND

                                                   (Description00 NOT LIKE '%TV/Video%'))) AND (dbo.Netcard_DATA.Description00 NOT LIKE '%cisco%') AND

                     (dbo.Netcard_DATA.Description00 NOT LIKE '%bluetooth%')

GROUP BY dbo.Netcard_DATA.MachineID, dbo.Netcard_DATA.Description00, dbo.Network_DATA.IPAddress00, dbo.Network_DATA.IPSubnet00,

                     dbo.Network_DATA.DefaultIPGateway00, dbo.Network_DATA.DHCPEnabled00, dbo.Netcard_DATA.MACAddress00

GO

  1. ODKinvMAC

USE [CM_CM1]

GO

/****** Object: View [dbo].[ODKinvMAC]   Script Date: 13-01-2017 10:53:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[ODKinvMAC]

AS

SELECT       dbo.ODKinvOSBIOSRAM.PCID, dbo.ODKinvNetCard.Netcard, dbo.ODKinvNetCard.MAC, dbo.ODKinvOSBIOSRAM.SMSstatus

FROM           dbo.ODKinvNetCard INNER JOIN

                         dbo.ODKinvOSBIOSRAM ON dbo.ODKinvNetCard.SMSid = dbo.ODKinvOSBIOSRAM.SMSID

WHERE       (dbo.ODKinvOSBIOSRAM.SMSstatus = 1) AND (dbo.ODKinvNetCard.MAC IS NOT NULL)

GO

 

Settings

 

There is a settings file with important info, which must be specific to your environment.

 

settings

 

  1. Program is picking up all data such as credentials, name of database, OU from one file – Settings

  2. Specify from which OU you want to get computers on tree view fx:

    LDAP://OU=DESKTOPS,OU=COMPUTERS,OU={0},OU=Office,OU=City,DC=domain,DC=com

    OU={0} – in our case it is a school name we write in GUI, in our AD structure every school has its own OU.

Source files:

Files part 1

Files part 2

Files part 3