none
Powershell DataTables, DataViews and DataSets RRS feed

  • General discussion

  • Hello All,

    I am loving using a DataTable in a Powershell script to help me sort through FTP logs and identify jobs matching certain criteria.

    However, I am having trouble with my non-programmers understanding of getting it all working.

    So I'm using Windows FTP logs as the source data. I use Get-Content and a bit of magic to get the column headings then create my Datatable and add the columns. Then I add each remaining row to the DataTable ($dt).

    I am then able to find all jobs that are using TLS by segregating the single rows that have 'TLS' in  the CS-URISTEM column:

    $QueryTLS = @"
    csuristem = 'tls'
    "@
    
    $TlsJobs = $dt.select($QueryTLS)

    This then allows me to cycle through the entire datatable and remove the rows that are associated with each TLS enabled job:

    Foreach ($Job in $TlsJobs) {
       $cip = $job.cip
       $cport = $job.cport
    
       $rows = $dt.Select("cip = '$cip' AND cport = '$cport'")
    
       Foreach ($row in $rows) {
          $row.delete()
       }
    }
    

    Now I need to segregate every remaining job! But I'm struggling...

    I believe that the way to do it is to look for the unique jobs, just as above. But each TLS job does have one single row that contains 'tls' in the cs-uristem column. So it's easy.

    Every job has a source IP address and a source port. The combination of these two values should be pretty much unique (or will be as we sample daily so any that are not today, should be tomorrow!). So I need to segregate these two by querying something along the lines of (written in Toms Query Language):

    SELECT all WHERE (cip AND cport) are unique together

    Then I could count the number of jobs being submitted and loop through and create objects for each job (or whatever else I need to do).

    But as Powershell doesn't understand Toms Query Language, can someone please point out what I need to do please?

    I have been looking at using a DataView but I can't figure out how to use in PoSH properly as all articles are for VB or C# or ADO which is all very foreign to me.

    Please help.

    Kind regards

    Thursday, June 22, 2017 11:22 AM

All replies

  • $dt | Group-Object cip, cport

    This will give you all counts by these two unique values.

    Be sure there are no filters defined on the table.


    \_(ツ)_/

    Thursday, June 22, 2017 11:33 AM
  • Thanks jrv

    Sadly, I didn't mention that I think I need to use Datatables or DataViews or DataSets for performance reasons. I have multiple FTP servers generating multiple logs. Each log has over 1000000 rows. Group-object won't run quick enough for me to extract the insecure jobs on a daily basis.

    Thursday, June 22, 2017 11:42 AM
  • You need a database to improve performance.  A datatable will not give you better performance.  The cost f loading it will be an issue.

    1M rows is not a lot for an Access database and would be even faster in a SQL database.

    Group-Object is not that slow.  It can group in one pass.


    \_(ツ)_/

    Thursday, June 22, 2017 1:56 PM
  • I would add that you may want to take a look at the Log Parser tool. (This isn't the right place to ask Log Parser questions, however.)

    -- Bill Stewart [Bill_Stewart]

    Thursday, June 22, 2017 2:44 PM
    Moderator
  •  

    Yes - LogParser is a high performance log analyzer tool.  It is supported on www.iis.net at https://forums.iis.net/default.aspx/51?Logparser+Forums


    \_(ツ)_/


    • Edited by jrv Thursday, June 22, 2017 2:54 PM
    Thursday, June 22, 2017 2:53 PM
  • Guys,

    Thanks for your comments.

    I am quite aware that there are probably better ways to crack this egg. But I was under the impression that this was a place to share scripting knowledge and not our sysadmin skills.

    If anyone knows how to manipulate a dataTable or DataView (in Powershell) to provide only rows where a combination of two columns are unique, it would be great for this knowledge to be shared on this public forum. I have searched the infoweb quite extensively and can only find reference to it in programming languages I don't have time to learn properly.

    Cheers

    Tomsk

    Friday, June 23, 2017 9:16 AM
  • What you are asking is not part of a table or data set.  The Group-Object command or loading into a true database are the answers for obtaining a DataTable.  "LogParser" is the usually tool used with IIS log files and can do what you ask in one line of code.

    Just because you can think of something does not make it possible. 

    I have loaded and parsed these files with LogParser and with datatable objects for mny years.  The tables and sets are not "SQL".  YOU can sort and filter a table.  You cannot group a table as a set.  You can create a table relation and use the relation to create a subdivided child table.  All of this takes a good fundamental understating of data and the ADO.Net objects. 

    THe ADO.Net objects have been around for almost 20 years.  There are thousands of examples of how to use them if you search.  YOu will not find what you are looking for except as a fairly large piece of code that synthesizes a grouping operation.  Access, SQLServer and LogPraser can all group records in the way you are asking.

    You can get a simple master table that will allow for a relation that can allow you to code a grouping operation.

    $dt.DefaultView.ToTable($true,'cip','cport')

    This will create a table of unique instances of the two columns.


    \_(ツ)_/

    Friday, June 23, 2017 9:33 AM
  • jrv,

    Spot on. Thank you for this help. No idea what I was doing wrong yesterday but I suspect I was missing the $ before true (as this is not required in C#).

    Maybe at some other time when I (or anyone else out there) is using a datatable for a more appropriate reason, they might struggle with this and be glad to find the answer to the question right here.

    Cheers

    Tomsk

    Friday, June 23, 2017 10:12 AM
  • Most of the issue seems to be that you have asked a very confusing question.  Try just asking a simple question first.  Too much arbitrary information tends to obscure your actual question.  It did not sound like you wanted unique keys but that you wanted a count of records by key.


    \_(ツ)_/

    Friday, June 23, 2017 10:22 AM