+ Post New Thread
Results 1 to 11 of 11
MIS Systems Thread, SIMS CommandReporter exports - adding a few extra bits in Technical; Currently half way through the final setup of a shiny system of SIMS reports to auto generate Moodle classes as ...
  1. #1
    jamesfed's Avatar
    Join Date
    Sep 2009
    Location
    Reading
    Posts
    2,202
    Thank Post
    137
    Thanked 342 Times in 289 Posts
    Rep Power
    86

    SIMS CommandReporter exports - adding a few extra bits

    Currently half way through the final setup of a shiny system of SIMS reports to auto generate Moodle classes as well as import the students/teacher into those classes.

    The last thing I need to accomplish to get it to work like clockwork is add two columns which say add and student (or teacher in another report) to the front of a csv export (which is automatically run through the CommandReporter feature).

    So what I have right now is (for example)...

    person_id, classname
    7563, 10Re 1
    3721, 11P.E. 2

    and I need to it to put out something which has

    add, student, person_id, classname
    add, student, 7563, 10Re 1
    add, student, 3721, 11P.E. 2

    Now I've got it from reliably sources that I could use a PHP file to automatically add in the extra bits of information but would anyone have any idea on how to do it through the report its self?

    Will be publishing the how to as normal on my website once done

  2. #2
    jamesfed's Avatar
    Join Date
    Sep 2009
    Location
    Reading
    Posts
    2,202
    Thank Post
    137
    Thanked 342 Times in 289 Posts
    Rep Power
    86
    Little bumpy bump for the Friday afternoon

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    I can't think of anything but horrible, messy ways to do this straight from SIMS, but I'm assuming you're using a BATCH script to automate extraction with CommandReporter? If so, you can modify it to add these columns.

    Straight after you have pulled the data (and assuming the output is called "report.csv"), do this:

    Code:
    FOR /F "tokens=1,2 delims=," %%G IN (report.csv) DO (
        echo add,student,%%G,%%H >> "temp.csv"
    )
    
    MOVE /Y temp.csv report.csv
    The first part sets up a FOR loop to read your CSV line by line.

    The part between brackets, after DO, tells the script what to do with each parsed line, in this case append it to a temporary file.

    Finally, we use MOVE to replace the original "report.csv" with the new one.

    Hope this helps!

  4. 2 Thanks to LosOjos:

    jamesfed (27th June 2014), Marci (20th June 2014)

  5. #4
    jamesfed's Avatar
    Join Date
    Sep 2009
    Location
    Reading
    Posts
    2,202
    Thank Post
    137
    Thanked 342 Times in 289 Posts
    Rep Power
    86
    Cheers for that!

    Had a bit of a thought and worked out I could do it in SSIS as well (even better I might be able to use it to import the data direct into the Moodle DB without doing any extra work )

  6. #5
    MicrodigitUK's Avatar
    Join Date
    May 2007
    Location
    Wiltshire
    Posts
    334
    Thank Post
    37
    Thanked 55 Times in 51 Posts
    Rep Power
    24
    If it was me I would use comand line reporter to export in XML. Then use PHP and XSLT functions to format the XML how you need it. Then output the styled XML file.

  7. #6
    jamesfed's Avatar
    Join Date
    Sep 2009
    Location
    Reading
    Posts
    2,202
    Thank Post
    137
    Thanked 342 Times in 289 Posts
    Rep Power
    86
    Quote Originally Posted by MicrodigitUK View Post
    If it was me I would use comand line reporter to export in XML. Then use PHP and XSLT functions to format the XML how you need it. Then output the styled XML file.
    Thing is I need the output data in CSV

  8. #7
    MicrodigitUK's Avatar
    Join Date
    May 2007
    Location
    Wiltshire
    Posts
    334
    Thank Post
    37
    Thanked 55 Times in 51 Posts
    Rep Power
    24
    XSLT will transform an XML file to any txt based format you like, not just XML.

    http://pragmaticintegrator.wordpress...-csv-via-xslt/

  9. Thanks to MicrodigitUK from:

    jamesfed (27th June 2014)

  10. #8

    Join Date
    Apr 2012
    Posts
    45
    Thank Post
    0
    Thanked 6 Times in 5 Posts
    Rep Power
    6
    Hi guys, given that command exporter runs on windows only, I've been making use of powershell recently to run a whole bunch of reports (on a daily basis) and email me if they aren't blank - covering little things like... comparing if list of students matches AD to whether all staff/students have photos to whether contacts have the correct gender for their salutation. I've been adding little scripts to a daily processing run as and when they come up - from sims to checking for old sophos PC's in sophos/wsus etc

    As a simple example, some basic code to run a report from sims is below - there's a distinct lack of error checking here - but for a 'read only' reporting basis of things we manually fix, it does the job.

    Where you are looking to get CSV output, instead of using Convert-To-Html, you could probably try making use of something similar to out-file -filepath C:\Scripts\output\parents.txt -append -encoding ASCII -width 200 .

    Hopefully there might be some useful ideas here for people


    Code:
    $pinfo = New-Object System.Diagnostics.ProcessStartInfo
    $pinfo.FileName = "c:\program files (x86)\sims\sims .net\CommandReporter.exe"
    $pinfo.RedirectStandardError = $true
    $pinfo.RedirectStandardOutput = $true
    $pinfo.UseShellExecute = $false
    $pinfo.Arguments = "/USER:*username* /PASSWORD:*password* /REPORT:""DataCheck-ContactGender"" /QUIET"
    $p = New-Object System.Diagnostics.Process
    $p.StartInfo = $pinfo
    $p.Start() | Out-Null
    $stdout = $p.StandardOutput.ReadToEnd()
    $stderr = $p.StandardError.ReadToEnd()
    $p.WaitForExit()
    
    $xml = [xml]$stdout
    
    if( $xml.SuperStarReport.Record ) {
    	Write-Host "This Script checks Genders of Contacts in Sims is set correctly<br/>"
    	Write-Host "<h3>Action Required:</h3>"
    	Write-Host "Check Salutation/Gender record in sims<br/>"
    	Write-Host "<h3>Contact Records to Check:</h3>"
    
    	$xml.SuperStarReport.Record | Select-Object primary_id,Full_x0020_name,ID,Gender | ConvertTo-Html -Fragment
    }
    P.S. I think I had to add: $pinfo.StandardOutputEncoding = [text.encoding]::utf8 to handle utf8 correctly for the one student with a accent in their name.

  11. #9

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,140
    Thank Post
    403
    Thanked 622 Times in 568 Posts
    Rep Power
    181
    Are you able to share your AD comparing script?

  12. #10

    Join Date
    Apr 2012
    Posts
    45
    Thank Post
    0
    Thanked 6 Times in 5 Posts
    Rep Power
    6
    I guess ;/ It's rather 'hacky' as it was knocked together to identify 'leaving staff' I'd missed removing from AD, as opposed to something for a public release

    step 1: get list of AD staff users
    step 2: get list of current staff in sims
    step 3: get list of future staff

    In our case, we store work email address as their @sch.uk email address always (this ensures we've always got a work/primary email address for staff for things like intouch that stays within school)

    step 4: if username part of email exists in AD array we built in step 1, remove the person from array
    step 5: if array is not empty, it means there's someone that might no longer exist.


    Code:
    # Get List of All Staff
    $Searcher = New-Object DirectoryServices.DirectorySearcher
    $Searcher.Filter = '(&(objectCategory=User))'
    $Searcher.SearchRoot = 'LDAP://ou=staff,ou=accounts,DC=domain'
    $searcher.PageSize = 10
    $searcher.SizeLimit = 10000
    
    $results = $Searcher.FindAll()
    
    $ADUsers = New-Object System.Collections.ArrayList
    
    foreach ($result in $results){
    	if( [string]$result.Properties["mailNickname"] ) {
    		$a = $ADUsers.Add( [string]$result.Properties["mailNickname"].Trim().ToLower() )
    	} else {
    		$a = $ADUsers.Add( [string]$result.Properties["samaccountname"].Trim().ToLower() )
    	}
    }
    
    # Compare to Email Address on staff file in SIMS
    $pinfo = New-Object System.Diagnostics.ProcessStartInfo
    $pinfo.FileName = "c:\program files (x86)\sims\sims .net\CommandReporter.exe"
    $pinfo.RedirectStandardError = $true
    $pinfo.RedirectStandardOutput = $true
    $pinfo.UseShellExecute = $false
    $pinfo.Arguments = "/USER:user /PASSWORD:pass /REPORT:""DataCheck-StaffEmail"" /QUIET"
    $p = New-Object System.Diagnostics.Process
    $p.StartInfo = $pinfo
    $p.Start() | Out-Null
    $stdout = $p.StandardOutput.ReadToEnd()
    $stderr = $p.StandardError.ReadToEnd()
    $p.WaitForExit()
    
    $xml = [xml]$stdout
    $address1 = "";
    
    
    
    $DuplicateRecords = @()
    foreach( $record in $xml.SuperStarReport.Record ) {
    	$email = ($record["Work_x0020_Email"].InnerText) -replace "@domain", ""
    
    	$ADUsers.Remove($email.Trim().ToLower())
    }
    
    $pinfo = New-Object System.Diagnostics.ProcessStartInfo
    $pinfo.FileName = "c:\program files (x86)\sims\sims .net\CommandReporter.exe"
    $pinfo.RedirectStandardError = $true
    $pinfo.RedirectStandardOutput = $true
    $pinfo.UseShellExecute = $false
    $pinfo.Arguments = "/USER:user /PASSWORD:pass /REPORT:""DataCheck-StaffFutureEmail"" /QUIET"
    $p = New-Object System.Diagnostics.Process
    $p.StartInfo = $pinfo
    $p.Start() | Out-Null
    $stdout = $p.StandardOutput.ReadToEnd()
    $stderr = $p.StandardError.ReadToEnd()
    $p.WaitForExit()
    
    $xml = [xml]$stdout
    $address1 = "";
    
    
    $DuplicateRecords = @()
    foreach( $record in $xml.SuperStarReport.Record ) {
    	$email = ($record["Work_x0020_Email"].InnerText) -replace "@domain.co.uk", ""
    
    	$ADUsers.Remove($email.Trim().ToLower())
    }
    
    if( $ADUsers ) {
    	Write-Host "This Script Compares Staff Users Accounts in the Active Directory Against SIMS<br/>"
    	Write-Host "<h3>Action Required:</h3>"
    	Write-Host "<br/>"
    	Write-Host "<h3>Active Directory <> SIMS Differences:</h3>"
    	if( $ADUsers.Count -gt 0 ) {
    		Write-Host "<br/>SIMS Staff" $xml.SuperStarReport.Record.Count
    		Write-Host "<br/>AD Staff" $ADUsers.Count
    		Write-Host "<br/>Leavers Still in AD" $ADUsers.Count
    	}
    
    	foreach($_ in $ADUsers){ 
    		Write-Host "<br/>" 
    		Write-Host $_ 
    	}
    }
    In terms of creating users, I knocked up the following script on friday to quickly create a batch of logons for a controlled assignment today (which given the class went into the computer room and i've heard no complaints, I assume worked ok):

    Code:
    
    Write-Host "Enter subject code e.g. bs14?"
    $subject = [Console]::ReadLine()
    
    Write-Host "Enter Number of students? e.g. 30"
    $count = [Console]::ReadLine()
    
    Write-Host "------"
    
    $adgroup = "control_" + $subject
    $Group = Get-ADGroup -Filter {sAMAccountName -eq $adgroup}
    $OU = Get-ADOrganizationalUnit -filter { name -eq $adgroup}
    If ($Group -ne $Null) {
    	Write-Host "AD Group $adgroup already exists"
    	return
    }
    If ($OU -ne $Null) {
    	Write-Host "OU Group $adgroup already exists"
    	return
    }
    
    $oupath = "OU=" + $adgroup + ",OU=Controlled,OU=Accounts,DC=X,DC=SCH,DC=UK"
    New-ADOrganizationalUnit -Name $adgroup -path "OU=Controlled,OU=Accounts,DC=X,DC=SCH,DC=UK"
    New-ADGroup -Name $adgroup -Path $oupath -GroupScope Universal -GroupCategory Security
    Add-ADGroupMember control $adgroup
    
    for ($i = 1; $i -le $count; $i++) {
    	$username = $subject + $i
    	$upn = $username + "@X.sch.uk"
    	$password = (([char[]](Get-Random -Input $(65..72) -Count 3)) -join "") +
    					(([char[]](Get-Random -Input $(50..57) -Count 3)) -join "") +
    					(([char[]](Get-Random -Input $(97..104) -Count 3)) -join "")
    	$homedir = "\\X\public\users\controlled\" + $username
    
    	Write-Host "Creating user: $username with password: $password"
    
    	Write-Host "<br/>samAccountName: " $username
    	Write-Host "<br/>givenName: " $forename
    	Write-Host "<br/>Description: " $id # SIMS ID
    	Write-Host "<br/>home: " $homedir
    	Write-Host "<br/>displayName: " $display
    	Write-Host "<br/>userPrincipalName: " $upn
    	Write-Host "<br/>Password: " $password
    	Write-Host $adgroup
    			
    	New-ADUser -Name $username -SamAccountName $username -GivenName $subject  -Surname $i -DisplayName $username -Path $oupath -AccountPassword (ConvertTo-SecureString $password -AsPlainText -force) -PasswordNeverExpires $True -CannotChangePassword $True -Enabled $true -UserPrincipalName $upn -HomeDirectory $homedir -HomeDrive "N:"
    
    	Add-ADGroupMember $adgroup $username
    	
    	"$username,$password" | out-file -filepath C:\Scripts\output\ca.txt -append -encoding ASCII -width 200
    Last edited by minimoo; 30th June 2014 at 10:54 PM.

  13. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Hi @minimoo - thankls for sharing! We did start a thread a while back where we were all going to share our CommandReporter scripts, hasn't been updated in ages but maybe if you post your script there, it may prompt others to start sharing again?

    [Sims] Command reporter working examples

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIMS Learning Gateway AD Provisioning - OpenVPN
    By FishCustard in forum MIS Systems
    Replies: 4
    Last Post: 5th June 2013, 10:48 AM
  2. [SIMS] SIMS/VLE export not working
    By pdr in forum MIS Systems
    Replies: 4
    Last Post: 23rd May 2012, 02:14 PM
  3. [SIMS] help in running sims commandreporter (yet again!)
    By iceman in forum MIS Systems
    Replies: 23
    Last Post: 12th June 2011, 01:48 PM
  4. [SIMS] Ecopy Toshiba SIMS.net Connector, AD login
    By ithelp in forum MIS Systems
    Replies: 2
    Last Post: 17th March 2011, 08:28 AM
  5. [SIMS] How do you export SIMS users into AD?
    By mcnallyfc in forum MIS Systems
    Replies: 16
    Last Post: 5th November 2010, 04:59 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •