Reporting

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

sjanwity
Communicator

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?

0 Karma
1 Solution

aweitzman
Motivator

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.

View solution in original post

sjanwity
Communicator

@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

0 Karma

sjanwity
Communicator

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.

0 Karma

aweitzman
Motivator

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
Communicator

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?

0 Karma

aweitzman
Motivator

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

0 Karma

aweitzman
Motivator

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
Communicator

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

aweitzman
Motivator

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

0 Karma

sjanwity
Communicator

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

0 Karma

aweitzman
Motivator

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.

0 Karma

sjanwity
Communicator

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?

0 Karma

aweitzman
Motivator

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.

0 Karma

sjanwity
Communicator

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

0 Karma

aweitzman
Motivator

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

0 Karma

sjanwity
Communicator

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

aweitzman
Motivator

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 &lt; and &gt; 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.

0 Karma

sjanwity
Communicator

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 😞

0 Karma

aweitzman
Motivator

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?

0 Karma

sjanwity
Communicator

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.

0 Karma

aweitzman
Motivator

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?

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...