Getting Data In

DB query to fetch logs from McAfee ePO 5.10

krvamsireddy
Explorer

We upgraded the McAfee ePO from 5.9 to 5.10 after that splunk integration was broken, so i checked some articles and prepared the below query, when i use the rising column in Splunk db query , we face the below error. 

Error: " Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to bigint."

Please help me on how to resolve this error.

Db Query :

SELECT

 [EPOEvents].[ReceivedUTC] as [timestamp],  [EPOEvents].[AutoID],

    [EPOEvents].[ThreatName] as [signature],

    [EPOEvents].[ThreatType] as [threat_type],

    [EPOEvents].[ThreatEventID] as [signature_id],

    [EPOEvents].[ThreatCategory] as [category],

    [EPOEvents].[ThreatSeverity] as [severity_id],  [EPOEvents].[DetectedUTC] as [detected_timestamp],

    [EPOEvents].[TargetFileName] as [file_name],

    [EPOEvents].[AnalyzerDetectionMethod] as [detection_method],

    [EPOEvents].[ThreatActionTaken] as [vendor_action],

    CAST([EPOEvents].[ThreatHandled] as int) as [threat_handled],

    [EPOEvents].[TargetUserName] as [logon_user],  [EPOComputerPropertiesMT].[UserName] as [user],

    [EPOComputerPropertiesMT].[DomainName] as [dest_nt_domain],  [EPOEvents].[TargetHostName] as [dest_dns],

    [EPOEvents].[TargetHostName] as [dest_nt_host],  [EPOComputerPropertiesMT].[IPHostName] as [fqdn],

    [dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),4,1))) ),

    [EPOComputerPropertiesMT].[SubnetMask] as [dest_netmask],

    [EPOComputerPropertiesMT].[NetAddress] as [dest_mac],

    [EPOComputerPropertiesMT].[OSType] as [os],

    [EPOComputerPropertiesMT].[OSVersion] as [os_version],

    [EPOComputerPropertiesMT].[OSBuildNum] as [os_build],

    [EPOComputerPropertiesMT].[TimeZone] as [timezone],  [EPOEvents].[SourceHostName] as [src_dns],

    [src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),4,1))) ),  [EPOEvents].[SourceMAC] as [src_mac],

    [EPOEvents].[SourceProcessName] as [process],

    [EPOEvents].[SourceURL] as [url],

    [EPOEvents].[SourceUserName] as [source_logon_user],  [EPOEvents].[AnalyzerName] as [product],

    [EPOEvents].[AnalyzerVersion] as [product_version],

    [EPOEvents].[AnalyzerEngineVersion] as [engine_version],

    [EPOEvents].[AnalyzerDATVersion] as [dat_version] FROM "ePO_server"."dbo"."EPOEvents"

LEFT JOIN "ePO_server"."dbo"."EPOLeafNodeMT" ON [EPOEvents].[AgentGUID] = [EPOLeafNodeMT].[AgentGUID] LEFT JOIN "ePO_server"."dbo"."EPOProdPropsView_VIRUSCAN" as [EPOProdPropsView_VIRUSCAN] ON [EPOLeafNodeMT].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]

LEFT OUTER JOIN "ePO_server"."dbo"."EPOProdPropsView_THREATPREVENTION" ON [EPOLeafNodeMT].[AutoID] = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID]

LEFT JOIN "ePO_server"."dbo"."EPOComputerPropertiesMT" ON [EPOLeafNodeMT].[AutoID] = [EPOComputerPropertiesMT].[ParentID]

LEFT JOIN "ePO_server"."dbo"."EPOEventFilterDesc" ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] AND ([EPOEventFilterDesc].[Language]='0409')

WHERE [EPOEvents].[AutoID] > ?

ORDER BY [EPOEvents].[AutoID] ASC;

Labels (1)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...