Please note that this is not a product. It is my own practical approach. It takes quite a bit to dig out all the plugins you need. What you see here is just examples and meant as inspiration.
There's also lots of commercial software that can help you out.
The most important part of this job is to locate all relevant information from underlying systems. The overview will never be better than the program that presents it. Obviously, you can add plugins crawling across several data sources, to find documents, signatures, etc., but you don't start there. As mentioned earlier, I have chosen to use MySQL. It has the functionality I'm looking for. In addition, it is free and can be installed on many platforms. What am I looking for?
- Good support for character sets
- Easy handling of data in / out of db via the file system
- Generally good functionality and easily recognizable
Character sets are important. When you retrieve data from sub systems, the output encoding can vary. If possible, specify character set by retrieval. Keep in mind that the information will be loaded into a new data model and special characters must be represented correctly.
See below for two small examples of extracting data from subsystems. You might consider installing a cron job / scheduled task to refresh the lists on a regular basis.
List of inactive AD accounts:
File inactive-users.bat:
powershell.exe -ExecutionPolicy ByPass -file inactive-users.ps1
File inactive-users.ps1:
import-module activedirectory
$domain = "your-domain"
$DaysInactive = 365
$time = (Get-Date).Adddays(-($DaysInactive))
# get AD Users with LastLogonTimestamp less than 365 days
Get-ADUser -Filter {LastLogonTimeStamp -lt $time } -Properties LastLogonTimeStamp,Name,Company,Department,SamAccountName |
# output to csv
select-object Name,Company,Department,SamAccountName,@{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp).ToString('yyyy-MM-dd_hh:mm:ss')}} | export-csv inactive-users.csv -notypeinformation -encoding utf8
List of users from one of your applications:
ORAENV_ASK=NO
ORACLE_SID=your-db
ORACLE_HOME=your-oracle-home
. $ORACLE_HOME/bin/oraenv
sqlplus /nolog <<eol|grep "tag:"|awk -F "tag:" '{ print $2 }' > app-user-list.csv
connect user/pw
select 'tag:'||user-id||','||full_name||','||company||','||days_since_last_login from users;
exit;
eol
You now have some nice comma separated files that represents a small selection of what you're up to. Now its time to load those files into MySQL. Be sure to set the character set and spot check your data. If you think you have missed out on fields containing useful information, then you must add this field and reproduce your csv file.
- create table ad (Name varchar(60), Company varchar(70), Department varchar(40), SamAccountName varchar(40), Stamp varchar(30));
- load data local infile 'inactive-users.csv' into table ad character set utf8 fields terminated by ',';
- show warnings;
- check the content: select * from ad;
- same procedure for app
MySQL is, by default, case insensitive, but if your system is case sensitive, you can always use the lower and upper functions. The tables now represent the files you have extracted from your sub systems. Dependent on the where clause used in the plugin you now might find some weird rows, mistakes from the production environment, mistypings, users intended for courses that was never held and so on. But as you have loaded logon data from several sub systems you should be able to connect them. Really, what you want to find is all digital traces for one specific person. You have to get familiar with the data and look for connections. If there are no connections you can make them. Create a table that link together sub systems. To find your entries you can make your queries wider. If you intend to reload the tables representing the sub systems you should be careful altering them. Create new tables or views to add the extra functionality you need. Here in this example I assume that the windows short name (samaccount) is the same as the one used in the app. If this is not the case you have to build a mapping table and join this to sub select with the correct id.
- To list inactive users: select * from ad;
- To list inactive users still registered in app: select * from ad where samaccountname in (select user-id from app);
- You might want to add functionality to days_since_last_login
A simple php sample presenting inactive users still registered in your app could look something along the lines of:
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<?php
set_include_path('dir ref to your php7 modules');
$servername = "localhost";
$username = "your-acc";
$password = "your-pw";
$dbname = "your-db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if (!$conn->set_charset("utf8")) {
printf("Error loading character set utf8: %s\n", $mysqli->error);
exit();
}
$sql = 'SELECT * from ad where SamAccountName in (select user-id from users) order by stamp';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>Name</th><th>Company</th><th>Department</th><th>SamAccountName</th><th>LastLogin</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["Name"]. "</td><td>" . $row["Company"] . "</td><td>" . $row["Department"] . "</td><td>" . $row["SamAccountName"] . "</td><td>" . $row["Stamp"] . "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
</body>
</html>
No comments:
Post a Comment