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

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

sjanwity — Tue, 14 Oct 2014 12:50:09 GMT

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?

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

aweitzman — Tue, 14 Oct 2014 13:21:34 GMT

You should be able to do this. I just wrote a short simple XML dashboard where the searchTemplate started with | dbquery and it worked just fine, no importing into an index necessary. I was able to use an input parameter from the form in the SQL query itself, as well as outside of the dbquery clause, and it all worked as expected.

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

sjanwity — Wed, 15 Oct 2014 12:31:40 GMT

@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

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

aweitzman — Wed, 15 Oct 2014 13:47:31 GMT

The time filter can't work without some manipulation because contents from dbquery don't have a Splunk time associated with them out of the box. If you have a field with the epoch time in it, I believe adding | rename timefield to _time will use that field as the Splunk time, which should then allow for time filtering.

I do not understand what you mean by "Adding a $series$ field...". Can you explain that further, please? For me, both of the following worked, where $formValue$ is the contents of an input box:

| dbquery mydb "SELECT * FROM dbo.mytable where MyField like '%$formValue$%'"

| dbquery mydb "SELECT * FROM dbo.mytable" | search MyField="*$formValue$*"

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

sjanwity — Wed, 15 Oct 2014 15:08:10 GMT

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.

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

aweitzman — Wed, 15 Oct 2014 15:45:04 GMT

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.)

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

aweitzman — Wed, 15 Oct 2014 16:00:53 GMT

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

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

sjanwity — Mon, 28 Sep 2020 17:55:13 GMT

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?

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

sjanwity — Wed, 15 Oct 2014 16:27:59 GMT

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>

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

aweitzman — Wed, 15 Oct 2014 16:34:14 GMT

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

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

sjanwity — Wed, 15 Oct 2014 17:19:06 GMT

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

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

aweitzman — Wed, 15 Oct 2014 17:25:01 GMT

Which problem are you trying to solve?

Search all available fields for the string in the input box
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_to_forms

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

aweitzman — Wed, 15 Oct 2014 17:27:53 GMT

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.

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

sjanwity — Wed, 15 Oct 2014 17:48:50 GMT

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?

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

aweitzman — Wed, 15 Oct 2014 18:00:47 GMT

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.

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

sjanwity — Wed, 15 Oct 2014 18:27:28 GMT

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

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

aweitzman — Wed, 15 Oct 2014 19:02:30 GMT

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

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

sjanwity — Thu, 16 Oct 2014 12:29:38 GMT

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 &lt; UPDATE_TIME AND info_max_time &gt; 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$*"

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

aweitzman — Thu, 16 Oct 2014 13:57:30 GMT

Looking only at the changed one:

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

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

sjanwity — Mon, 28 Sep 2020 17:53:51 GMT

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 😞