Using Splunk DB Connect to join splunk index to a table in sql server and fetch relevant data

[neltonk]

Hi,
I am new to SPL and Splunk. I use the following query to find PTP violations per server

index=indexwintimesynclogs|eval offset=Delta|where offset>0.0001 and like(ServerName,"%PRD%") | stats max(offset) as offset, count(offset) as violations by ServerName, TimeSource|sort -offset

Now I want to join the results of this query to the CMDB database and get the OS and Application details for each server reported by the above query.

What I have done so far?
- I have wrote the SQL query that is required to fetch data from CMDB
- Installed the App DB Connect and configured connection to CMDB
- Tested the connectivity and DB query -> Splunk DB Connect->Data Lab -> SQL Explorer
- I tried using dbxlookup to join:

index=indexwintimesynclogs|eval offset=Delta|where offset>0.0001 and like(ServerName,"%PRD%") | stats max(offset) as offset, count(offset) as violations by ServerName, TimeSource|sort -offset
| dbxlookup connection="CMDB" query="SELECT [T0].[COMPUTERNAME] As 'Server',
[T0].AD_DOMAINNAME,
[T0].[COMPUTERENVIRONMENT] As 'Environnement',
[T0].[OSNAME] As 'OS',
[T0].[Id_location] AS 'Location',
[T2].[APPLICATIONNAME] As 'ApplicationName',
[T2].[APPLICATIONCODE] As 'ApplicationCode',
[T2].ID_MANAGEMENTTEAM As 'Management team (Application)'
FROM [Publishing].[dbo].INFRASTRUCTURE_Server AS T0
JOIN [Publishing].[dbo].GENERAL_Relations AS T1 ON T0.ID_SERVER = T1.ID_PARENT AND T1.CIT_PARENT = 'Server' AND LINKTYPE = 'CST_APP2SRV'
JOIN [Publishing].[dbo].APPLICATION_Application AS T2 ON T2.ID_APPLICATION = T1.ID_CHILD AND T1.CIT_CHILD = 'Application'
LEFT JOIN [Publishing].[dbo].ORGANIZATION_BusinessLine AS T3 ON T2.ID_BUSINESSLINE_OWNER = T3.ID_BUSINESSLINE
WHERE T0.AM_ASSIGNMENT='0' and T0.COMPUTERENVIRONMENT = 'PROD' and T1.CRSTATE = 'In Service' ORDER BY T0.COMPUTERNAME" Server as ServerName OUTPUT OS AS OS, ApplicationName AS ApplicationIt does not work...

I tried creating a lookup under - Splunk DB Connect->Data Lab->Lookups

The query gets stuck at the first step - "Set Reference Search"

index=indexwintimesynclogs|eval offset=Delta|where offset>0.0001 and like(ServerName,"%PRD%") | stats max(offset) as offset, count(offset) as violations by ServerName, TimeSource|sort -offset

Search hangs with message - No fields found, forget to run the search?

Please help me...

[neltonk · Accepted solution]

fixed the query myself...

index=indexwintimesynclogs NOT (ServerName=UAT OR ServerName=DEV)|eval ServerName=replace(ServerName, ".+?(\/)",""), offset=Delta|where (offset>0.0001 OR offset<-0.0001) | stats max(offset) as offset, count(offset) as violations by ServerName, TimeSource|sort -offset
| dbxlookup connection="CMDB" query="SELECT upper([T0].[COMPUTERNAME]) As 'Server',
[T0].AD_DOMAINNAME,
[T0].[COMPUTERENVIRONMENT] As 'Environnement',
[T0].[OSNAME] As 'OS',
[T0].[Id_location] AS 'Location',
[T2].[APPLICATIONNAME] As 'ApplicationName',
[T2].[APPLICATIONCODE] As 'ApplicationCode',
[T2].ID_MANAGEMENTTEAM As 'Management team (Application)'
FROM [Publishing].[dbo].INFRASTRUCTURE_Server AS T0 with (nolock)
JOIN [Publishing].[dbo].GENERAL_Relations AS T1 with (nolock) ON T0.ID_SERVER = T1.ID_PARENT AND T1.CIT_PARENT = 'Server' AND LINKTYPE = 'CST_APP2SRV'
JOIN [Publishing].[dbo].APPLICATION_Application AS T2 with (nolock) ON T2.ID_APPLICATION = T1.ID_CHILD AND T1.CIT_CHILD = 'Application'
LEFT JOIN [Publishing].[dbo].ORGANIZATION_BusinessLine AS T3 with (nolock) ON T2.ID_BUSINESSLINE_OWNER = T3.ID_BUSINESSLINE
WHERE T0.AM_ASSIGNMENT='0' and T0.COMPUTERENVIRONMENT = 'PROD' and T1.CRSTATE = 'In Service'" Server as ServerName OUTPUT OS AS OS, ApplicationName AS Application

[neltonk]

Please assist... I am unable to setup a Lookup using DBConnect? Is DBConnect Lookup the solution for my requirement?

[kuruvilla]

Hi Did you figure this out... I am having very similar requirement, I am new to Splunk. I have been asked to work on MiFID regulatory reporting / auditing using Splunk....

Please help