Creating a dummy windows service in C

13 Jan 2015

I have a load balanced web application that is hosted in a managed datacenter in another state. Because the data center is managed by a third party I am not allowed to have direct access to the load balancer for SLA purposes but I still need an ability to pull a server from the load balancer pool at will. As a work-around I want to create a Windows service that the load balancer can monitor and as long as the service is running the server should remain in the pool, if service is stopped the load balancer should pull that server from the pool and route users to a server still considered operational. The trick to this is, the service essentially does nothing but run and is something I can manually start and stop at my own discretion.

Having never created a Windows service I had to do a quite a bit of research to determine the proper way to get this going. In the end I decided on using C as the language for the service, using this article as my main source of information about accessing the Windows API. All this service does is start, then loop a sleep pause every 5 minutes and that’s it, very simple and serves the purpose.

#include <windows.h>
#include <stdio.h>

#define LOGFILE "C:\\Temp\\sleepstatus.txt"

SERVICE_STATUS ServiceStatus; 
SERVICE_STATUS_HANDLE hStatus; 
 
void  ServiceMain(int argc, char** argv); 
void  ControlHandler(DWORD request); 
int InitService();

int WriteToLog(char* str)
{
	FILE* log;
	log = fopen(LOGFILE, "a+");
	if (log == NULL)
		return -1;
	fprintf(log, "%s\n", str);
	fclose(log);
	return 0;
}

void main() 
{ 
    SERVICE_TABLE_ENTRY ServiceTable[2];
    ServiceTable[0].lpServiceName = "SleepService";
    ServiceTable[0].lpServiceProc = (LPSERVICE_MAIN_FUNCTION)ServiceMain;

    ServiceTable[1].lpServiceName = NULL;
    ServiceTable[1].lpServiceProc = NULL;
    // Start the control dispatcher thread for our service
    StartServiceCtrlDispatcher(ServiceTable);  
}

void ServiceMain(int argc, char** argv) 
{ 
    int error; 
 
    ServiceStatus.dwServiceType        = SERVICE_WIN32; 
    ServiceStatus.dwCurrentState       = SERVICE_START_PENDING; 
    ServiceStatus.dwControlsAccepted   = SERVICE_ACCEPT_STOP | SERVICE_ACCEPT_SHUTDOWN;
    ServiceStatus.dwWin32ExitCode      = 0; 
    ServiceStatus.dwServiceSpecificExitCode = 0; 
    ServiceStatus.dwCheckPoint         = 0; 
    ServiceStatus.dwWaitHint           = 0; 
 
    hStatus = RegisterServiceCtrlHandler(
		"SleepService", 
		(LPHANDLER_FUNCTION)ControlHandler); 
    if (hStatus == (SERVICE_STATUS_HANDLE)0) 
    { 
        // Registering Control Handler failed
        return; 
    }  
    // Initialize Service 
    error = InitService(); 
    if (error) 
    {
		// Initialization failed
        ServiceStatus.dwCurrentState       = SERVICE_STOPPED; 
        ServiceStatus.dwWin32ExitCode      = -1; 
        SetServiceStatus(hStatus, &ServiceStatus); 
        return; 
    } 
    // We report the running status to SCM. 
    ServiceStatus.dwCurrentState = SERVICE_RUNNING; 
    SetServiceStatus (hStatus, &ServiceStatus);
 
    // The worker loop of a service
    while (ServiceStatus.dwCurrentState == SERVICE_RUNNING)
	{
		int result;
    
	    /* Do nothing but loop once every 5 minutes */
		while(1)
		{
			sleep(300);			
		}
		
	}
    return; 
}
 
// Service initialization
int InitService() 
{ 
    int result;
    result = WriteToLog("Monitoring started.");
    return(result); 
} 


