Passing Search result into db connect subsearch query fails

[samhodgson]

Hi,

I am pulling a projectID value from kvstore and trying to pass it as a value in a sql query however I can see on the sql server that the value is not being populated, instead it passes ProjectId = ProjectId:

| inputlookup topas1_lookup | fields ProjectId | join ProjectId [dbxquery query="exec sp_executesql N'/* GetSelectSql(vwProjectAnalysisWithForecastAllTimeAndApprovedTime) */ SELECT [dbo].[vwProjectAnalysisWithForecastAllTimeAndApprovedTime].[ProjectName] as Project, [dbo].[vwProjectAnalysisWithForecastAllTimeAndApprovedTime].[Worked] as Worked, [dbo].[vwProjectAnalysisWithForecastAllTimeAndApprovedTime].[Budget] as Budget FROM [dbo].[vwProjectAnalysisWithForecastAllTimeAndApprovedTime] WHERE [vwProjectAnalysisWithForecastAllTimeAndApprovedTime].[ProjectId] = ProjectId" connection="topas"]

The field in the sql db is ProjectId exactly and I have verified that the ProjectId value is correctly set after the kvstore lookup.

Any help is greatly appreciated!!

[DalJeanis]

Unlike SQL, the join command in splunk does not pass any values from one side to the other before the result sets are determined. so, your sql query has to be standalone. The are a lot of different ways to achieve this kind of analysis (join, map, lookup, etc) but if my assumptions are accurate, a join can work for this use case.

Since these are project summary statistics, they can't be very large. I would expect the efficient method would be to invert the sides of the join as follows. This is air code, and I've aliased the table because using the full database and tablename for every field is verbose and ugly, and I've prettified the code, so you'll have to kill whitespace and adjust anything my fingers mucked up...

[dbxquery  query="exec sp_executesql 
     N'/*  GetSelectSql(vwProjectAnalysisWithForecastAllTimeAndApprovedTime) */ 
      SELECT vPAF.ProjectId as ProjectId, 
             vPAF.ProjectName as Project,
             vPAF.Worked as Worked, 
             vPAF.Budget as Budget 
      FROM dbo.vwProjectAnalysisWithForecastAllTimeAndApprovedTime vPAF"   
     connection="topas"
]
| join ProjectId
    [ | inputlookup topas1_lookup | table ProjectId]

[samhodgson]

Many thanks Dal this helps me out a lot 🙂

[samhodgson]

I have just got round to testing this (license expired) and still not having any joy unfortunately.

The input_lookup query definitely returns a project id however it is not being passed into the db query, I have simplified to a very basic query and still seeing the string ProjectId instead of the value in the Splunk db logs and also on the sql server query profiler. Have tried swapping the search's round to no avail.

Any advice on how to troubleshoot this would be much appreciated.