Windows Vista Forums

Re: Powershell openfiles.exe output

  1. 08 Sep 2009 #1


    Shay Levy [MVP] Guest

    Re: Powershell openfiles.exe output


    PS > openfiles /query /s \\FILE1A /fo CSV /v > openfiles.csv
    PS > import-csv openfiles.csv | convertto-html | openfiles.html
    PS > invoke-item openfiles.html



    ---
    Shay Levy
    Windows PowerShell MVP
    http://blogs.microsoft.co.il/blogs/ScriptFanatic
    PowerShell Toolbar: http://tinyurl.com/PSToolbar





    M> NeverMind..... Googling found me an answer / work around.
    M>
    M> Running the commands in the following order preserves the formatting,
    M> but loses the html, body and table tags so an additional bit of code
    M> added on the bottom fixes the missing tags.
    M>
    M> The following script should show who has what files open on server
    M> FILE1A,sorted by directory name filtered for a certain string (CAD in
    M> this case)...
    M>
    M> $in = openfiles /query /s \\FILE1A /fo CSV /v /nh
    M> $out=@()
    M> foreach($line in $in){
    M> $obj=new-object psobject
    M> $obj|add-member noteproperty User $line.split(",")[2]
    M> $obj|add-member noteproperty Type $line.split(",")[5]
    M> $obj|add-member noteproperty File $line.split(",")[6]
    M> $out+=$obj
    M> }
    M> $out | sort-object File
    M> $html = $out | sort-object FILE | convertto-html
    M> $html | select-string 'CAD' > C:\openfiles.html
    M> ${C:\openfiles.html} = @"
    M> <html>
    M> <body>
    M> <table>
    M> ${C:\openfiles.html}
    M> </table>
    M> </body>
    M> </html>
    M> "@
    M> Messy I am sure but gets the job done!
    M>
    M> Thanks for all of your help!
    M>
    M> "Morphius" wrote:
    M>

    >> Ok, so i get that $out | sort-object FILE will sort the results, and
    >> a filter should be something like select-string 'CAD' would only
    >> select the entries according to those with CAD in the file name
    >> however if i try to run the following it breaks the code...
    >>
    >> $in = openfiles /query /s \\file1a /fo CSV /v /nh | select-string
    >> 'CAD' $out=@()
    >>
    >> foreach($line in $in){
    >> $obj=new-object psobject
    >> $obj|add-member noteproperty User $line.split(",")[2]
    >> $obj|add-member noteproperty Type $line.split(",")[5]
    >> $obj|add-member noteproperty File $line.split(",")[6]
    >> $out+=$obj
    >> }
    >> $out | sort-object File
    >> It just generates the following:
    >>
    >> + $obj|add-member noteproperty File $line.split( <<<< ",")[6]
    >> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >> At line:3 char:48
    >> + $obj|add-member noteproperty User $line.split( <<<< ",")[2]
    >> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >> At line:4 char:48
    >> + $obj|add-member noteproperty Type $line.split( <<<< ",")[5]
    >> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >> At line:5 char:48
    >> + $obj|add-member noteproperty File $line.split( <<<< ",")[6]
    >> I am sure this is possible but I cant get the order of commands right
    >> and I am a beginner when it comes to PS.
    >>
    >> "Morphius" wrote:
    >>

    >>> Hello
    >>>
    >>> Worked out the code to split it differently after all.
    >>>
    >>> Next question, is it possible to sort the output are is that really
    >>> pushing my luck with powershell?
    >>>
    >>> Thanks again for all your help!
    >>>
    >>> "Morphius" wrote:
    >>>
    >>>> Hi Marco.
    >>>>
    >>>> That is great and is getting me nearer but the output is still a
    >>>> bit broken. Could that code be modified to seperate out the columns
    >>>> based on a comma as that command can output in a csv type format.
    >>>>
    >>>> Thanks
    >>>>
    >>>> "Marco Shaw [MVP]" wrote:
    >>>>
    >>>>> Needs some tweaking below. You'd pass your command-line to $in.
    >>>>>
    >>>>> Instead of splitting on spaces, perhaps using positional
    >>>>> characters would be best.
    >>>>>
    >>>>> Is there a blank space before the output starts?
    >>>>>
    >>>>> -------------------
    >>>>> $in=your_command
    >>>>> $out=@()
    >>>>>
    >>>>> foreach($line in $in){
    >>>>> $obj=new-object psobject
    >>>>> $obj|add-member noteproperty ID $line.split("")[0]
    >>>>> $obj|add-member noteproperty User $line.split("")[2]
    >>>>> $obj|add-member noteproperty Type $line.split("")[15]
    >>>>> $obj|add-member noteproperty File $line.split("")[19]
    >>>>> $out+=$obj
    >>>>> }
    >>>>> $out
    >>>>> -------------------
    >>>>> From there, $out will be in a format that you can pass to
    >>>>> export-csv or convertto-html.
    >>>>>
    >>>>> "Morphius" <Morphius@xxxxxx> wrote in message
    >>>>> news:68150345-A937-4258-A314-6FAC33DFBBB9@xxxxxx
    >>>>>
    >>>>>> Hi Marco
    >>>>>>
    >>>>>> The output I get when I run the command looks like this...
    >>>>>>
    >>>>>> ID Accessed By Type Open File
    >>>>>> (Path\executable)
    >>>>>> ======== ==================== ==========
    >>>>>> =====================================
    >>>>>> 3626682 MSPENCER Windows D:\Data\DFSData\Malaysia
    >>>>>> Shared
    >>>>>> 3628212 PTIBBS Windows D:\..\609 PSR -
    >>>>>> Bechtel.XLS
    >>>>>> 3628566 MSPENCER Windows D:\..\H01-4101 &
    >>>>>> H02-4101
    >>>>>> 3628607 MSPENCER Windows D:\..607 ETD003D
    >>>>>> H01-2601
    >>>>>> 250809.xls
    >>>>>> 3630086 HWURTZ Windows D:\Data\DFSData\Malaysia
    >>>>>> Shared
    >>>>>> 3631001 HWURTZ Windows D:\..\From
    >>>>>> Client\07.09.09
    >>>>>> 3631766 PHALSTEAD Windows D:\..\Contract Invoicing
    >>>>>> Control.xls
    >>>>>> 3632791 ASNELLING Windows
    >>>>>> D:\..\GA\H01-4101-STACK.SLDPRT
    >>>>>> However when I try to pipe it into html or csv the output comes
    >>>>>> out like this..
    >>>>>>
    >>>>>> PS C:\Users\dsmith> openfiles /query /s \\file1a | convertto-html
    >>>>>> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    >>>>>> "http://www.w3.org/TR/html4/strict.dtd">
    >>>>>> <html>
    >>>>>> <head>
    >>>>>> <title>HTML TABLE</title>
    >>>>>> </head><body>
    >>>>>> <table>
    >>>>>> <colgroup>
    >>>>>> <col>
    >>>>>> </colgroup>
    >>>>>> <tr><th>*</th></tr>
    >>>>>> <tr><td>0</td></tr>
    >>>>>> <tr><td>78</td></tr>
    >>>>>> <tr><td>78</td></tr>
    >>>>>> <tr><td>72</td></tr>
    >>>>>> <tr><td>68</td></tr>
    >>>>>> <tr><td>66</td></tr>
    >>>>>> <tr><td>77</td></tr>
    >>>>>> <tr><td>72</td></tr>
    >>>>>> <tr><td>67</td></tr>
    >>>>>> <tr><td>69</td></tr>
    >>>>>> <tr><td>68</td></tr>
    >>>>>> <tr><td>72</td></tr>
    >>>>>> <tr><td>77</td></tr>
    >>>>>> <tr><td>77</td></tr>
    >>>>>> <tr><td>72</td></tr>
    >>>>>> <tr><td>77</td></tr>
    >>>>>> <tr><td>69</td></tr>
    >>>>>> <tr><td>72</td></tr>
    >>>>>> (truncated as it goes on forever in the same fashion)
    >>>>>> What we really want is the html to work so we can list who has
    >>>>>> what files open on a specific server and paste it on the intranet
    >>>>>> for people to see....
    >>>>>>
    >>>>>> if there is an alternative code that would show this, that would
    >>>>>> also be great!
    >>>>>>
    >>>>>> Thanks
    >>>>>>
    >>>>>> "Marco Shaw [MVP]" wrote:
    >>>>>>
    >>>>>>> When you run a DOS-based command-line like this, what is
    >>>>>>> outputted isn't easily processed by PowerShell.
    >>>>>>>
    >>>>>>> I don't have any shares I can use to provide a demo. Are you
    >>>>>>> able to
    >>>>>>> provide some sample output from the command that you want to
    >>>>>>> wrap into a
    >>>>>>> CSV
    >>>>>>> or HTML?
    >>>>>>> Marco
    >>>>>>>
    >>>>>>> "Morphius" <Morphius@xxxxxx> wrote in message
    >>>>>>> news:492FB882-EB12-4E25-8DCA-2B03CBA84047@xxxxxx
    >>>>>>>
    >>>>>>>> Hello
    >>>>>>>>
    >>>>>>>> Excuse what may be a stupid question but I am trying to output
    >>>>>>>> the
    >>>>>>>> result
    >>>>>>>> from running openfiles.exe to either a correctly formatted .csv
    >>>>>>>> or a
    >>>>>>>> .html
    >>>>>>>> file.
    >>>>>>>> I can output the result to text without a problem but i need to
    >>>>>>>> show it
    >>>>>>>> in
    >>>>>>>> a
    >>>>>>>> web page.
    >>>>>>>> I have tried the export-csv and the convertto-html commands
    >>>>>>>> without any
    >>>>>>>> success as it just exports a seemingly random string of numbers
    >>>>>>>> rather
    >>>>>>>> than
    >>>>>>>> the list of which files are open.
    >>>>>>>> I have included code snippits below if anyone has any
    >>>>>>>> suggestions?
    >>>>>>>>
    >>>>>>>> openfiles /query /s \\fileserver <--- This
    >>>>>>>> shows the
    >>>>>>>> data
    >>>>>>>> openfiles /query /s \\fileserver | convertto-html <---
    >>>>>>>> This
    >>>>>>>> shows
    >>>>>>>> the
    >>>>>>>> html but for meaningless data
    >>>>>>>> openfiles /query /s \\fileserver | export-csv c:\test.csv
    >>>>>>>> <---
    >>>>>>>> This
    >>>>>>>> exports a csv but with meaningless data
    >>>>>>>> Thanks for any help!
    >>>>>>>>


      My System SpecsSystem Spec
  2. 09 Sep 2009 #2


    Marco Shaw [MVP] Guest

    Re: Powershell openfiles.exe output

    Thanks Shay...

    Yes, much easier. I should have stopped to think about this earlier...

    Marco

    "Shay Levy [MVP]" <no@xxxxxx> wrote in message
    news:37162f6da03428cbfe89a6f49b61@xxxxxx

    >
    > PS > openfiles /query /s \\FILE1A /fo CSV /v > openfiles.csv
    > PS > import-csv openfiles.csv | convertto-html | openfiles.html
    > PS > invoke-item openfiles.html
    >
    >
    >
    > ---
    > Shay Levy
    > Windows PowerShell MVP
    > http://blogs.microsoft.co.il/blogs/ScriptFanatic
    > PowerShell Toolbar: http://tinyurl.com/PSToolbar
    >
    >
    >
    > M> NeverMind..... Googling found me an answer / work around.
    > M> M> Running the commands in the following order preserves the
    > formatting,
    > M> but loses the html, body and table tags so an additional bit of code
    > M> added on the bottom fixes the missing tags.
    > M> M> The following script should show who has what files open on server
    > M> FILE1A,sorted by directory name filtered for a certain string (CAD in
    > M> this case)...
    > M> M> $in = openfiles /query /s \\FILE1A /fo CSV /v /nh
    > M> $out=@()
    > M> foreach($line in $in){
    > M> $obj=new-object psobject
    > M> $obj|add-member noteproperty User $line.split(",")[2]
    > M> $obj|add-member noteproperty Type $line.split(",")[5]
    > M> $obj|add-member noteproperty File $line.split(",")[6]
    > M> $out+=$obj
    > M> }
    > M> $out | sort-object File
    > M> $html = $out | sort-object FILE | convertto-html
    > M> $html | select-string 'CAD' > C:\openfiles.html
    > M> ${C:\openfiles.html} = @"
    > M> <html>
    > M> <body>
    > M> <table>
    > M> ${C:\openfiles.html}
    > M> </table>
    > M> </body>
    > M> </html>
    > M> "@
    > M> Messy I am sure but gets the job done!
    > M> M> Thanks for all of your help!
    > M> M> "Morphius" wrote:
    > M>

    >>> Ok, so i get that $out | sort-object FILE will sort the results, and
    >>> a filter should be something like select-string 'CAD' would only
    >>> select the entries according to those with CAD in the file name
    >>> however if i try to run the following it breaks the code...
    >>>
    >>> $in = openfiles /query /s \\file1a /fo CSV /v /nh | select-string
    >>> 'CAD' $out=@()
    >>>
    >>> foreach($line in $in){
    >>> $obj=new-object psobject
    >>> $obj|add-member noteproperty User $line.split(",")[2]
    >>> $obj|add-member noteproperty Type $line.split(",")[5]
    >>> $obj|add-member noteproperty File $line.split(",")[6]
    >>> $out+=$obj
    >>> }
    >>> $out | sort-object File
    >>> It just generates the following:
    >>>
    >>> + $obj|add-member noteproperty File $line.split( <<<< ",")[6]
    >>> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >>> At line:3 char:48
    >>> + $obj|add-member noteproperty User $line.split( <<<< ",")[2]
    >>> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >>> At line:4 char:48
    >>> + $obj|add-member noteproperty Type $line.split( <<<< ",")[5]
    >>> Method invocation failed because [Microsoft.PowerShell.Commands.M
    >>> At line:5 char:48
    >>> + $obj|add-member noteproperty File $line.split( <<<< ",")[6]
    >>> I am sure this is possible but I cant get the order of commands right
    >>> and I am a beginner when it comes to PS.
    >>>
    >>> "Morphius" wrote:
    >>>
    >>>> Hello
    >>>>
    >>>> Worked out the code to split it differently after all.
    >>>>
    >>>> Next question, is it possible to sort the output are is that really
    >>>> pushing my luck with powershell?
    >>>>
    >>>> Thanks again for all your help!
    >>>>
    >>>> "Morphius" wrote:
    >>>>
    >>>>> Hi Marco.
    >>>>>
    >>>>> That is great and is getting me nearer but the output is still a
    >>>>> bit broken. Could that code be modified to seperate out the columns
    >>>>> based on a comma as that command can output in a csv type format.
    >>>>>
    >>>>> Thanks
    >>>>>
    >>>>> "Marco Shaw [MVP]" wrote:
    >>>>>
    >>>>>> Needs some tweaking below. You'd pass your command-line to $in.
    >>>>>>
    >>>>>> Instead of splitting on spaces, perhaps using positional
    >>>>>> characters would be best.
    >>>>>>
    >>>>>> Is there a blank space before the output starts?
    >>>>>>
    >>>>>> -------------------
    >>>>>> $in=your_command
    >>>>>> $out=@()
    >>>>>>
    >>>>>> foreach($line in $in){
    >>>>>> $obj=new-object psobject
    >>>>>> $obj|add-member noteproperty ID $line.split("")[0]
    >>>>>> $obj|add-member noteproperty User $line.split("")[2]
    >>>>>> $obj|add-member noteproperty Type $line.split("")[15]
    >>>>>> $obj|add-member noteproperty File $line.split("")[19]
    >>>>>> $out+=$obj
    >>>>>> }
    >>>>>> $out
    >>>>>> -------------------
    >>>>>> From there, $out will be in a format that you can pass to
    >>>>>> export-csv or convertto-html.
    >>>>>>
    >>>>>> "Morphius" <Morphius@xxxxxx> wrote in message
    >>>>>> news:68150345-A937-4258-A314-6FAC33DFBBB9@xxxxxx
    >>>>>>
    >>>>>>> Hi Marco
    >>>>>>>
    >>>>>>> The output I get when I run the command looks like this...
    >>>>>>>
    >>>>>>> ID Accessed By Type Open File
    >>>>>>> (Path\executable)
    >>>>>>> ======== ==================== ==========
    >>>>>>> =====================================
    >>>>>>> 3626682 MSPENCER Windows D:\Data\DFSData\Malaysia
    >>>>>>> Shared
    >>>>>>> 3628212 PTIBBS Windows D:\..\609 PSR -
    >>>>>>> Bechtel.XLS
    >>>>>>> 3628566 MSPENCER Windows D:\..\H01-4101 &
    >>>>>>> H02-4101
    >>>>>>> 3628607 MSPENCER Windows D:\..607 ETD003D
    >>>>>>> H01-2601
    >>>>>>> 250809.xls
    >>>>>>> 3630086 HWURTZ Windows D:\Data\DFSData\Malaysia
    >>>>>>> Shared
    >>>>>>> 3631001 HWURTZ Windows D:\..\From
    >>>>>>> Client\07.09.09
    >>>>>>> 3631766 PHALSTEAD Windows D:\..\Contract Invoicing
    >>>>>>> Control.xls
    >>>>>>> 3632791 ASNELLING Windows
    >>>>>>> D:\..\GA\H01-4101-STACK.SLDPRT
    >>>>>>> However when I try to pipe it into html or csv the output comes
    >>>>>>> out like this..
    >>>>>>>
    >>>>>>> PS C:\Users\dsmith> openfiles /query /s \\file1a | convertto-html
    >>>>>>> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    >>>>>>> "http://www.w3.org/TR/html4/strict.dtd">
    >>>>>>> <html>
    >>>>>>> <head>
    >>>>>>> <title>HTML TABLE</title>
    >>>>>>> </head><body>
    >>>>>>> <table>
    >>>>>>> <colgroup>
    >>>>>>> <col>
    >>>>>>> </colgroup>
    >>>>>>> <tr><th>*</th></tr>
    >>>>>>> <tr><td>0</td></tr>
    >>>>>>> <tr><td>78</td></tr>
    >>>>>>> <tr><td>78</td></tr>
    >>>>>>> <tr><td>72</td></tr>
    >>>>>>> <tr><td>68</td></tr>
    >>>>>>> <tr><td>66</td></tr>
    >>>>>>> <tr><td>77</td></tr>
    >>>>>>> <tr><td>72</td></tr>
    >>>>>>> <tr><td>67</td></tr>
    >>>>>>> <tr><td>69</td></tr>
    >>>>>>> <tr><td>68</td></tr>
    >>>>>>> <tr><td>72</td></tr>
    >>>>>>> <tr><td>77</td></tr>
    >>>>>>> <tr><td>77</td></tr>
    >>>>>>> <tr><td>72</td></tr>
    >>>>>>> <tr><td>77</td></tr>
    >>>>>>> <tr><td>69</td></tr>
    >>>>>>> <tr><td>72</td></tr>
    >>>>>>> (truncated as it goes on forever in the same fashion)
    >>>>>>> What we really want is the html to work so we can list who has
    >>>>>>> what files open on a specific server and paste it on the intranet
    >>>>>>> for people to see....
    >>>>>>>
    >>>>>>> if there is an alternative code that would show this, that would
    >>>>>>> also be great!
    >>>>>>>
    >>>>>>> Thanks
    >>>>>>>
    >>>>>>> "Marco Shaw [MVP]" wrote:
    >>>>>>>
    >>>>>>>> When you run a DOS-based command-line like this, what is
    >>>>>>>> outputted isn't easily processed by PowerShell.
    >>>>>>>>
    >>>>>>>> I don't have any shares I can use to provide a demo. Are you
    >>>>>>>> able to
    >>>>>>>> provide some sample output from the command that you want to
    >>>>>>>> wrap into a
    >>>>>>>> CSV
    >>>>>>>> or HTML?
    >>>>>>>> Marco
    >>>>>>>>
    >>>>>>>> "Morphius" <Morphius@xxxxxx> wrote in message
    >>>>>>>> news:492FB882-EB12-4E25-8DCA-2B03CBA84047@xxxxxx
    >>>>>>>>
    >>>>>>>>> Hello
    >>>>>>>>>
    >>>>>>>>> Excuse what may be a stupid question but I am trying to output
    >>>>>>>>> the
    >>>>>>>>> result
    >>>>>>>>> from running openfiles.exe to either a correctly formatted .csv
    >>>>>>>>> or a
    >>>>>>>>> .html
    >>>>>>>>> file.
    >>>>>>>>> I can output the result to text without a problem but i need to
    >>>>>>>>> show it
    >>>>>>>>> in
    >>>>>>>>> a
    >>>>>>>>> web page.
    >>>>>>>>> I have tried the export-csv and the convertto-html commands
    >>>>>>>>> without any
    >>>>>>>>> success as it just exports a seemingly random string of numbers
    >>>>>>>>> rather
    >>>>>>>>> than
    >>>>>>>>> the list of which files are open.
    >>>>>>>>> I have included code snippits below if anyone has any
    >>>>>>>>> suggestions?
    >>>>>>>>>
    >>>>>>>>> openfiles /query /s \\fileserver <--- This
    >>>>>>>>> shows the
    >>>>>>>>> data
    >>>>>>>>> openfiles /query /s \\fileserver | convertto-html <---
    >>>>>>>>> This
    >>>>>>>>> shows
    >>>>>>>>> the
    >>>>>>>>> html but for meaningless data
    >>>>>>>>> openfiles /query /s \\fileserver | export-csv c:\test.csv
    >>>>>>>>> <---
    >>>>>>>>> This
    >>>>>>>>> exports a csv but with meaningless data
    >>>>>>>>> Thanks for any help!
    >>>>>>>>>
    >
    >

      My System SpecsSystem Spec
Re: Powershell openfiles.exe output
« How to configure a NIC with 2 IP addresses in powershell | Registry: Remove-ItemProperty -Confirm fails on 'No' »

Similar Threads
Thread Thread Starter Forum Replies Last Post
PowerShell output Bill PowerShell 4 29 Jan 2009
Getting Powershell output into a sql table Blue Sky PowerShell 1 04 Oct 2008
Cut the Output in Windows PowerShell Irshad Ahmed PowerShell 7 13 Feb 2008
Redirecting Output in Powershell Jasmin PowerShell 7 23 Nov 2007
powershell terminal sometimes need a C/R for output Frank PowerShell 1 24 May 2007