DB column "OSServicePackVer" disappeared on ePO 5.10

[Omriil]

Hi,

our customer is pulling data to SIEM from the ePO DB.

we made an upgrade from 5.3.1 to 5.10 and now we can't find the column "OSServicePackVer" at the table EPOComputerProperties 

we know from other sites that this column exists in 5.9.1, is it possible that it was moved/removed in 5.10?

[cdinet]

I tested it on my system - try using column name OScsdversion instead

[Hawkmoon · Accepted solution]

Hi Omriil, (and so to add!) 

The column isn't missing and hasn't been removed, it’s been renamed!

Below are two queries, one from ePO 5.9 the other 5.10, they are asking only for the 'service pack' details of the devices in the managed estate.

Query from ePO 5.9.

select [EPOLeafNode].[AgentGUID], [EPOLeafNode].[AutoID] from [EPOLeafNode] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] where ( ( [EPOComputerProperties].[OSServicePackVer] is null or ltrim( rtrim( [EPOComputerProperties].[OSServicePackVer] ) ) = '' ) or ( not ( [EPOComputerProperties].[OSServicePackVer] is null or ltrim( rtrim( [EPOComputerProperties].[OSServicePackVer] ) ) = '' ) ) ) order by [EPOLeafNode].[AgentGUID] asc

The column name is defined as expected- OSServicePackVer

Query from ePO 5.10

select [EPOLeafNode].[LastUpdate], [EPOLeafNode].[NodeName], [EPOLeafNode].[AutoID] from [EPOLeafNode] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] where ( ( [EPOComputerProperties].[OSCsdVersion] is null or ltrim( rtrim( [EPOComputerProperties].[OSCsdVersion] ) ) = '' ) or ( not ( [EPOComputerProperties].[OSCsdVersion] is null or ltrim( rtrim( [EPOComputerProperties].[OSCsdVersion] ) ) = '' ) ) ) order by [EPOLeafNode].[LastUpdate] asc

The column name is defined here as - OSCsdVersion

In line with the details and contents of the 'client' device.

For example the details can be collected 'locally' via WMI:        wmic os get Caption,CSDVersion /value

or via Powereshell.
Ref:

https://docs.microsoft.com/en-us/dotnet/api/microsoft.powershell.commands.computerinfo.oscsdversion?...

Best speak with the SIEM team to see about having the default query (if this a default query) updated, else if it’s a custom creation simply swap the names out.