SQL Query Database pull - ePO 5.9.1

[User68970876]

We are integrating with a new SIEM tool and our only way to integrate data into the tool is with a direct DB pull. We are having some extreme issues with creating / finding a query that will give us all the data we need inside of the tool. We have identified multiple fields we need, but we are having a huge issue attempting to find what the MD5 (HASH) of threats found from a host based VirusScan Enterprise event.

We are having the same amount of trouble pulling the IP address of the hosts as well. For reference, the MD5 can be found on the GUI of our ePO server. We can go to the "Threat Event Log Details" view that has the malware event, and scroll down to "Additional Event details from VirusScan Enterprise". In this table, we see "MD5: [hash]". We are not sure what this value is called, and we do not know what table to pull it from.

Similarly, we can see the ip address in this table as "Threat Source IPv4 Address". We are not sure what this value is called, and we do not know what table to pull it from.

[cdinet · Accepted solution]

When you have the query created in epo that gives the data you would like to see from the siem, make sure it is saved.  Select that query, then go to actions, view sql.  That will tell you what tables and columns data is being pulled from.

[User68970876]

This seems to helped with the majority of things, but the SQL query is bringing back IP addresses up as a negative and non-decimal number.

Is there a conversion for these numbers?

[cdinet]

Yes - run this in sql:

SELECT dbo.RSDFN_ConvertIntToIPString(1979448947)

It should add a new column in the table to pull from.  If that doesn't do it, then use these steps:

SELECT *,

"AnalyzerIPV4_desc" = ( convert(varchar(3),

convert(tinyint,

substring(convert(varbinary(4),

convert(bigint,

("EPOEvents"."AnalyzerIPV4" + 2147483648))),

1,

1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),4,1))) ), "SourceIPV4_desc" = ( 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))) ), "TargetIPV4_desc" = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),4,1))) )

FROM [EPOEvents]

WITH (NOLOCK)

Once we run the above query. It will convert sourceIPV4 from the INT  to actual IP format. It will add one more column to show the Actual IP address.