// Control handler function
void ControlHandler(DWORD request) 
{ 
    switch(request) 
    { 
        case SERVICE_CONTROL_STOP: 
             WriteToLog("Monitoring stopped.");

            ServiceStatus.dwWin32ExitCode = 0; 
            ServiceStatus.dwCurrentState  = SERVICE_STOPPED; 
            SetServiceStatus (hStatus, &ServiceStatus);
            return; 
 
        case SERVICE_CONTROL_SHUTDOWN: 
            WriteToLog("Monitoring stopped.");

            ServiceStatus.dwWin32ExitCode = 0; 
            ServiceStatus.dwCurrentState  = SERVICE_STOPPED; 
            SetServiceStatus (hStatus, &ServiceStatus);
            return; 
        
        default:
            break;
    }  
    
    // Report current status
    SetServiceStatus (hStatus,  &ServiceStatus);
 
    return; 
} 	

Now that the C file has been created, I compile this into an EXE using MinGW:

C:\MinGW\bin\gcc sleepservice.c -o sleepservice.exe

At this point I create the C:\Temp\ folder on the server in question and copy the EXE to the newly created directory. To create the service on the server, open a command prompt and enter the following.

C:\sc create SleepService binPath= "C:\Temp\sleepservice.exe" DisplayName= "SleepService " start= auto
[SC] CreateService SUCCESS

At this point you are done, you should be able to see your newly created Windows service and can start it and stop it at will. When started you should see a process called sleepservice.exe running in the Windows Task Manager.

New Windows Service



Embedding Youtube and Soundcloud Content Nicely with jQuery

09 Oct 2013

The first incarnation of my music page contained a simple list of plain <a href> links to site hosting the content, usually Youtube, Soundcloud or Grooveshark. While this did meet my most basic requirements of maintaining a list of songs I like, having to leave the page to view the content, only to hit my back button to return and select the next song was clunky to me. To solve this I wanted to embed the video content right onto the page, at the same time still leave the ‘list like’ format for conciseness.

Creating the embedable <iframe> for Youtube was fairly straight forward. Grab the url, parse out the ‘v’ parameter, then copy paste Google’s iframe syntax populated with the parameter. Boom, embedable glorious Youtube content.

Soundcloud’s embedable content was a bit more difficult because the URL they use in the <iframe> is not a straight derivative of the URL a person would use to view content on their website. Thankfully Soundcloud has a great API and with a little python magic I could fetch the whole <iframe> source straight from web service call.

Now in possession of embedable content from my favorite providers I needed a way to display this content neatly. I decided to search for a way to create a collapsible <div>. Something that would allow me to still see the artist and song in list form, but additionally allow me to view the content without leaving the page. John Snyder created an awesome jQuery plugin that does exactly that.

This was my first foray with jQuery so I’m not entirely sure how this magic works, perusing the source I could vagely follow what was happening, but I do not have enough knowledge yet of javascript or the DOM to fully understand the plugin. Regardless, with a little effort I managed to get the desired results as demonstrated below (click the plus sign, it’s awesome):

This is a rough example of how I retrieve the Soundcloud <iframe> HTML. You can see how the URL used to view content on the Soundcloud webpage is ‘url’ parameter in the API call. They offer several output formats, I choose JSON since I was already using it to parse the Reddit data coming back from their API

import urllib.request, urllib.error, urllib.parse
import json

def fetch_soundcloud_embed_html():
    url = 'http://soundcloud.com/oembed?format=json&url=https://soundcloud.com/mat_zo/the-mat-zo-mix-004-21-09-13&iframe=true'
    opener = urllib.request.build_opener()
    try:
        url = urllib.request.Request(url)
        r = opener.open(url).read()
        output = r.decode('utf-8')
        html = json.loads(output)["html"]

    return html

    except Exception as e:
        logging.error(e)



Github, Python, Reddit and more

23 Sep 2013

Ever find yourself wondering what the name of that song was you listened to a week ago on YouTube? This happens to me all the time, I browse the /r/electronicmusic subreddit almost everyday to find new music to listen to. I would upvote songs and then find myself a week later attempting to look through my history for songs I liked but it was always a hassle to weed through all my upvoted content just to find songs to listen to.

Knowing Reddit offered an API I thought there was probably a way I could automate reading through my history and listing all the songs I liked saving myself the hours of manual labor (I am very lazy…err, I mean efficient).

I wanted to accomplish three things with this mini project:

  • Write a python script
  • Get myself on GitHub
  • A consistently updated list of good music to listen to

