Integrating two powershell commands

I have the below shell commands which give the the time of database mount & dismount.

get-eventlog -LogName Application -Newest 20000 | Where-Object { $_.EventID -like "103" } | ft timewritten, eventid

get-eventlog -LogName Application -Newest 20000 | Where-Object { $_.EventID -like "102" } | ft timewritten, eventid,

event 103 lists all dismounted SG list and 102 list all mounted DB list with date & time.

I need the output in the below format:

Databasename, dismount time, mount time, difference
--------------------------------------------------------------------------------------------
DB1                    10:30               10:45             15 min
DB2                    10:40                10:50             10 mins
.......................
LVL 2
AhmedAliShaikAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Your events are coming in the "wrong" order (first 102, then 103). I'll have to check for that, and ignore the first mount without prior dismount:
cls
Get-EventLog Application -Newest 20KB | 
  ? {$_.EventID -eq 103 -or $_.EventID -eq 102} | 
  select Source, TimeWritten, EventID | 
  sort Source, TimeWritten | 
  % { $event = $_
    switch ($event.EventID) {
    103 { $entry = New-Object PSObject @{
            DB       = $event.Source
            Dismount = $event.TimeWritten
            Mount   = $null
            Diff    = 0
          }
        }
    102 { if ($event.Source -ne $entry.DB) {
            $entry
            $entry.DB       = $event.Source
            $entry.Dismount = $null
            $entry.Diff     = 0
          }
          if ($entry) {
            $entry.Mount = $event.TimeWritten
            if ($entry.Dismount -ne $null) {$entry.Diff = $entry.Mount - $entry.Dismount} 
            $entry
          }
        }
    }
  } | select @{l="DB"; e={$_.DB}},
              @{l="Dismount"; e={$_.Dismount}},
              @{l="Mount"; e={$_.Mount}},
              @{l="Diff"; e={$_.Diff}}

Open in new window

The code should have worked (if you ignore those error messages).
0
 
AkulshCommented:
You have habit of abandoning the questions (leaving them open, without any feedback from your side), so I am a bit reluctant to spend time on this new question of yours.
0
 
Manpreet SIngh KhatraSolutions Architect, Project LeadCommented:
get-eventlog -LogName Application -Newest 20000 | Where-Object { $_.EventID -like "102","103" } | ft

First get the Output and then check which attributes you want.

http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/df254730-b21d-4355-9d06-7ed375bb1bd9
http://www.sapien.com/forums/scriptinganswers/forum_posts.asp?TID=3653

- Rancy
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
AhmedAliShaikAuthor Commented:
Akulsh,

You are the first person who is telling this. May be one or two questions i have not spent time and not responded. But i am getting many answers from experts on a regular basis.

I am not sure for which question you have posted and havent got feedback.
0
 
AhmedAliShaikAuthor Commented:
Dear Rancy,

The technet article is greek and latin for me. I tried hard to understand it but unable.

If we are giving ($_.EventID -like "102","103") we will output get one by one. But i want the parallel output for same database when it got dismounted as well as mounted with the difference.

:)
0
 
Manpreet SIngh KhatraSolutions Architect, Project LeadCommented:
Hey Ahmed ..... we meet again :)
YOu really bring some stuff to work with shell or others :)

- Rancy
0
 
Manpreet SIngh KhatraSolutions Architect, Project LeadCommented:
Once i have my Lab will give it a shot and check with it and get back to you :)

- Rancy
0
 
AhmedAliShaikAuthor Commented:
I am really blessed to have you as an expert.

Thanks to virtually meet you.

Regards,
Ahmed.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
"Joining" result sets is not straight-forward in PowerShell. In addition there might have been several dismount/mount cycles, making the DB ID no unique key. We will hence need a "full-sort merge" join, that is having to manually match successive entries.
cls
Get-EventLog Application -Newest 20KB | 
  ? {$_.EventID -eq 103 -or $_.EventID -eq 102} | 
  select Source, TimeWritten, EventID | 
  sort Source, TimeWritten | 
  % { $event = $_
    switch ($event.EventID) {
    103 { $entry = New-Object PSObject @{
            DB       = $event.Source
            Dismount = $event.TimeWritten
            Mount   = $null
            Diff    = 0
          }
        }
    102 { if ($event.Source -ne $entry.DB) {
            $entry
            $entry.DB       = $event.Source
            $entry.Dismount = $null
            $entry.Diff     = 0
          }
          $entry.Mount = $event.TimeWritten
          if ($entry.Dismount -ne $null) {$entry.Diff = $entry.Mount - $entry.Dismount} 
          $entry
        }
    }
  } | select @{l="DB"; e={$_.DB}},
              @{l="Dismount"; e={$_.Dismount}},
              @{l="Mount"; e={$_.Mount}},
              @{l="Diff"; e={$_.Diff}}

Open in new window

0
 
AhmedAliShaikAuthor Commented:
Hi Qlemo,

Appreciate your work in providing me the script. I will run this is production and post you the results.
0
 
AhmedAliShaikAuthor Commented:
Hi qlemo,

Getting the below errors

Property 'Mount' cannot be found on this object; make sure it exists and is set
table.
At C:\TEMP\sla.ps1:20 char:18
+           $entry. <<<< Mount = $event.TimeWritten
    + CategoryInfo          : InvalidOperation: (Mount:String) [], RuntimeExce
   ption
    + FullyQualifiedErrorId : PropertyNotFound

Property 'DB' cannot be found on this object; make sure it exists and is settab
le.
At C:\TEMP\sla.ps1:16 char:20
+             $entry. <<<< DB       = $event.Source
    + CategoryInfo          : InvalidOperation: (DB:String) [], RuntimeExcepti
   on
    + FullyQualifiedErrorId : PropertyNotFound

Property 'Dismount' cannot be found on this object; make sure it exists and is
settable.
At C:\TEMP\sla.ps1:17 char:20
+             $entry. <<<< Dismount = $null
    + CategoryInfo          : InvalidOperation: (Dismount:String) [], RuntimeE
   xception
    + FullyQualifiedErrorId : PropertyNotFound

Property 'Diff' cannot be found on this object; make sure it exists and is sett
able.
At C:\TEMP\sla.ps1:18 char:20
+             $entry. <<<< Diff     = 0
    + CategoryInfo          : InvalidOperation: (Diff:String) [], RuntimeExcep
   tion
    + FullyQualifiedErrorId : PropertyNotFound

Property 'Mount' cannot be found on this object; make sure it exists and is set
table.
At C:\TEMP\sla.ps1:20 char:18
+           $entry. <<<< Mount = $event.TimeWritten
    + CategoryInfo          : InvalidOperation: (Mount:String) [], RuntimeExce
   ption
    + FullyQualifiedErrorId : PropertyNotFound

Open in new window

0
 
AhmedAliShaikAuthor Commented:
I have given points to Qlemo for the customised script.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.