Stuff I Do

Ever need to run a command line utility on a remote Windows machine?

Make sure you have PSTools installed on the workstation you are working from.
From your command line type:

psexec \\nameOfRemote cmd.exe

You will now have a command line interface on the remote workstation.

In our Active Directory domain we do not allow the clients to resolve outside domain names. This is because we have a very restrictive web browsing policy and people are only allowed access out if a manager says they can, and even at that point it goes through a proxy server that blocks 99% of the internet. The added benefit is that this really curbs any virus or spyware issues. Even though we don’t let user workstations out of the network, there are other vlans that definitely need out. At the time we were very limited on the number of Windows servers we could purchase and place so we went with Linux to provide this name server for these vlans. Below is the config and how resolve both internal and external names and I will describe what each section involves.

Line 6-14 list the subnets that are allowed access to this name server. If you are not in this subnet and you request a name resolution from this server, you will be denied.
Line 18-21 lists the name servers for outside (internet) name resolution. These are the OpenDNS ip addresses.
Line 25-33 tells named that for the domain ‘domain.com’ resolve those names to the internal Active Directory DNS servers.
Line 35-55 lists the zones for reverse name resolution. These zone names must match the reverse name in your AD DNS servers.

options {
        directory "/var/named";
        dump-file "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        version "not currently available";
        allow-query {
                127.0.0.1;
                10.1.4.0/24;
                10.1.6.0/24;
                10.1.7.0/24;
                10.1.15.0/24;
                10.2.4.0/24;
                10.2.7.0/24;
        };
        allow-transfer {
                none;
        };
        forwarders {
                208.67.220.220;
                208.67.222.222;
        };
        forward only;
};

zone "domain.com" IN {
        type forward;
        forwarders {
                10.1.100.100;
                10.1.101.101;
                10.2.100.100;
                10.2.101.101;
        };
};

zone "1.10.in-addr.arpa" {
    type slave;
    file "slaves/1.10.in-addr.arpa";
    masters {
                10.1.100.100;
                10.1.101.101;
                10.2.100.100;
                10.2.101.101;
    };
};

zone "2.10.in-addr.arpa" {
    type slave;
    file "slaves/2.10.in-addr.arpa";
    masters {
                10.1.100.100;
                10.1.101.101;
                10.2.100.100;
                10.2.101.101;
    };
};

include "/etc/rndc.key";

Our maintenance department uses a CMMS package called MP2 for all maintenance work orders. The software is based on a MSSQL back end which has allowed us to create many additional reports and web interfaces to help the maintenance staff handle the work orders. Some of the work orders are scheduled PMs and they have a due date. Part of their review is making sure they have the work orders closed on time so to help them out I created a SQL Server Reporting Services report that will email them a list of work orders that are due the next business day.

First I have to get a listing of all the work orders that are due the next business day and then if it is Friday, get all the work orders that are due up through Monday. Below is the TSQL to get that info:

select
	wo.wonum,
	woeqlist.location,
	equip.description,
	wo.taskdesc,
	wo.wotype,
	wo.atfirstname + ' ' + wo.atlastname as fullname,
	wo.schedfinishdate
from
	wo left join woeqlist on wo.wonum = woeqlist.wonum
		join equip on woeqlist.eqnum = equip.eqnum
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
order by wonum

This will return the work order number, which plant location the equipment’s name, what the title of the work order is, type of work order, who is assigned to the work order and the date it is due as a extreme reminder which day they need to complete this job.

Throw that in a Reporting Services report and we are almost ready. I don’t want to send an email unless there is actually a work order that is due for the following day. For this I create a Data Driven Subscription in the Reporting Services web page for this report.

To do this go to the report you want to send out in the Reporting Services web page and click on the Subscriptions tab. Select the New Data Driven Subscription. Give this subscription a description and select the Shared Data Sources option and click Next. Pick your shared data source and click Next. Now we will make a slightly modified query to get back an email address if any records are available.

select
	email = 'MaintenanceDept@domain.com'
from
	wo
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
group by email

This will return us just one record of MaintenanceDept@domain.com if there are any number of work orders due for the next business day.

On the next page in the TO: field select the database field email to include the needed email address. Set any other properties on this page and click Next. Set the days and hours that you want the report to run and you are done.

Now an email will go out only when there are work orders that are due tomorrow and if no work orders are do, no one gets an email.

In a later post, I will show a super cool user defined function that you can use to take into account holidays and other office closed events.