Getting Data In

DB query to fetch logs from McAfee ePO 5.10

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!