Splunk DB Connect - filter results featuring a start timestamp and a end timestamp

[dominiquevocat]

Hi, i have read http://answers.splunk.com/answers/75999/splunk-db-connect-dbquery-inline-search-and-time-filtering-n... and while it covers part i would like to take advantage of the many smart people here :-).

I have a DB Backend where i call canned reports (stored procedures) and i would like to efficiently filter the results that are within the search timerange picker's range. Each record has two timestamps an insertdate and a deletedate. The amount of data in a report is not really very very big so i think we can live with this limitation of the dbx connector.

I think the most useful way to go about this would probably be a macro that covers the calling of the backend with a parameter for the stored procedure and one for the search parameter from the form representing the GUI for the report which filters down and nicely represents the timestamp as dates.

Example:
search is something like
| dbquery "reportingplatform" "EXEC splunk.getUsersOfOU @OUName = '$ou$'" limit=1000

Output is something like:
OU,UserName,UserID,UserIsManager,UserManagerDefAlias,UserDisableDate,UserCostCenter,IDate,DDate

This is fine but i would like to limit events to the timerange control's time ( addinfo ) - issue is that there may not be a IDate or DDate since it might be currently valid.

Recreating the necessary search each time is prone to error and makes it unnecessarily complex. Pretifying the fieldnames is a bonus but easy enough makes keeping the same drilldown block easier though.

Anyone got a good way of implementing this? Might not be a macro but i guess it is the most straightforward way to achieve it? Goal is that the dataowner can easily build new reports together with DB guy.

[jcoates_splunk]

Hi, I would think that a macro makes the most sense, so that you can eval out a value no matter what. psuedocode:

1. SPL: calculate the time range you want into date fields as sowings and ziegfried show
2. SQL: add a "where date containing columns are between these dates or NULL"

[jcoates_splunk]

nope, my bad -- shouldn't post before trying things out. My suggestion won't work.

[dominiquevocat]

wait - i was under the impression that there is no way to pass the timerange from the search into the sql query since the dbx extension is not a streaming one and i only get the fields with addinfo? I would would be way cool if i could use the dbx extension as a eventgenerating input... did i miss something new???