locked
Powershell String Manipulation - Stripping out info in CSV files RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Evening all,

    I'll describe my scenario first and then the request for help.  Wise minds may spot a solution that I'm not seeing.

    I've generated an audit report from Office365's Auditing centre that details specific user account actions. The resultant file has four columns: -

    • CreationDate - Contains UTC Timestamp
    • UserIDs - Contains domain specific domain data
    • Operations - Single string value limited to available user options
    • AuditData - String data that contains all data pertaining to a user operation. Lots of information under one header all separated by a comma (,).  This column encloses its data between curly brackets, which consists 22 key value pairs (""Label":"Value","Label2":"Value2")

    The fourth column is the one I'm interested in a some of the value pairs in this column contain file paths of files I want to identify.  The relevant key pairs are: -

    1. "Operation":"FileDownloaded"
    2. "ObjectId":"https:\/\/SharePointURL\Sites\DocLibrary\FolderName\Doc.doc"

    The Object ID value pair contains various pathways but I'm only interested in a specific URL as I can use it to filter down relevant documents.  I've searched the PS Help Files for string manipulation commands but fear I'm not understanding how to use them.  I've tried: -

    • Import-CSV | Select-Object AuditData but unclear on how to progress next
    • Select-String -Path ./Report.csv -Pattern "URL" -AllMatches

    This second set returned nothing.

    So I'm a little confused on what to try next.  I suspect that the first command can be developed a little more with hashtables but I don't know how to isolate the strings in question.

    Any ideas on how to progress would be appreciated.

    Steven Andrews
    SharePoint Business Analyst: LiveNation Entertainment
    Blog: baron72.wordpress.com
    Twitter: Follow @backpackerd00d
    My Wiki Articles: CodePlex Corner Series
    Please remember to mark your question as "answered" if this solves (or helps) your problem.

    Friday, June 22, 2018 11:45 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    The field contains Json strings.  Just convert it to an object and you have what you are looking for.

    Import-Csv <file> |
     ForEach-Object{
             $_.AuditData | ConvertFrom-Json
     }

    \_(ツ)_/

    Saturday, June 23, 2018 12:12 AM