Advertisement

10.11.2008 at 06:29AM PDT, ID: 23806453 | Points: 250
[x]
Attachment Details

Add output to MS access DB

Asked by PHrozen1 in Perl Programming Language

Tags:

I Have this script that outputs to txt files. I want to have the script add the info to an MS access db.
The DB has tables for disk usage , eventlogs, and services. The fields(colums) are date and one for each server name that is read in from the ini file. It is a normalized db. If the access portion of the script could check to see if the fields(colums) are there if not create it and add the info into the rows of the colum. Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:

#!perl
 
use Getopt::Long;
use Time::Local;
use Win32::OLE qw(in);
use constant vbCrLf => "\r\n";
use constant vbTab => "\x09";
use Win32::Service;
use Win32::Registry;
use POSIX;
use Win32::EventLog;
use Config::Inifiles;
use File::Copy;
 
 
   BEGIN {Win32::SetChildShowWindow(0)
       if defined &Win32::SetChildShowWindow};
 
 
$ENV{PATH}="c:\\windows\\system32";
 
  #general
my $ini = Config::IniFiles->new( -file => 'C:\servers2.ini' );
my @servers = $ini->Sections;
my $today = strftime("%d%b%Y",localtime);
#disk usage
my ($objWMIService, $objItem, $colItems);
my ($strDriveType, $strDiskSize, $txt);
#services
my ($key, %services,  %status);
 
my @logs = qw(application system);
my $EventLog;
 
my $SEC = 1;
my $MIN = 60 * $SEC;
my $HOUR = 60 * $MIN;
my $DAY = 24 * $HOUR;
 
 
 %state = (
            0 => 'unknown',
            1 => 'stopped',
            2 => 'starting',
            3 => 'stopping',
            4 => 'running',
            5 => 'resuming',
            6 => 'pausing',
            7 => 'paused',
            8 => 'undefined',  # used only by Show_Service.pl
            );
 %startup = (
            0 => 'unknown',
            1 => 'kernel driver',
            2 => 'automatic startup',
            3 => 'manual startup',
            4 => 'startup disabled',
            5 => 'no registry key',   # used only by Show_Service.pl
            );
 %type = (
            0   => 'unknown',
            16  => 'unknown',
            32  => 'unknown',
            272 => 'unknown',
            288 => 'unknown',
            );
 
%EVENT_TYPE = (
    eval EVENTLOG_AUDIT_FAILURE     =>  'AUDIT_FAILURE',
    eval EVENTLOG_AUDIT_SUCCESS     =>  'AUDIT_SUCCESS',
    eval EVENTLOG_ERROR_TYPE        =>  'ERROR',
    eval EVENTLOG_WARNING_TYPE      =>  'WARNING',
    eval EVENTLOG_INFORMATION_TYPE  =>  'INFORMATION',
);
 
 
 Configure( \%Config );
if( $Config{hour} || $Config{day} )
{
    $TIME_LIMIT = time() - ( $DAY * $Config{day} ) - ( $HOUR * $Config{hour} );
}
 
 
if( defined( $Config{type} ) )
{
    foreach my $Mask ( @{$Config{type}} )
    {
        # Try referencing the EVENTLOG_xxxx_TYPE and EVENTLOG_xxxxx
        # constants. One of them is bound to work.
        $EVENT_MASK |= eval( "EVENTLOG_" . uc( $Mask ) . "_TYPE" );
        #$EVENT_MASK |= eval( "EVENTLOG_" . uc( $Mask ) );
    }
}
else
{
    map
    {
        $EVENT_MASK |= 0 + $_;
    }( keys( %EVENT_TYPE ) );
}
 
 
 