Overview

This python script will call Reddit’s API, parse a users ‘liked’ history for a given subreddit and output a list of HTML links to a file.

Usage

  1. Download the redditlikedlist.py and rll.cfg to a directory of your choice.

  2. Populate the following fields in the rll.cfg file:

  3. Execute the python script

    $> python redditlikedlist.py rll.cfg

The first time the script is run it will start with your most recent liked links and work backwards into the past. Reddit will allow you to parse your most recent 1000 links. After the first run the script stores the most recent link in the ‘beforelinkname’ in the configuration file. Subsequent executions the script will start parsing from this link and work forward to present time

Below I will post the most current version of this script, however please see the GitHub repository for the most up-to-date version.

Configuration File

[PATHS]  
outputfile = (The destination of the output file. Example: /home/John/musiclist.html) 
subreddit = (The subreddit you want to parse from. Example: electronicmusic) 

[WAYPOINT] 
beforelinkname = (leave blank, the script will populate) 

[CREDENTIALS] 
username = (Your Reddit username, must have a valid Reddit account to use this script.) 
password = (Your Reddit password, must have a valid Reddit account to use this script.) 
useragent = (Reddit requires a unique user agent for all calls to its API, 
             it is recommended you incorporate your username in the agent. Example: BobaFett37's Liked List Parse)

Python Script

Python 3+ is required to run this script. I have another mostly working version on GitHub for Python 2.6.6

# This script will login to Reddit, return all liked stories for a given user
# parse all the subreddit likes and build and output for 
# a website listing.
#
import time
import datetime
import urllib.request, urllib.parse, urllib.error
import urllib.request, urllib.error, urllib.parse
import http.cookiejar
import json
import configparser
import logging
import tempfile
import os
import argparse

# Variables
hdr = {}
before_flag = False
link_value = '' 
liked_url =  'https://ssl.reddit.com/user/<username>/liked.json?limit=100&<direction>=<link_name>'
cj = http.cookiejar.CookieJar()
cfg_file = ''
final_file_location = ''
username = ''
password = ''
subreddit = ''
iCounter = 0
tmpfile = tempfile.TemporaryFile()
opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))

# Parse input
def parse_input_params():
    global cfg_file
    parser = argparse.ArgumentParser(description='Music list built from liked stories within a subreddit')
    parser.add_argument("config_file", help="Configuration file location")
    args = parser.parse_args()
    cfg_file = args.config_file

# Load config file
def get_config():
    config = configparser.SafeConfigParser()
    config.optionxform(str())
    try:
        config.read(cfg_file)
        return config
    except Exception as e:
        logging.error(e)

# Retrieve values from config file
def load_config_values(config):
    try:
        global final_file_location
        global username
        global password
        global subreddit
        global hdr
        global before_flag
        global link_value
        final_file_location = config.get('PATHS','outputFile')
        subreddit = config.get('PATHS','subreddit')
        link_value = config.get('WAYPOINT','beforeLinkName')
        if link_value:
            before_flag = True
        username = config.get('CREDENTIALS','username')
        password = config.get('CREDENTIALS','password')
        hdr['User-Agent'] = config.get('CREDENTIALS','useragent')

    except Exception as e:
        logging.error(e)

# Reddit Login Function
def login(username, passwd):
    values = {'user': username,
              'api_type': 'json',
              'passwd': passwd}

    login_url = urllib.request.Request('https://ssl.reddit.com/api/login/', headers=hdr)
    data = urllib.parse.urlencode(values)
    data_bytes = data.encode('utf-8')
    try:
        response = opener.open(login_url, data_bytes).read()
    except Exception as e:
        logging.error(e)        
        
def process_reddit_data():
    global link_value
    global tmpfile
    global iCounter
    try:
        while (link_value is not None):
            time.sleep(3)
            liked_json = retrieve_liked(username)

            if (before_flag == False):
                link_value = json.loads(liked_json)["data"]["after"]
            else:
                link_value = json.loads(liked_json)["data"]["before"]

            liked_json = json.loads(liked_json)["data"]["children"]
            for titles in liked_json:
                iCounter += 1
                if (iCounter == 1):
                    write_config_values(titles["data"]["name"])

                if(titles["data"]["subreddit"]==subreddit and titles["data"]["media"] is not None):
                    tmpfile.write(bytes('<a href=\''+ titles["data"]["url"] + '\'>' + titles["data"]["title"] + '</a><br/>\n', 'utf-8' ))

    except Exception as e:
        logging.error(e)
        
