SQL Report of apps & KBs installed between last 2 boot ups.

I thought I would share my report here in case it is of use to anyone else.

It shows Applications and Hotfixes installed since the last two boot ups of the target computers. Note: It is dependent on having the PDQ Inventory Agent installed (and history collected from the last 2 reboots).

I schedule this report to run daily using a collection of servers rebooted in the last 24 hours.

Sorry if the formatting is a bit off -

SELECT	ComputerName,
	EventTime,
	EventType,
	Detail
	
FROM (
	-- Get the details of the last 2 boot ups per computer
	SELECT	Computers.Name as "ComputerName",
			datetime(AgentEvents.DateTime, 'localtime') as "EventTime",
			AgentEvents.EventType,
			AgentEvents.Reason as Detail
		
FROM	Computers

INNER JOIN	AgentEvents ON Computers.ComputerId = AgentEvents.ComputerId

WHERE	AgentEvents.[DateTime] >= 
		( 
			SELECT	[DateTime] 
			
			FROM 	AgentEvents
			
			WHERE 	AgentEvents.ComputerId = Computers.ComputerId
			AND  	AgentEvents.EventType = 'SystemBoot'
			AND  	AgentEvents.IsShutdown = 0
			
			ORDER BY AgentEvents.DateTime DESC 
			
			LIMIT 1 OFFSET 1
		)
AND		AgentEvents.EventType = 'SystemBoot'
AND 	AgentEvents.IsShutdown = 0
AND		<ComputerFilter> 

UNION ALL

-- Get the details of Apps installed since the last 2 boot ups per computer
SELECT	Computers.Name AS "ComputerName",
		COALESCE(Applications.InstallDate,Applications.InstallDateSecondary ) as "EventTime",
		"SoftwareInstall" as "EventType",
		Applications.Name || " " || Applications.Publisher || " " || Applications.Version as "Detail"

FROM	Computers 

INNER JOIN	Applications ON Applications.ComputerId = Computers.ComputerId
INNER JOIN
	(
	-- JOIN to the SECOND most recent bootup event, so we can filter events
	SELECT	PBComp.ComputerId,
			PBComp.Name as ComputerName,
			( 
				SELECT	[DateTime]
		
				FROM 	AgentEvents
		
				WHERE	AgentEvents.ComputerId = PBComp.ComputerId
				AND  	AgentEvents.EventType = 'SystemBoot'
				AND  	AgentEvents.IsShutdown = 0
				
				ORDER BY	AgentEvents.DateTime DESC
				
				LIMIT 1 OFFSET 1
			) [DateTime]
	
	FROM	Computers AS PBComp
  
	) AS PBTime ON PBTime.ComputerId = Computers.ComputerId
  
WHERE	COALESCE(Applications.InstallDate,Applications.InstallDateSecondary ) >= datetime(PBTime.[DateTime], 'localtime')  -- events which have happened since the last 2 boot ups  
AND 	<ComputerFilter> -- PDQ FILTER

UNION ALL

-- Get the details of KBs installed since the last 2 boot ups per computer
SELECT	Computers.Name as "ComputerName",
		Hotfixes.InstalledOn as "EventTime",
		COALESCE( NULLIF(HotFixes.Description, "") , "GenericUpdate") as "EventType",
		HotFixes.Name || " (" || HotFixes.HelpLink || ")" as "Detail" 

FROM	Computers 

INNER JOIN	HotFixes ON Hotfixes.ComputerId = Computers.ComputerId
INNER JOIN 
	(
	-- JOIN to the SECOND most recent bootup event, so we can filter events
	SELECT	PBComp.ComputerId,
			PBComp.Name as ComputerName,
			( 
				SELECT [DateTime] 
		
				FROM	AgentEvents
		
				WHERE	AgentEvents.ComputerId = PBComp.ComputerId
				AND  	AgentEvents.EventType = 'SystemBoot'
				AND  	AgentEvents.IsShutdown = 0
				
				ORDER BY	AgentEvents.[DateTime] DESC 
				
				LIMIT 1 OFFSET 1
			) AS [DateTime]
	
	FROM	Computers AS PBComp
	  
	) AS PBTime ON PBTime.ComputerId = Computers.ComputerId
  
WHERE  Hotfixes.InstalledOn >= datetime(PBTime.[DateTime], 'localtime')  -- events which have happened since the last 2 boot ups  
AND 	<ComputerFilter> -- PDQ Filter
)

ORDER BY	ComputerName, EventTime DESC