FOG Client Last Check-in Report

[fry_p]

Hi All,
Not sure if this is the right section to post in, but I am tasked with deploying a small script to install a web filter client to every PC in our district. I think snapins are perfect for this. The only issue I see is that on some PC’s that were imaged a while ago with older images, the fog client is either missing or in a “funky” state. I was hoping to somehow either query the database or use the API (though I am very unfamiliar with it) to run a custom report to see the last client check-in time for each host (if any) and possibly the version. Hopefully this will tell me what needs to be updated or fixed. Below are my specs:

FOG 1.5.5 on CENTOS7
Client version vary, but the most common one should be 0.11.16.
Client OS ranges from Windows 7 SP1 to Win 10 1803.

As always, many thanks for everything you guys do!

[george1421]

@fry_p I’m not sure I can help you with the fog client checking in but looking at the tables it looks like we might be able to leverage the usertracking table.

MariaDB [fog]> describe hosts;
+------------------+---------------+------+-----+---------------------+----------------+
| Field            | Type          | Null | Key | Default             | Extra          |
+------------------+---------------+------+-----+---------------------+----------------+
| hostID           | int(11)       | NO   | PRI | NULL                | auto_increment |
| hostName         | varchar(16)   | NO   | UNI | NULL                |                |
| hostDesc         | longtext      | NO   |     | NULL                |                |
| hostIP           | varchar(25)   | NO   | MUL | NULL                |                |
| hostImage        | int(11)       | NO   |     | NULL                |                |
| hostBuilding     | int(11)       | NO   |     | NULL                |                |
| hostCreateDate   | timestamp     | NO   |     | CURRENT_TIMESTAMP   |                |
| hostLastDeploy   | datetime      | NO   |     | NULL                |                |
| hostCreateBy     | varchar(50)   | NO   |     | NULL                |                |
| hostUseAD        | char(1)       | NO   | MUL | NULL                |                |
| hostADDomain     | varchar(250)  | NO   |     | NULL                |                |
| hostADOU         | longtext      | NO   |     | NULL                |                |
| hostADUser       | varchar(250)  | NO   |     | NULL                |                |
| hostADPass       | varchar(250)  | NO   |     | NULL                |                |
| hostADPassLegacy | longtext      | NO   |     | NULL                |                |
| hostProductKey   | longtext      | YES  |     | NULL                |                |
| hostPrinterLevel | varchar(2)    | NO   |     | NULL                |                |
| hostKernelArgs   | varchar(250)  | NO   |     | NULL                |                |
| hostKernel       | varchar(250)  | NO   |     | NULL                |                |
| hostDevice       | varchar(250)  | NO   |     | NULL                |                |
| hostInit         | longtext      | YES  |     | NULL                |                |
| hostPending      | enum('0','1') | NO   |     | NULL                |                |
| hostPubKey       | longtext      | NO   |     | NULL                |                |
| hostSecToken     | longtext      | NO   |     | NULL                |                |
| hostSecTime      | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| hostPingCode     | varchar(20)   | YES  |     | NULL                |                |
| hostExitBios     | longtext      | YES  |     | NULL                |                |
| hostExitEfi      | longtext      | YES  |     | NULL                |                |
| hostEnforce      | enum('0','1') | NO   |     | 1                   |                |
+------------------+---------------+------+-----+---------------------+----------------+
29 rows in set (0.00 sec)

MariaDB [fog]> describe userTracking;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| utID       | int(11)      | NO   | PRI | NULL              | auto_increment |
| utHostID   | int(11)      | NO   | MUL | NULL              |                |
| utUserName | varchar(50)  | NO   | MUL | NULL              |                |
| utAction   | varchar(2)   | NO   | MUL | NULL              |                |
| utDateTime | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
| utDesc     | varchar(250) | NO   |     | NULL              |                |
| utDate     | date         | NO   |     | NULL              |                |
| utAnon3    | varchar(2)   | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)

This query should combine the two tables into a report giving you the name of the computer and user where the user was reported between 01-Mar-19 and the end of the year.

select hostName, utUserName, utDate from userTracking left join hosts on utHostID=hostID where utDate between '2019-03-01' and '2019-12-31' and utAction=1 order by hostName,utDate desc;

If you want know the name of systems that haven’t checked in since 01-Mar-19 then this query

 select distinct hostName from userTracking left join hosts on utHostID=hostID where utDate < '2019-03-01' and utAction=1 order by hostName,utDate desc;

Now I don’t use the FOG client or snapins in my organization because we already had an application deployment tool in place (PDQ Deploy). Admin Arsenal also has a free version of PDQ Deploy that will work for your quest too. As for harmonizing your FOG client across the board you could use PDQ Deploy and create a batch file to remove the old fog client, flush the fog client directory and redeploy a new (current) fog client to all computers in your domain or selection list.

[fry_p]

@george1421 I will play with this tomorrow as I am out of the office today. Thanks for the reply!

[fry_p]

@george1421
The first query you list gives me a table of 45,967 rows and displays the logins since 3-1-19 of what appears to be my entire host list.
The second query you list gives me a table of 1053 rows and displays what appears to be my entire host list.

I’m not an expert on DB stuff at all, but it looks like the first query is designed to look at the most recent logins (since 3-1-19). I tried “>” instead of a date range in the first query and got less rows, but it is just showing any login dates prior to that date. It isn’t quite telling me for sure if there are more recent logins. Also, in the first query, if I use the “<” method prior to '2019-01-01" I get an empty set.

[george1421]

@fry_p

select hostName, utUserName, max(utDate) from userTracking left join hosts on utHostID=hostID where utAction=1 group by hostname, utUserName order by utDate desc;

[fry_p]

@george1421 I took a complete 180 on the methodology on this problem with the help of my co-worker who is better with Powershell than I am. Below is the sanitized version of our simple script to check Domain PC’s for the FOG Client:

ForEach ($Computer in (Get-ADComputer -Filter * -SearchBase "OU=Sample,DC=yourdomain,DC=com")) {
$ComputerName = $Computer.Name
If (Test-Connection $ComputerName -Count 1 -Quiet) {
    $FogService = Get-Service -Name FogService -ComputerName $ComputerName -ErrorAction SilentlyContinue
        If ($FogService) { Write-Output "FOG is running on $ComputerName" | Out-File C:\Scripts\FOG.csv -Append}
        Else {Write-Output "FOG is not running on $ComputerName" | Out-File C:\Scripts\FOG.csv -Append }}
Else {Write-Output "$ComputerName is Offline" | Out-File C:\Scripts\FOG.csv -Append}
}

It isn’t the prettiest, but it is versatile in the way that you can really sub the FOGService with any other service name to check if said service is installed and running. I have a initial CSV for the online PC’s that had a broken or not installed FOG Client and set a GPO Startup script to remove any old service, install the new service, and finally start the service. Below is a sanitized batch file for that:

msiexec /q /x C:\LegacyClientInstaller\FOG Service Installer.msi"
msiexec /i "C:\NewClientInstaller" /quiet USETRAY="0" HTTPS="0" WEBADDRESS="YourFOGAddress" WEBROOT="/fog" ROOTLOG="1"
net start FOGService

Hope this may help someone someday!

[EduardoTSeoane]

@fry_p Maybe too late, but in addition to complete a few:

You can configure the fog client to delayed-auto and to reboot the service on each error with the commands below recommended before start it:

sc.exe config FOGService start=delayed-auto
sc.exe failure FOGService actions= restart/60000/restart/60000/restart/ reset= 120.