# Tell the extension to always attempt to fetch the
# event log message table text
$Win32::EventLog::GetMessageText = 1;
$~ = EventLogFormat;
 
 
# Main control loop start
foreach my $server (@servers) {
 
# Get the server name and store in variable $name
my $name = $ini->val($server, 'name');
# Store the name of file saving to which is the server name with current date in the $file variable
my $file = $name . "_"  . $today;
 
# Disk Usage start variables
    $objWMIService = Win32::OLE->GetObject('winmgmts:\\\\' . $name . '\\root\\cimv2');
    $colItems = $objWMIService->ExecQuery('Select * from Win32_LogicalDisk WHERE DriveType=3');
    $txt = 'Drive' . vbTab . 'Size' . vbTab . 'Used' . vbTab . 'Free' . vbTab . 'Free(%)' . vbCrLf;
 
 
# Disk usage loop
    foreach $objItem (in $colItems) {
 
        my ($pctFreeSpace, $strFreeSpace, $strusedSpace);
 
        $pctFreeSpace = int(($objItem->FreeSpace / $objItem->Size) * 1000) / 10;
        $strDiskSize = int($objItem->Size / 1073741824) . 'Gb';
        $strFreeSpace = int($objItem->FreeSpace / 1073741824) . 'Gb';
        $strusedSpace = int(($objItem->Size - $objItem->FreeSpace) / 1073741824) . 'Gb';
	$txt = $txt . $objItem->Name . vbTab . $strDiskSize . vbTab . $strusedSpace . vbTab . $strFreeSpace . vbTab . $pctFreeSpace . vbCrLf;
 
 
    }
 
# Print disk usage to file
         &printme("$txt \n \n \n", $file);
 
# Add header in file to show
	 &printme("Services for $name \n \n \n \n", $file) ;
# Connect to service manager and enumerate services (long name, short name)
Win32::Service::GetServices('\\\\'.$name,\%services);
 
# Service enumeration loop start
foreach $key (sort keys %services) {
 
# Connect to service manager and get service status
   Win32::Service::GetStatus( '',$services{$key}, \%status);
   if (!defined($status{CurrentState})) {
      $status{CurrentState} = 8;
   }
   if (!defined($status{ServiceType})) {
      $status{ServiceType} = 0;
   }
 
 
 
# Connect to registry and get service startup mode
   $HKEY_LOCAL_MACHINE->Win32::Registry::Connect($name,$Remote_Registry_HKLM);
   if (!$Remote_Registry_HKLM) {
      die "Unable to Connect to HKLM on $server\n";
   }
   $Remote_Registry_HKLM->Open('System\\CurrentControlSet\\Services\\'.$services{$key},$Reg_Service);
   if (!$Reg_Service) {
# Die "Unable to Connect to HKLM...Services\\$services{$key} on $server\n";
     $value = 5;
   }
   else {
      $Reg_Service->QueryValueEx('Start',$type, $value);
      $Reg_Service->Close;
   }
   $Remote_Registry_HKLM->Close;
 
# Match and print only Running Services
   if($state{$status{CurrentState}}=~ m/running/) {
 
      my $servicelist =  "   $key ($services{$key}), $state{$status{CurrentState}}, $startup{$value}\n \n";
   &printme( $servicelist, $file);
 
       } # Print end brace for services
 
    } # Services enumeration end brace
 
 
 &printme("\n \n \n Eventlogs for $name \n \n \n \n", $file) ;
 
   foreach my $log (@logs) {
 
    if( $EventLog = Win32::EventLog->new( $log , $name ) )
    {
 
        local %Event;
        local $Count = 0;
 
        while( ( $EventLog->Read( EVENTLOG_BACKWARDS_READ
                                 | EVENTLOG_SEQUENTIAL_READ,
                                 0,
                                 \%Event ) )
                && ( $Event{TimeGenerated} > $TIME_LIMIT ) )
        {
            # Display the event if it is one of our requested
            # event types
            $Count++;
 
                 open STDOUT, ">>c:\\$file.txt" or die "Can't redirect STDOUT";
 
               write  if( $Event{EventType} & $EVENT_MASK );
                                close STDOUT;
 
        }
    }
    else
    {
        print "Can not connect to the $log Event Log on $name.\n";
    }
 
 
   }
 
         } # Main loop end brace
 
   sub Configure
{
    my( $Config ) = @_;
    $Config->{hour} = 24;
    $Config->{day} = 0;
    $Config->{type} = ['ERROR'];
 
    }
 
 sub printme {
 
      my ($subarg) = $_[0];
      my ($subarg2) = $_[1];
 
 open(OUTFILE, ">> c:\\$subarg2.txt");
     print OUTFILE "$subarg";
        close(OUTFILE)
      }
 
 
 
format EventLogFormat =
--------------------------------
@>>>>> @<<<<<<<<<<<<<<<<<<<<<<<<<<<<  ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$Event{RecordNumber}, "\\\\" . $Event{Computer},     $Event{Message}
       @<<<<<<<<<<<<<<<<<<<<<<<<<<<<  ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
scalar localtime( $Event{TimeGenerated} ), $Event{Message}
       Type: @<<<<<<<<<<<<<<<<<<<<<<  ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$EVENT_TYPE{$Event{EventType}}, $Event{Message}
       Source: @<<<<<<<<<<<<<<<<<<<<  ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$Event{Source},                       $Event{Message}
~                                     ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                      $Event{Message}
~                                     ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                      $Event{Message}
~                                     ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                      $Event{Message}
~                                     ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                      $Event{Message}
~                                     ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                      $Event{Message}
 
.
 
Keywords: Add output to MS access DB
Title
1 c# Eventlog WriteEntry
2 Eventlog
3 2 colums compare
4 Microsoft, Windows Server 2003…
5 vb.net read ini file
6 Online Poll will not send info to .txt file.
 
Loading Advertisement...
 
[+][-]10.11.2008 at 06:30AM PDT, ID: 22693852

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.11.2008 at 11:17AM PDT, ID: 22694866

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.12.2008 at 12:12AM PDT, ID: 22696602

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.12.2008 at 07:58PM PDT, ID: 22699995

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-42 / EE_QW_2_20070628