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...
... View more