# Fetch liked content for a user
def retrieve_liked(username):
    try:
        if(before_flag == True):
            direction = 'before'
        else:
            direction = 'after'
        repl = {'<username>':username, '<link_name>':link_value, '<direction>':direction}
        url = replace_all(liked_url, repl)
        url =  urllib.request.Request(url, headers=hdr)
        r = opener.open(url).read()
        response = r.decode('utf-8')
        return response
    except Exception as e:
        logging.error(e)

# Write/Update config file
def write_config_values(before_link):
    try:
        configVal.set('WAYPOINT', 'beforeLinkName', before_link)
        f = open(cfg_file, 'w')
        configVal.write(f)
        f.close
    except Exception as e:
        logging.error(e)

def updated_timestamp():
    ts = time.time()
    st = datetime.datetime.fromtimestamp(ts).strftime('%m-%d-%Y %H:%M:%S')
    return("Last updated at: " + st + "<br/><br/>\n")
        
def write_output():
    global tmpfile
    try:
        if os.path.exists(final_file_location):
            #final output file aleady exists, we need to append new data.
            f2 = open(final_file_location, 'r')
            for i in range(1):
                next(f2)
            for line in f2:
                tmpfile.write(bytes(line, 'utf-8'))
            f2.close()

        tmpfile.seek(0)
        f = open(final_file_location, 'wb')
        f.write(bytes(updated_timestamp(), 'utf-8'))
        for line in tmpfile:
            f.write(bytes(line))
        f.close()

        tmpfile.close()
    except Exception as e:
        logging.error(e)

# generic replace text using dict function
def replace_all(text, dic):
    for i, j in dic.items():
        text = text.replace(i, j)
    return text


###########################################################
# Main Processing
###########################################################
parse_input_params()
configVal = get_config()
load_config_values(configVal)

# Call login and retrieve liked content.  Each call must separated by at least 2 seconds.
login(username, password)
process_reddit_data()
write_output()



Parse Excel Documents Quickly with Powershell

06 Sep 2013

Using Powershell to manipulate Microsoft Excel documents can be an extremely handy and helpful tool. Setting out to interface an Excel document into EnterpriseOne I found this MSDN blog post by Sriram Reddy to help with my initial PS script. Sriram’s method is to use a COM interface into Excel to gain access to its content. This method essentially starts up Excel, in the background or foreground, and gives you access almost as if you were using the application directly. The problem I soon discovered was this method proves to be excessively slow, I was seeing performance in the area of .6 seconds per row read and I was only reading around 1000 rows of data. In search of better performance I found a better way to access Excel via Powerhsell using OLEDB that is extemely efficient and easy to use.

Here is a snippet of the sample data I used for this test.
Sample Data

My first script shown below ran very slow.

