Custom Server report

[JohanKotze]

Good day,

I've been trying to create a report that inventory my Server landscape and provide me with a quick oversight of my servers.

I've managed to write a report and its almost where I want it however I want to dynamically add into based on what is installed on the server from an Anti virus and Patch management software perspective.

I have a major challenge with the reporting but comes down to the correct SQL query, however I'm not an DBA and SQL is not my strongest skill thus my results are skewed... I have 320 servers. We running Trellix(Mcafee) Protection Endpoint protection, EDR and firewall etc..

Using the build in report of Servers with AV i get 963 results as it lists all the different component of Trellix as well as other software like Malware bytes etc.

If change my criteria to use an != 'software i dont want to see' And != 'Some other Security software' my results drops to 312 which tells me I'm out with 8 servers on the report...

I want to query whether "McAfee Endpoint Security Platform" is installed and what version is running on the system. so it can return me a value of Yes or No, if the value is yes it must show me the version number of that software...

Similarly I want to list if "ManageEngine Endpoint Central" is installed showing me a yes or no, and if yes the version.

here is my Query thus far... how can it be changed to accommodate the YES/NO for the 2 specific pieces of software i want and showing me all 320 servers?

Thank you in advance for the help and time...

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP As SP,
unioned.software As [Anti-Virus],
unioned.assetid,
unioned.version As [AV Version],
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan],
tblSoftwareUni.softwareName
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (unioned.software Is Null Or (unioned.software != 'Malwarebytes version'
And unioned.software != 'Kaspersky Security Center 10 Network Agent' And
unioned.software != 'FortiClient' And unioned.software !=
'McAfee Endpoint Security Package Designer' And unioned.software !=
'McAfee Endpoint Security Adaptive Threat Protection' And
unioned.software != 'McAfee Endpoint Security Web Control' And
unioned.software != 'McAfee Endpoint Security Threat Prevention'
And unioned.software != 'McAfee Endpoint Security Firewall')) And
tblSoftwareUni.softwareName Like '%Central - Agent%' And
tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[JohanKotze]

Thank you this is a very nice report and will surely be utilizing it for the security team. let me play and see if i can achieve my objective.

[Hendrik_VE]

This is the resulting output to give you an idea. It's colour coded, in case the AV signature is too old the line is displayed in red.

Report is pushed to PowerBI so I get a more visual representation of my AV status.

[Hendrik_VE]

Unfortunately I don't have the time to look into your query, but this is the report I am using (we're also using McAfee ENS). Please note that you have to scan a couple of registry keys in order to report all information (EPO server and DAT version).

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tblAssets.IPAddress,
  unioned.software As AVSoftware,
  DATQuery.Value As DATversion,
  DATQuery.Lastchanged As LastChanged,
  Case CharIndex('|', EPOQuery.Value)
    When 0 Then EPOQuery.Value
    Else SubString(EPOQuery.Value, 1, CharIndex('|', EPOQuery.Value) - 1)
  End As EPOServer,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Scanserver,
  Case
    When Convert(nvarchar,DateDiff(day, DATQuery.Lastchanged, GetDate())) > 7
    Then '#ffadad'
    When unioned.software = '' Or unioned.software Is Null Then '#ffff00'
    Else '#d4f4be'
  End As backgroundcolor
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join (Select tblRegistry.AssetID,
        tblRegistry.Regkey,
        tblRegistry.Valuename,
        tblRegistry.Value,
        tblRegistry.Lastchanged
      From tblRegistry
      Where tblRegistry.Valuename Like '%ePOServerList%') EPOQuery On
    EPOQuery.AssetID = tblAssets.AssetID
  Left Join ((Select a.assetid As assetid,
        a.software As software,
        a.version As version,
        'software comparison' As RetrievedFrom,
        '' As Enabled,
        '' As Uptodate
      From (Select tblSoftware.AssetID As assetid,
              tblSoftwareUni.softwareName As software,
              tblSoftware.softwareVersion As version
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.softID =
                tblSoftwareUni.SoftID
              Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
                tsysantivirus.Software) a)) unioned On tblAssets.AssetID =
    unioned.assetid
  Left Join (Select t.AssetID,
        t.Regkey,
        t.Valuename,
        t.Value,
        t.Lastchanged
      From (Select tblRegistry.AssetID,
              Max(tblRegistry.Lastchanged) As max_lastchanged
            From tblRegistry
            Where tblRegistry.Regkey Like '%McAfee%' And
              tblRegistry.Valuename Like '%version%'
            Group By tblRegistry.AssetID) As m
        Inner Join tblRegistry As t On t.AssetID = m.AssetID And
          t.Lastchanged = m.max_lastchanged And t.Valuename Like '%version%' And
          t.Regkey Like '%McAfee%') DATQuery On DATQuery.AssetID =
    tblAssets.AssetID
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.Image,
  tblAssets.Domain,
  tblAssets.IPAddress,
  unioned.software,
  DATQuery.Value,
  DATQuery.Lastchanged,
  tsysIPLocations.IPLocation,
  tsysOS.OSname,
  tblAssets.SP,
 tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Scanserver,
  EPOQuery.Value
Order By tblAssets.AssetName