Notification

Icon
Error

Software : changes last 24 hours Enhancement

Posted: Thursday, November 7, 2019 5:22:39 PM(UTC)
Dan S

Dan S

Member Original PosterPosts: 2
0
Like
Hi All

I'm trying to use the software changes last 24 hours report and noticed although its tracking windows software it doesn't track the operating system when that updates. Is there a way to report on this either with all the other software changes which would be most preferable or as a separate report?

If i go into the history of a machine i can see at a KB level certain patches with the plus/minus so its being tracked i just don't know how to get this incorporated in the software report?
RC62N
#1RC62N Member Posts: 450  
posted: 11/7/2019 10:05:53 PM(UTC)
Software changes and KB updates are tracked in two different tables. You can either run two separate reports or use a bit of sticky tape (a Union) and combine them.

e.g.
Code:
Select Top 1000000
  tsysOS.Image As icon,
  tsysOS.OSname,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Description,
  Case tblSoftwareHist.Action
    When 1 Then 'Ins >>>'
    When 2 Then '<<< Rmv'
    Else '???'
  End As [+/-],
  tblSoftwareUni.softwareName As Software,
  tblSoftwareHist.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Convert(Char(10),tblSoftwareHist.Installdate,120) As InstallDate,
  tblSoftwareHist.Lastchanged
From
  tblAssets
  Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
  Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
  tblSoftwareHist.Lastchanged > GetDate() - 1
  And tblAssetCustom.State = 1

Union

Select Top 1000000
  tsysOS.Image As icon,
  tsysOS.OSname,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Description,
  Case tblQuickFixEngineeringHist.Action
    When '1' Then 'Ins >>>'
    When '2' Then '<<< Rmv'
    Else '???'
  End As [+/-],
  tblQuickFixEngineeringUni.HotFixID As Software,
  Null As Version,
  tblQuickFixEngineeringUni.Description As Publisher,
  Case
    When IsDate(tblQuickFixEngineeringHist.InstalledOn) = 1
    Then Convert(Char(10),Convert(DateTime,tblQuickFixEngineeringHist.InstalledOn),120)
    Else Null
  End As InstallDate,
  tblQuickFixEngineeringHist.Lastchanged
From
  tblAssets
  Inner Join tblQuickFixEngineeringHist On tblAssets.AssetID = tblQuickFixEngineeringHist.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
  tblQuickFixEngineeringHist.Lastchanged > GetDate() - 1
  And tblAssetCustom.State = 1

Order By
  AssetName,
  Lastchanged Desc,
  InstallDate Desc,
  Software

Active Discussions

Lansweeper Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34  
Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein  
Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)