[sims] SIMS CommandReporter exports - adding a few extra bits

[jamesfed]

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

[jamesfed]

Little bumpy bump for the Friday afternoon

[LosOjos]

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:

 

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!

[jamesfed]

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 )

[MicrodigitUK]

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.

[jamesfed]

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

[MicrodigitUK]

XSLT will transform an XML file to any txt based format you like, not just XML.

 

http://pragmaticintegrator.wordpress.com/2012/10/28/transforming-xml-to-csv-via-xslt/

[minimoo]

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

 

 

$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
"
Write-Host "Action Required:"
Write-Host "Check Salutation/Gender record in sims
"
Write-Host "Contact Records to Check:"

$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.

[Edu-IT]

Are you able to share your AD comparing script?

[minimoo]

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.

 

 

# 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
"
Write-Host "Action Required:"
Write-Host "
"
Write-Host "Active Directory <> SIMS Differences:"
if( $ADUsers.Count -gt 0 ) {
	Write-Host "
SIMS Staff" $xml.SuperStarReport.Record.Count
	Write-Host "
AD Staff" $ADUsers.Count
	Write-Host "
Leavers Still in AD" $ADUsers.Count
}

foreach($_ in $ADUsers){ 
	Write-Host "
" 
	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):

 

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 "
samAccountName: " $username
Write-Host "
givenName: " $forename
Write-Host "
Description: " $id # SIMS ID
Write-Host "
home: " $homedir
Write-Host "
displayName: " $display
Write-Host "
userPrincipalName: " $upn
Write-Host "
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

Edited June 30, 2014 by minimoo

[LosOjos]

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?

 

http://www.edugeek.net/forums/mis-systems/57084-sims-command-reporter-working-examples.html