Reporting on whether a reboot has happened since certain software is installed
I have a report that tells me if a certain app version is installed, but I would like the report to tell me if the last restarted date/time is earlier than the install date time of the app - this way, I know the app was installed but the user hasn't rebooted their PC and I can force it or ask the user to please do so at the next opportunity.
I'm looking for the logic to put into the report where
App installed = x
Date of App installed = y
If Last Reboot (or up time or whatever) < y
-
I'm not aware of any way that you can do this all directly in a PDQ report, but you could do it in two steps. If you pull an auto report and filter it to only computers containing that app, you could export it as a CSV and have the report contain the application install date & time, boot time, and computer name. Then you could have a PowerShell script loop through the CSV file and compare the two dates, returning the computer name from the line if the computer hasn't rebooted yet. You could have the auto report save to a file and have the PowerShell script run as a scheduled task that refers to that file to have the whole process be automated.
Rough powershell code:
$InputFile = "C:\path\to\report.csv"
$OutputFile = "C:\path\to\output.txt"$InputFileObject = Import-Csv $InputFile -Delimiter ','
$FirstLine = "Computers that need a reboot:"
$FirstLine | Out-File -FilePath $OutputFile -Encoding UTF8foreach ($line in $InputFileObject) {
if ($line.ApplicationInstallDateAndTime -gt $line.ComputerBootTime) {
$line.ComputerName | Out-File -FilePath $OutputFile -Append -Encoding UTF8
}
} -
This might be overkill, but I had this running on our boxes here.
I have a script running on the box, sent out through PDQ, that checks to see if the box needs to be rebooted. It checks a configuration file on the box to make sure that I'm rebooting during the hours it SHOULD reboot, and also to check to make sure this is a box that CAN be rebooted:
$os=Get-WmiObject win32_operatingsystem
$uptime = ((get-date) - ($os.ConvertToDateTime($os.lastbootuptime))).Days
$date = get-date -format o
$pathtoconfig = "c:\windows\logs\rebootcfg.ps1"#Look to see if we've been up for 14 days (or whatever the below is set to)
if ($uptime -lt 21){$a = $date + " : We are at $uptime days... it is not time to reboot."
$a | out-file "c:\windows\logs\reboot.log" -appendbreak
}
else
{#It's time, so let's reboot. But do we have a config?
$a = $date + " : We are at $uptime days - it is time to reboot."
$a | out-file "c:\windows\logs\reboot.log" -append#Looking for the configuration file..
if (!(Test-Path $pathtoconfig)){
"Configuration file not found at $pathtoconfig" | out-file "c:\windows\logs\reboot.log" -append
& shutdown.exe /r /t 3600 /c "Your computer has a scheduled reboot. Any questions can be directed to the help desk at Ext. 8888, Thank you." /d p:01:01
"Reboot has been scheduled." | out-file "c:\windows\logs\reboot.log" -append
$wshell = New-Object -ComObject Wscript.Shell
$ts = New-TimeSpan -Hours 1
$shuttime = (get-date) + $ts
$wshell.Popup("Your workstation will reboot at $shuttime for a scheduled update. Please ensure you save any work. If you have any questions, please call the Help Desk at x8888",0,"Done",0x1)
break
}
else {
#Found it, let's load it."A configuration file was found, opening it." | out-file "c:\windows\logs\reboot.log" -append
[string]$ConfigFile = "$pathtoconfig"## Load up dat config file
if(Test-path $ConfigFile) {
. $ConfigFile
Write-Verbose "Loaded $ConfigFile"
$b = "Our window is between " + $rebootconfig.TimeAllowedLow + " and " + $rebootconfig.TimeAllowedHigh
$b | out-file "c:\windows\logs\reboot.log" -append}
Else {
Throw "Could not load config file $ConfigFile`. Exiting."
}#Get current time and compare it
if ($rebootconfig.neverreboot -eq 1){
"Machine is set to never reboot, exiting." | out-file "c:\windows\logs\reboot.log" -Append
break}
$time = (get-date).Hour
$inrange = $time -In $rebootconfig.TimeAllowedLow..$rebootconfig.TimeAllowedHigh
if ($inrange -eq "true")
{
"We are within our window, rebooting." | out-file "c:\windows\logs\reboot.log" -append
& shutdown.exe /r /t 3600 /c "Your computer has a scheduled reboot. Please save off any work to prevent loss of data. Any questions can be directed to the help desk at Ext. 8888, Thank you." /d p:01:01
$wshell = New-Object -ComObject Wscript.Shell
$ts = New-TimeSpan -Hours 1
$shuttime = (get-date) + $ts
$wshell.Popup("Your workstation will reboot at $shuttime for a scheduled update. Please ensure you save any work. If you have any questions, please call the Help Desk at x8888",0,"Done",0x1)
break
}
#Exit if we're not within the window.else {
"We are not within our window, not rebooting." | out-file "c:\windows\logs\reboot.log" -append
break
}
}
}The configuration file looks like this:
$Global:RebootConfig = @{
## Some generic things
TimeAllowedLow=8
TimeAllowedHigh=12
NeverReboot=1
} -
Luke and Brian -
thanks for your responses.
Brian - It's overkill for this need, but I liked the code and will enjoy putting it to use another time.
Luke - I could do that but I was thinking it should totally be doable with a SQL query that can compare the output. I guess what I need is the date installed of the app (so the app name and date installed fields) and the last time the computer was restarted field. Then I should be able to write it in SQL. I don't know all the fields and their relationships. I work more comfortably with MS SQL.
-
Avi,
That is true, you should be able to do it in a SQL report. I don't have much experience with the PDQ SQL reports so I didn't even consider that as an option. I cobbled this together as a quick test and it seems to work for me, just replace the value of Applications.Name for your desired application, of course:
select
Applications.InstallDate,
Applications.Name as "Application Name",
Computers.BootTime,
Computers.Name as "Computer Name"
from Computers, Applications
where Computers.ComputerId = Applications.ComputerId and Applications.Name == "Microsoft Visio Professional 2016" and Applications.InstallDate > Computers.BootTime and <ComputerFilter>(Fixed where clause from "Computers.ComputerID = Applications.ApplicationId" to "Computers.ComputerId = Applications.ComputerId")
-
Avi,
I made a mistake in my earlier SQL code, it should have been "Computers.ComputerId = Applications.ComputerId" instead of "Computers.ComputerID = Applications.ApplicationId". I updated it in my previous post.
New SQL code:
select
Applications.InstallDate,
Applications.Name as "Application Name",
Computers.BootTime,
Computers.Name as "Computer Name"
from Computers, Applications
where Computers.ComputerID = Applications.ComputerId and Applications.Name == "Microsoft Visio Professional 2016" and Applications.InstallDate > Computers.BootTime and <ComputerFilter>
Please sign in to leave a comment.
Comments
5 comments