Is it possible to create a form to better present splunk data coming from another app without the use of an index?

[sjanwity]

I have some data which is coming from the Splunk DB Connect app, which I need to present onto a report. I want this report to have filtering functionality, thus necessitating the use of forms. I'm following the Splunk tutorial on how to implement a basic keyword filter on a report through a simple form, but I found that I need to use an index as a intermediary input data store.

I currently can not use any index due to some other issues which in all fairness will be solved soon, so this question is academic only: is it possible to use forms this way without storing the data in an index?

[sjanwity]

@aweitzman It works but the searches and time filter doesn't work. Adding a $series$ field after the dbquery doesn't seem to do anything to my search results

[sjanwity]

Ah I get it, I was following the example on http://docs.splunk.com/Documentation/Splunk/latest/Viz/Buildandeditforms and thought that you would be following the same keyword - they use $series$ to search. How would I make it search for any column on the table, though, without having to modify 'MyField'?

I also have a _time field but the time range still isn't working. My code is from the splunk help page, by the way.

[aweitzman]

As for searching any field after a dbquery instead of just one, I don't think you can do that without listing each field, like (fieldA="$series$" OR fieldB="$series$" OR ...).

I'd recommend creating a macro for that if you have to do it in more than one place. See this page for more details: http://docs.splunk.com/Documentation/Splunk/latest/Search/Usesearchmacros

[sjanwity]

I have an idea - I would just tell the user to, when searching for fieldB, just include "B_" in front of his search string. This doesn't seem to work though - is it because '_' is a special character? How can I escape it?

[aweitzman]

Which problem are you trying to solve?

1. Search all available fields for the string in the input box
2. Allow the user to provide a field name to search as well as a string

If you're trying to solve 1, you'll have to list all the fields in the search as above. If you're trying to solve 2, you can provide a dropdown that allows the user of the dashboard to choose the field they're searching, and then use that in your search string. Look at this example for how you might do that: http://docs.splunk.com/Documentation/Splunk/latest/Viz/Buildandeditforms#Static_and_dynamic_inputs_t...

[aweitzman]

Another way to deal with the time is to use a Splunk command called addinfo. This will add fields related to the time of the search being done, info_min_time and info_max_time, so you can compare against a field you already have. For instance, if the field you get from the db containing the epoch time is TimeStamp, then the following should work:

| dbquery mydb "SELECT * FROM dbo.mytable where MyField like '%$formValue$%'" | addinfo | convert auto(TimeStamp) | where (info_min_time (less-than) TimeStamp AND info_max_time (greater-than) TimeStamp)

(Replace less-than and greater-than with the actual symbols.)

[sjanwity]

I don't quite understand what use addinfo would do? I've translated my timestamp field taken from dbqueryinto splunk time by using the eval _time=UPDATE_TIME command, but the time picker doesn't work. The current code I have in xml is

<input type="time" searchWhenChanged="true">
      <default>
        <earliestTime>@d</earliestTime>
        <latestTime>now</latestTime>
      </default>
</input>

[aweitzman]

What I mean is, leave out the eval _time=UPDATE_TIME clause in your search and replace it with the addinfo suggestion instead.

[sjanwity]

but how does this link to the time picker I have on my form?

[aweitzman]

By definition, the time picker identifies the time boundaries of your search. There's no other "link" than that. The time boundaries are metadata values with respect to the search, so they don't show up as usable values by default. The addinfo command makes those metadata values available to you so you can work with them.

[sjanwity]

Sorry, this time picker is on a form, not a search - I made it myself using the code snippet above. Does this change your comment in any way?

[aweitzman]

The only context in which a time picker makes sense is on a form.

By choosing a time range with your time picker, you define the "time boundaries" I described earlier. By using addinfo you get access to the metadata values that represent those time boundaries. By comparing the value of your UPDATE_TIME field from your search to the info_min_time and info_max_time values generated by the addinfo command, you can filter your results by the range in your time picker.

Exactly what information are you looking for that isn't in this answer? I'm having trouble divining what you're really asking.

[sjanwity]

I just have trouble understanding how your solution works and thought you were misunderstanding. Anyhow, the solution is showing no results...

[aweitzman]

At this point, I'd need to see the complete search string you're using that isn't working.

[sjanwity]

original without your change:

  | dbquery "DB" limit=100000 "select * from generic_data_hist" series=$series$.
      | eval _time = UPDATE_TIME
      | transaction OBJECT_KEY FIELD_NAME keeporphans=true maxspan=1s maxevents=2
      | eval OPERATION_RESULT=if(OPERATION=="INSERT" AND OPERATION=="DELETE","UPDATE",OPERATION)
      | sort TYPE_NAME, OBJECT_KEY, FIELD_NAME, 
      | table _time, TXN_ID, OPERATION, OPERATION_RESULT, VERSION, TYPE_NAME, OBJECT_KEY, FIELD_NAME, FIELD_VALUE
      | search OBJECT_KEY = "*$series$*"

with your changes:

| dbquery "" limit=100000 "select * from generic_data_hist" series=$series$.
  | addinfo
  | convert auto(UPDATE_TIME)
  | where (info_min_time < UPDATE_TIME AND info_max_time > UPDATE_TIME)
  | transaction OBJECT_KEY FIELD_NAME keeporphans=true maxspan=1s maxevents=2
  | eval OPERATION_RESULT=if(OPERATION=="INSERT" AND OPERATION=="DELETE","UPDATE",OPERATION)
  | sort TYPE_NAME, OBJECT_KEY, FIELD_NAME, 
  | table _time, TXN_ID, OPERATION, OPERATION_RESULT, VERSION, TYPE_NAME, OBJECT_KEY, FIELD_NAME, FIELD_VALUE
  | search OBJECT_KEY = "*$series$*"

[aweitzman]

Looking only at the changed one:

1. In the initial clause, series=$series$. is not helping. Either leave it out (because you search for it later in the last clause) or include it in your SQL statement somewhere.
2. In your final table, you should replace _time with UPDATE_TIME.

(Also, I'm assuming < and > are actual less-than and greater-than symbols.)

Incidentally, are you doing this in a dashboard or at the search bar? If you're doing it in a dashboard, try doing it at the search bar first (replacing $series$ with a useful value) and see what you get.

[sjanwity]

I'm doing this on the dashboard, and series=$series$. is for the search filter somewhere else. Yes those are less-than and greater-than symbols because somehow splunk xml view doesn't render them.

Replacing _time with UPDATE_TIME still gives no results found 😞

[aweitzman]

I don't understand what "for the search filter somewhere else" means. It does not belong as part of the dbquery command. It needs to be removed.

Did you try my suggestion of running the query at the search bar?

[sjanwity]

It's for the search box on my dashboard. There are results when I include series in my original query anyway, but not with yours.

Ofcourse I tried it in search! No results.

[aweitzman]

Please humor me and take it out of the dbquery command. I don't see how it can possibly be helpful.

(Just out of curiosity, why is there a period after the variable name?)

Anyways, after taking that chunk out of your dbquery command, please run just the first three clauses of the second search (through the convert clause) from the search bar using the "All time" time picker value. Please confirm for me that you get a table that includes the UPDATE_TIME, info_max_time and info_min_time columns.

Now add your where clause. Did you get the same result?

Now add your transaction and eval clauses. Did you get what you expected?