topic Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax? in Getting Data In

Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

agoktas — Sat, 14 Oct 2017 01:13:22 GMT

Hello,

We have been importing a particular CSV daily into a single index, so the data is nice and clean.

We want to perform 1 search and chart out results.

Fields are: Volume, Change, Price
Volume needs to be greater than 1
Change needs to be greater than 1
Price needs to be greater than 0.001
These 3 fields will determine results.

We want to then output a table that has the following columns:

Symbol
Volume
Change
Price

We want to then have the flexibility to sort the table results by one of the 3 fields (volume, change, price) listed above in ascending or descending order.

Does the sort need to be included in the search syntax, or can we simply use the Splunk UI to click the column to sort? (So far, I don't see this as an option, but I could be doing something wrong).

HERE'S THE KICKER...
The "volume" field must have been 0 at some point in time (remember, we are ingesting results daily), and must have changed to greater than 1 (as per above requirement).

Thanks in advance!

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

pradeepkumarg — Sat, 14 Oct 2017 02:02:00 GMT

Where is the field symbol coming from? Can you post some sample data and the desired format how you want the output to be ?

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

gjanders — Sat, 14 Oct 2017 02:08:15 GMT

So based on the first part of your question I would have said something like:

Volume>1 Change>1 Price>0.001 | table Symbol, Volume, Change, Price

However you also said the Volume was zero at some point, does that mean you want Volume>1 OR Volume=0 ?

Volume>1 OR Volume=0 Change>1 Price>0.001 | table Symbol, Volume, Change, Price

To sort you can add in a sort command, sort - Volume, or sort Volume for example. If you are using a dashboard then you can substitute a variable into the sort command...

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

elliotproebstel — Sat, 14 Oct 2017 02:22:10 GMT

You didn't specify that the input CSV contained the field Symbol, but my answer will assume that was an oversight, as I'm not sure where else it would have come from. Based on your comments below, I think I understand now that your requirement for the Volume field is this: at some point, it was 0, and at some other point (not necessarily after) it was greater than 1.

Assuming that's true, here is a search that should address your needs:
Change>1 AND Price>0.001 | eval has_been_zero_volume=if(Volume=0, 1, NULL) | eventstats values(has_been_zero_volume) AS has_been_zero_volume BY Symbol | where isnotnull(has_been_zero_volume) AND AND Volume>1 | table Symbol Volume Change Price

The resulting table should have four fields: Symbol, Volume, Change, and Price. If you click on any one of those field headers, you should be able to sort the results in ascending or descending order.

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

agoktas — Sat, 14 Oct 2017 02:31:37 GMT

Hi garethatiag,

Thanks for your reply.

Yes, we will need the dependency to where the value must have been 0.00000 a some point in time - within the results, and has at least 1 day to where it has changed to above zero (i.e.: 1.12345 or 0.12345). Otherwise, results should not display.

So if volume for a particular Symbol was...
day 1 = 0.00000
day 2 = 0.00000
day 3 = 0.00000
day 4 = 1.12345
(csv was loaded 4 times in 4 days).

this WOULD qualify as a successful result.

Side note: Does Splunk handle a certain decimal value by default? If not, do we need to specify this to support x number of decimal places to allow values that are as low as 0.00001. I just don't want an automatic round up to decimal.

Thanks.

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

gjanders — Sat, 14 Oct 2017 02:51:49 GMT

Ok, so if you test appending one of my mentioned searches onto your existing CSV lookup it might work...if not someone else has provided an alternative answer as well.

Since Splunk will be treating this as raw data I could not find a mention of any precision limitations, I did find the eval command uses double precision floating point numbers when doing calculations.

Feel free to like if these posts were helpful 🙂

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

elliotproebstel — Sat, 14 Oct 2017 02:59:04 GMT

Looking over this again, I think there are a few issues. First, if you are loading a CSV each day that has only four columns ( Symbol, Volume, Change, and Price), then we have no _time field (or anything equivalent). Thus, I'm not sure we can be certain to return results where the Volume has been 0 in the past and have subsequently moved above 0 - at least not without some guarantee that either the newer results are above the older results, or vice versa.

Furthermore, I am not sure what you want the output to be. I assume the first column will be Symbol, but then you have potentially many values for Volume, Change, and Price. Do you want the max of each, the average of each, the most recent value for each, etc.?

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

agoktas — Sat, 14 Oct 2017 04:10:02 GMT

The time stamp on when the csv was loaded is sufficient. Can we simply use the timestamp of when the entries were loaded.

The goal is to have results that match the following criteria:

1) Match all 3 value requirements (listed above)
2) At least one occurrence of "Volume" was equal to zero (0.0000) within the search time range (all time or 1 week for example).

Hopefully this helps clarify.

Thanks!

Re: Can we use the Splunk UI to sort our data by each column, or does that need to be in the search syntax?

elliotproebstel — Sat, 14 Oct 2017 22:28:30 GMT

I updated my answer, so I think it now meets all of your requirements. Give it a try!