Why is Splunk Add-on for McAfee ePO data not indexing, but SQL query works in Splunk DB Connect 2?

[brian_799]

Hello!

I tried to set up indexing of McAfee ePO data using the Splunk Add-on for McAfee and DB Connect 2. The database Connection works fine, when configuring the database Input in the DBX2-App, the SQL query shown in the documentation of the McAfee add-on works fine and shows appropriate results. Nevertheless, there is no data indexed - no results when I search for the defined sourcetype McAfee:epo. The health Dashboard of the dbx2-App shows a total of 1125 successful Connections for the EPO database - still there is no data actually indexed. Does anyone have a hint where the problem might be?

Thanks!

[vidda42]

i had the same problem. it come from date format.
i change Output Timestamp Format: yyyy-MM-dd HH:mm:ss to Output Timestamp Format: epoch time.
Now McAfee ePO data is indexing

[pj]

If your database is large/massive, you probably dont want the query above running on your entire database. Instead you might want to add a where clause on the end to only pull back the last day's worth of events. Such as:

where [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )

[reswob4]

I'm having this same problem and on my end it looks like the last part of the query

WHERE [EPOEvents].[AutoID] > 0

is where the issue is. It seems if I select the following parameters in inputs.conf

mode = tail
tail_follow_only = 1
tail_rising_column_name = AutoID
tail_rising_column_number = 2

that the number in the query should increase each time. In this person's question (http://answers.splunk.com/answers/234052/splunk-add-on-for-mcafee-210-with-db-connect-201.html) you can see his autoid is very large. Mine has not changed from 0. When you look at the sample data from Operations - DB Inputs - Choose and Preview Table, and convert the timestamp, you find that is is constantly pulling the OLDEST entries from ePO. So it seems that autoid number should be changed every time the query runs. But it's not on mine.

So #1, this could be your problem

2, is there a setting to fix this?

[reswob4]

So I manually increased that number in the inputs.conf and immediately started getting data. BUT the number still didn't increase so I don't know if I will keep getting data. I will check tomorrow.

[sbochniewicz]

I am having the same problem it looks like it worked once, imported 10,000 row, the wrong direction then stopped. Even though the logs make it seem to be running every minute.

[sbochniewicz]

I removed the TOP 10000 from the Query and it started working.

[bwooden]

Is it cofigured to store in the main index or other? Do you see results for "index=* sourcetype=mcafee:epo"? Are you using the AutoID rising column - and if so do you have the "where" substitution configured correctly in the query? What does your query look like?

[brian_799]

i configured it to store in the main index. no results on the "index=* sourcetype=McAfee:epo" query. Interestingly i found in the "data summary" of the search app that 43,000 Events with the sourcetype "McAfee:epo" are indexed (last update: 16/07/2015 05:16:08.000 - the night after i initially configured the McAfee indexing). clicking on the sourcetype the search Shows no results, also when trying the index=*.

I am using exactle the configuration described in the splunk documentation for the McAfee addon, which is Rising column AutoID, here's the query:

SELECT TOP 10000 [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], [EPOEventFilterDesc].[Name] as [event_description], [EPOEvents].[DetectedUTC] as [detected_timestamp], [EPOEvents].[TargetFileName] as [file_name], [EPOEvents].[AnalyzerDetectionMethod] as [detection_method], [EPOEvents].[ThreatActionTaken] as [vendor_action], [EPOEvents].[ThreatHandled] as [threat_handled], [EPOEvents].[TargetUserName] as [logon_user], [EPOComputerProperties].[UserName] as [user], [EPOComputerProperties].[DomainName] as [dest_nt_domain], [EPOEvents].[TargetHostName] as [dest_dns], [EPOEvents].[TargetHostName] as [dest_nt_host], [EPOComputerProperties].[IPHostName] as [fqdn], [dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),4,1))) ), [EPOComputerProperties].[SubnetMask] as [dest_netmask], [EPOComputerProperties].[NetAddress] as [dest_mac], [EPOComputerProperties].[OSType] as [os], [EPOComputerProperties].[OSServicePackVer] as [sp], [EPOComputerProperties].[OSVersion] as [os_version], [EPOComputerProperties].[OSBuildNum] as [os_build], [EPOComputerProperties].[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], [EPOComputerProperties].[IsPortable] as [is_laptop], [EPOEvents].[AnalyzerName] as [product], [EPOEvents].[AnalyzerVersion] as [product_version], [EPOEvents].[AnalyzerEngineVersion] as [engine_version], [EPOEvents].[AnalyzerEngineVersion] as [dat_version], [EPOProdPropsView_VIRUSCAN].[datver] as [vse_dat_version], [EPOProdPropsView_VIRUSCAN].[enginever64] as [vse_engine64_version], [EPOProdPropsView_VIRUSCAN].[enginever] as [vse_engine_version], [EPOProdPropsView_VIRUSCAN].[hotfix] as [vse_hotfix], [EPOProdPropsView_VIRUSCAN].[productversion] as [vse_product_version], [EPOProdPropsView_VIRUSCAN].[servicepack] as [vse_sp] FROM [EPOEvents] left join [EPOLeafNode] on [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOEventFilterDesc] on [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') WHERE [EPOEvents].[AutoID] > 0

[bwooden]

Do you get results if you search by index time instead? Try "index=* sourcetype=mcafee:epo _index_earliest=-7d"

[brian_799]

No search results on this query as well. Interestingly, the search data summary still shows the same amount of indexed Events for sourcetype McAfee:epo with the same value in "last updated" after three days in which i left the app running with the initial configuration.

[bwooden]

And you're running the search from an Administrator account or User account ? Does this search return any results:
| metadata type=sourcetypes | search sourcetype=mcafee*

[riqbal]

We have the same senerio.
Database connection is working fine.
query is returing data.
but no data is getting indexed.

the about query is also showing no results

[brian_799]

this seems to be the same info as shown in the search data summary. it's basically two things that currently worry me:

1. there seems to be some data indexed with the sourcetype mcafee:epo but searching it does not display results
2. there is no new mcafee:epo data indexed, the last indexed data is still from the time when i first created the dbconnect data Input.

[brian_799]

Administrator account.
The search from above Returns the following:

firstTime↕ lastTime↕ recentTime↕ sourcetype↕ totalCount↕ type↕ 
1436968562  1437023762  1437016568  mcafee:epo  43811  sourcetypes