Splunk Search

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

dominiquevocat
Motivator

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
Splunk Employee
Splunk Employee

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"
0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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

0 Karma

dominiquevocat
Motivator

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???

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...