########################################################################################################
# This is a common function I am using which will release excel objects
########################################################################################################
function Release-Ref ($ref) {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

########################################################################################################
# Creating excel object
########################################################################################################
$objExcel = new-object -comobject excel.application 

# Set to false to not open the app on screen.
$objExcel.Visible = $False

########################################################################################################
# Directory location where we have our excel files
########################################################################################################
$ExcelFilesLocation = "C:/TestExcelData/" + $args[0]

########################################################################################################
# Open our excel file
########################################################################################################
$UserWorkBook = $objExcel.Workbooks.Open($ExcelFilesLocation) 

########################################################################################################
# Here Item(1) refers to sheet 1 of of the workbook. If we want to access sheet 10, 
# we have to modify the code to Item(10)
########################################################################################################
$UserWorksheet = $UserWorkBook.Worksheets.Item(2)

########################################################################################################
# This is counter which will help to iterrate trough the loop. This is simply a row counter
# I am starting row count as 2, because the first row in my case is header. 
# So we dont need to read the header data
########################################################################################################
$intRow = 2

$a = Get-Date
write-host $a
write-host "---------------------------"

Do {

    # Reading the first column of the current row
    $TicketNumber = $UserWorksheet.Cells.Item($intRow, 1).Value()
    write-host $intRow " " $TicketNumber    
    $intRow++

} While ($UserWorksheet.Cells.Item($intRow,1).Value() -ne $null)

$a = Get-Date
write-host $a
write-host "---------------------------"

########################################################################################################
# Exiting the excel object
########################################################################################################
$objExcel.Quit()

########################################################################################################
#Release all the objects used above
########################################################################################################
$a = Release-Ref($UserWorksheet)
$a = Release-Ref($UserWorkBook) 
$a = Release-Ref($objExcel)

Adding the timestamps showed just how slow this method was at accessing Excel data, running right around .6 seconds per row. That translates to my users staring patiently at their screen for 1 minute every 120 rows of excel data they are trying to process. I don’t know about your users, but my mine will definitely not be kind to me with that kind of performance, nor should they be.


PS C:\Powershell\ExcelInterfaceTest> .\ExcelCOMInterface.ps1 test.xlsx
9/6/2013 4:46:01 PM
---------------------------
2   078110
3   078108
4   078107
<SNIP>
244   078347
245   078350
9/6/2013 4:48:33 PM
---------------------------
PS C:\Powershell\ExcelInterfaceTest>

End users were going to be the final destination for this interface; having performance hover in the area of several minutes to process the excel data was not going to pass muster. Asking on StackOverflow was I directed to using an OLEDB to access Excel data from Poweshell and even uses SQL syntax to do so!

Here is the much faster new script using the OLEDB to accomplish the same task above. It should be noted using this method your first row of data must contain your column header information, what columns you ‘SELECT’ from in SQL terms.

########################################################################################################
# Directory location where we have our excel files
########################################################################################################
$ExcelFile = "C:/TestExcelData/" + $args[0]
$Sheetname = "Sheet2$"

########################################################################################################
$OleDbConn = New-Object .System.Data.OleDb.OleDbConnection.
$OleDbCmd = New-Object .System.Data.OleDb.OleDbCommand.
$OleDbAdapter = New-Object .System.Data.OleDb.OleDbDataAdapter.
$DataTable = New-Object .System.Data.DataTable.
$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$ExcelFile`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
$OleDbConn.Open()
$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.commandtext = .Select * from [$Sheetname].
$OleDbAdapter.SelectCommand = $OleDbCmd
########################################################################################################

$RowsReturned = $OleDbAdapter.Fill($DataTable)
$intRow = 1
ForEach ($DataRec in $DataTable) {

    # Reading the first column of the current row
	$TicketNumber = $DataRec."ticket number"
    write-host $intRow " " $TicketNumber  
    $intRow++
}

While an OLEDB is still a COM object it is much faster than using excel.application COM, in addition you can access your data using SQL syntax increasing readability for maintaining the script. I didn’t add timings to judge performance with this script because it was a night and day difference, completion literally within 1-2 seconds; there was no doubt that this was the solution to use.



Dynamically switch Powershell to run in 64-bit mode

03 Sep 2013

Powershell is an amazing tool in the MS Windows world of computing, I explain to the unfamiliar that it’s like BASH for windows. You can accomplish pretty much anything in *nix using BASH scripting and the same is becoming true using Powershell for Windows. Want to query Active Directory for all startup scripts assigned to a user in an OU? Powershell can do it. Want to deploy an application to every PC in your remote office in Tunguska, Siberia? Powershell can do it. Want to verify a service is running on your helpdesk server and restart it not? Powershell can do it.

As with any new tool Powershell does have its own learning curve and quirks, getting a handle of the syntax and process behavior does take some time as you’ll see with this example.

I stumbled upon this Powershell oddity while trying to parse and import a Microsoft Excel document into JD Edwards EnterpriseOne table. To do this I was calling Powershell from a C program using the jdeSystem command (the JDE version of a C library ‘system’ call). I developed the application, the business function and the Powershell script on my development client, tested it and everything went swimmingly, of course all this on the first try too. ;) Next comes a build and deploy for a unit-test in our development environment JAS and application servers. This time when I run the test I get…nothing. No errors, no warnings and no expected data. I was baffled; it still runs perfectly on my dev client but not on the server.

The culprit a combination our 64-bit Microsoft Server 2008 R2 Enterprise Server, 64-bit MS Excel ODBC driver and our 32-bit compiled JDE software. All JD Edwards C functions are compiled as 32-bit programs so when you make a ‘system’ call to launch Powershell it will by default launch the 32-bit Powershell environment regardless of the 64-bit OS… and 32-bit Powershell just won’t play nice with a 64-bit ODBC driver.

After a lot of Google-fu I found a blog post describing a similar situation and derived my fix. You ask Powershell to verify its architecture and relaunch itself recursively using, in my case, the 64-bit environment.

This is another good explanation as to when the $env:PROCESSOR_ARCHITE6432 is set to either x86 or AMD64.

Here is a brief explanation of the script below:

  1. Script launches
  2. Checks the environment variable PROCESSOR_ARCHITEW6432 to determine if we are in 64 or 32 bit land.
  3. If in 32-bit land, relaunch script using 64-bit Powershell and exit. Otherwise complete the script normally.

This really saved me a lot of headache and I hope it helps someone else.

####################################################
# 32-bit-Test.ps1
# Created: 03/20/2013
# Author: me
# Summary: This script will test if Powershell is 
#          running in 32-bit mode and if true
#          launch itself in 64-bit mode for completion
####################################################

########################################################################################################
#If Powershell is running the 32-bit version on a 64-bit machine, we need to force powershell to run in
#64-bit mode to allow the OleDb access to function properly.
########################################################################################################
if ($env:PROCESSOR_ARCHITEW6432 -eq "AMD64") {
	write-warning "Y'arg Matey, we're off to 64-bit land....."
	if ($myInvocation.Line) {
		&"$env:WINDIR\sysnative\windowspowershell\v1.0\powershell.exe" -NonInteractive -NoProfile $myInvocation.Line
	}else{
		&"$env:WINDIR\sysnative\windowspowershell\v1.0\powershell.exe" -NonInteractive -NoProfile -file "$($myInvocation.InvocationName)" $args
	}
exit $lastexitcode
}


write-host "Main script body"

################
# END
################



Verify a service is running with Powershell

24 Aug 2012

We use Spiceworks to manage our helpdesk and IT infrastructure. Frequently we would find that the ‘spiceworks’ service would be stopped for one reason or another on the server, which prevented our users from entering helpdesk tickets (bad!).

At this point in time we did not want to implement something like Nagios to ensure everything is up and running so I wrote a small Powershell script that will check the status of a service and if it’s not running send an email and attempt to restart the service. This gives IT a heads up there may be an issue with the server, and hopefully proactively get’s things running again without any intervention on our part.

We have this called via Windows Scheduler every 15 minutes. For execution, run the script and pass the service name like so: verify_service_status.ps1 [Service]

###################################################################
# verify_service_status.ps1
# Created: 09/14/2012
# Author: B.Antonczak
# Summary: Verify a service is running, send email if it's down
###################################################################
#####################################
# Send Email Function
#####################################
 function sendMail($service, $serverNM){
 
     #SMTP server name
     $smtpServer = "XXX.XXX.XXX.XXX"
	 
     #Creating a Mail object
     $msg = new-object Net.Mail.MailMessage

     #Creating SMTP server object
     $smtp = new-object Net.Mail.SmtpClient($smtpServer)

     #Email structure
     $msg.From = "Server@example.com"
     $msg.ReplyTo = "ImportantPeople@example.com"
     $msg.To.Add("ImportantPeople@example.com"
	 $msg.To.Add("ImportantPeople@example.com"
     $msg.subject = 'Alert ' + $Service + ' is not running on ' + $serverNM
     $msg.body = "The following service is not running on " + $serverNM + " and has attempted to be restarted: " + $Service

     #Sending email
     $smtp.Send($msg)
 
}

##############################
# Get service status
##############################
if($args[0] -ne $NULL){

	$serviceName = $args[0]
	$serverName = hostname

	$status = (Get-Service $serviceName).Status

	if ($status -ne "Running"){
		sendMail "$serviceName" "$serverName"
		Restart-Service $ServiceName
	}else{
			# Service is running, do nothing;
	}
}



Validate Media Object files physically exist in JD Edwards EnterpriseOne

24 Aug 2012

Media Objects are method to store a file attachment to a record within the EnterpriseOne ERP system. The gist of the process is there is a table (F00165) that will store a pointer to a physical file location stored on a server. Occasionally these physical files will be deleted due to bugs within EnterpriseOne (ex: OLE’s being linked to more than 1 transaction, etc…) or by user error where the file is physically deleted from the media object server.

I created this powershell script to go through F00165 media object table and verify that the file still physically exists. If it does not it will write a record to a CSV file to be reviewed later. Please note this is performed on an Oracle database, but can be application to any database with minor modifications.



##################################################################
# Validate_Media_Object_Files.ps1
# Created: 12/20/2010
# Author: Brian Antonczak
# Summary: Fetch all file and OLE media object records from the F00165 table.
# Then verify the file exists in the location specified (should always be media object server.
##################################################################
#------------------------
# Inits
#------------------------
[Reflection.Assembly]::LoadFile("C:\ora10g\odp.net\bin\2.x\Oracle.DataAccess.dll")
$connectionString = "Data Source=XXXXX;User Id=XXXXXXXX;Password=tXXXXXXX;"
$connection = New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
$connection.Open()
$queryString = "SELECT GDOBNM, GDTXKY, GDMOSEQN, GDGTMOTYPE, GDGTFILENM, GDUSER, GDUPMJ, GDTDAY FROM F00165 WHERE GDGTMOTYPE IN (2, 5)"
$command = new-Object Oracle.DataAccess.Client.OracleCommand($queryString, $connection)
$mediaObjRS = $command.ExecuteReader()
$files = @() # Initialize array outside loop ...
$counter = 0
$filedate = get-date -format "M-d-yyyy"
$outputFilename = "MissingMediaAttach" + $filedate + "_PROD.csv"

#---------------------------------------
# Loop through recordset
#---------------------------------------
while ($mediaObjRS.read()) {

   # Assign variables from recordset.
   $object = $mediaObjRS.GetString(0)
   $objectKey = $mediaObjRS.GetString(1)
   $objectSeq = $mediaObjRS.GetDecimal(2)
   $objectType=$mediaObjRS.GetDecimal(3)
   $objectFileName = $mediaObjRS.GetString(4)
   $objectUser = $mediaObjRS.GetString(5)
   $objectDate=$mediaObjRS.GetDecimal(6)
   $objectTime=$mediaObjRS.GetDecimal(7)

        #-------------------------------------------------------
        # Check if file exists based on filetype.
        #-------------------------------------------------------
        if($objectType -eq 2){
                # Type 2 = OLE
                $fileLocation = "\\jdemed02\D$\E812\MEDIAOBJ\Oleque\" + $objectFileName
        }elseif($objectType -eq 5){
                # Type 5 = FILE
                $fileLocation = $objectFileName
        }

        # Skip test if media object points to network share
        if($fileLocation.substring(1,2) -ne ":\"){
                # Skip test if URL
                if(!$fileLocation.tolower().StartsWith("http")){
                        $fileExists = Test-Path $fileLocation

                        if(!$fileExists){
                                #Write to output file
                                $obj = new-object psobject -prop @{
                                           Object = $object
                                           FileName = $objectFileName
                                           Type = $objectType
                                           Seq = $objectSeq
                                           Key = $objectKey
                                           User = $objectUser
                                           Date = $objectDate
                                           Time = $objectTime
                                   }
                                   $files += $obj
                                   $counter = $counter + 1
                                   Write-Host "$objectKey $objectSeq $objectType $objectFileName"
                        }
                }
        }
}

$files | Export-Csv $outputFilename

$connection.Close()

Write-Host "Record Count: " $counter