• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

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
.......................
0
AhmedAliShaik
Asked:
AhmedAliShaik
  • 6
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

 
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
 
QlemoC++ DeveloperCommented:
"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
 
QlemoC++ DeveloperCommented:
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
 
AhmedAliShaikAuthor Commented:
I have given points to Qlemo for the customised script.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now