Enclosing numerical values in quotes in SELECT WHERE statement

musca999 · November 21, 2017, 9:19pm

Hi,

I use the following template when submitting data to InfluxDB:

templates = [ “ASR.PORT.* .measurement.Nodename.CardNo.PortNo.field” ]

The data I submit is like this:

echo "ASR.PORT.ALPRGAGQPN7.2.1.tx_bytes 400 `date +%s`" | nc localhost 2003

And this is what ends up in the DB:

> select * from "PORT"
name: PORT
time                CardNo Nodename    PortNo tx_bytes
----                ------ --------    ------ --------
1511298123000000000 1      ALPRGAGQPN7 1      200
1511298129000000000 1      ALPRGAGQPN7 2      300
1511298134000000000 2      ALPRGAGQPN7 2      500
1511298180000000000 2      ALPRGAGQPN7 1      400

In Grafana, I want to narrow the graph down to Nodename/CardNo/PortNo. But when I select CardNo as my first filter, Grafana sends:

> SHOW TAG KEYS FROM "PORT" WHERE "CardNo" = 1
>

Because the 1 is not quoted, Grafana doesn’t find anything and therefore will not show anything when I click on the “+” to narrow my criteria. However, if I enclose the 1 in quotes, it finds the other keys:

> SHOW TAG KEYS FROM "PORT" WHERE "CardNo" = '1'
name: PORT
tagKey
------
CardNo
Nodename
PortNo

And therefore I’m able to drill down some more and select my PortNo and Nodename.

I suppose I could prepend all my numerical values with a “:” or something like that (and it does work in that I can now make a selection for all the fields in my WHERE statement), but it’s not pretty.

Any suggestions on how I can get around this behavior?

I’m running Grafana 4.6.1 and InfluxDB 1.4.2.

Thank you

mefraimsson · November 28, 2017, 2:00pm

Hi,

I tried to reproduce it, but failed. I’ve tried to filter hostname=1 at http://play.grafana.org/dashboard/db/influxdb-group-by-time?panelId=1&fullscreen&edit&orgId=1 and it gets properly enclosed. That is Grafana v4.6.2. So I suggest that you upgrade to Grafana v.4.6.2.

Please let me know if that works

Marcus

musca999 · November 29, 2017, 7:56pm

Hi Marcus,

Thanks for your reply. I upgraded to version 4.6.2 as you suggested, but now I’m running into another odd issue with how Grafana queries Influx.

Please note, I no longer use graphite as the backend because I find it’s too slow. Instead I write direct to influx using “curl -i -XPOST ‘http://localhost:8086/write?db=bulkstats’ --data-binary @/tmp/outfile”)

Influx will not allow me to write timestamps in 1511984465672ms format. It only allows 1511984465672. But the Grafana query has the “ms” in the query so it doesn’t find anything.

SELECT “cpu2-memtotal” FROM “CARD” WHERE (“Nodename” = ‘ALPRGAGQPNM’ AND “card” = ‘:2’) AND time >= 1511939866977ms and time <= 1511984465672ms <<<< Nothing found. This is the query sent by Grafana copied from Query Inspector.

SELECT “cpu2-memtotal” FROM “CARD” WHERE (“Nodename” = ‘ALPRGAGQPNM’ AND “card” = ‘:2’) AND time >= 1511939866977 and time <= 1511984465672 <<<< This is a manual query in Influx
name: CARD
time cpu2-memtotal

1511980200000 65536

Is there any way to not include the “ms” when querying Influx?

Thanks,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

musca999 · November 29, 2017, 11:57pm

Hi Marcus,

Reading a bit more on this on the InfluxDB site and it says:

Relative time

You can use now() to calculate a timestamp relative to the server’s current timestamp. For example:

select value from response_times where time > now() - 1h limit 1000;

will return all points starting an hour ago until now.

Other options for how to specify time durations are u for microseconds, s for seconds, m for minutes, h for hours, d for days and w for weeks. If no suffix is given the value is interpreted as microseconds.

So InfluxDB doesn’t support queries with “ms” it looks like, but Grafana appends “ms”

Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: https://mycase.cloudapps.cisco.com/case
Cisco Worldwide Contact: http://www.cisco.com/c/en/us/support/web/tsd-cisco-worldwide-contacts.html

mefraimsson · November 30, 2017, 9:13am

Hi,

If you take a look at this link and check the query inspector you’ll see that the following queries are generated:

SELECT mean("value") FROM "logins.count" WHERE ("hostname" = 'server1') AND time >= now() - 30m GROUP BY time(10m), "hostname"
SELECT mean("value") FROM "logins.count" WHERE time >= now() - 30m GROUP BY time(10m)

So the relative time information you’re referring to should already be implemented in Grafana.

In order to further help you may I ask you to include a screenshot of your metric tab in grafana for a panel that does not work and if possible also a screenshot of your time settings (upper right corner) of your dashboard.

Marcus

musca999 · November 30, 2017, 9:40am

Hi Marcus,

Thanks again for helping out. To make it simple I have 1 datapoint in my DB:

show measurements
name: measurements
name

mefraimsson · November 30, 2017, 12:37pm

Oh right. I tried absolute time range on the play.grafana.org link above and it works, even if ms are added. Now I think I know why your having problems.

Are you running influxdb 0.8? I suppose you read the documentation for 0.8 regarding relative time.

If you read the documenation for influxdb 0.9, it clearly states that it supports ms.

So I would suggest you to upgrade influxdb

Marcus

musca999 · November 30, 2017, 3:44pm

Hi Marcus,

This is what I get:

[admin@httstools]$ influx -version
InfluxDB shell version: 1.4.2

Does that make sense? Or were you referring to some other process version? Do you think this is a influx bug?

Thanks,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: https://mycase.cloudapps.cisco.com/case
Cisco Worldwide Contact: http://www.cisco.com/c/en/us/support/web/tsd-cisco-worldwide-contacts.html

mefraimsson · December 1, 2017, 9:21am

Hmm okay but then your running the latest version of influxdb. Then my thought were wrong. I’ve verified that for me running latest influx and Grafana v4.6.2 works with using an absolute time range and no problems with using “ms” in query.

Just to make sure:

What is the actual result in the Grafana panel when using ms in query - no data points or something else? Would be interesting to see a screenshot of this.
If you remove the ms parts from the query you’ll get a result in Grafana which you think is correct?

Would be really interesting to see the whole result object from the query inspector for both using with and without “ms” to see the actual data coming back, could you provide screenshots of these?

Thanks

Marcus

musca999 · December 1, 2017, 5:29pm

Hi Marcus,

Apologize for the huge screenshot, but I think what you’re looking for is all in there:

[/uploads/grafana/original/2X/8/81d3453ba7d5484cf37aa8b58224b73dc7a1bd25.png]

As you can see the range goes from 11am to 11pm, and my one datapoint is at 6:20pm but it’s not showing on the graph because the “ms” in the query.

If I run the exact same query manually in influx with out the “ms”, I get this:

SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’) AND time >= 1511956025214ms and time <= 1511998153086ms
SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’) AND time >= 1511956025214 and time <= 1511998153086
name: P2P
time p2p-duration-value

1511979600000 0

So you can see the “ms” query fails and the exact same query without the “ms” works.

To answer your second question, yes I believe if Grafana did not include the “ms” then the query would work and the data point would show up on the graph. But I can’t test that because I don’t know how to omit that “ms” through configuration. I tried looking at the source code but i’m not a Go expert and couldn’t really figure out where that “ms” was being added to the query string. Maybe if it was configurable it would help (ie user can choose to either include or omit the “ms”)?

Thank you,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

mefraimsson · December 4, 2017, 8:23am

Hi,

Unfortunately I cannot see the screenshot, only a relative link. Can you include it?

You can actually run those queries in Grafana if you Toggle Edit Mode:

Do you get the same result then as when you run the query direct against influxdb?

Marcus

musca999 · December 4, 2017, 5:56pm

Hi Marcus,

This a cool trick I didn’t know about (ie being able to modify the query). I tried though and it gave me an even stranger result. I’m hoping you can see the screenshot this time. As you can see, it seems to think there is a datapoint but it is outside the time range, which is false because the datapoint is at Nov 29th 18:20:00 UTC:

SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’ AND “p2p-protocol” = ‘lync-file-transfer’) AND time >= 1511915222000 and time <= 1511996400000
name: P2P
time p2p-duration-value

1511979600000 0

Of course if you can’t see the screenshot it’s not going to help much. I also attached it as a file in case case email messes it up. I’m using Apple Mail and it does strange things with images sometimes.

[/uploads/grafana/original/2X/a/a210e34c19567c45cb91ce2f5b7b9e7d8e611c94.png]

Thanks,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

mefraimsson · December 5, 2017, 8:43am

Hi,

I can see the screenshot now

Since you only have one data point I think you need to display the graphs as points instead of lines. Please try that.

Marcus

musca999 · December 5, 2017, 3:41pm

Hi Marcus,

I changed it to Point and still no luck (see attached…one screenshot shows I select Point and the other shows the formula).

Also, the output of the Grafana command when run manually on influx:

use bulkstats
Using database bulkstats
SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’ AND “p2p-protocol” = ‘lync-file-transfer’) AND time >= 1511915587000 and time <= 1511998387000
name: P2P
time p2p-duration-value

1511979600000 0

I’ll try adding a few more datapoints a few seconds apart to see if that helps. Can you think of anything else?

Thank you,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

musca999 · December 5, 2017, 3:53pm

Hi Marcus,

While poking around some more I noticed something odd. When I remove the “ms” from the time range using manual edit, then mouse around the chart area it seems to think it’s 1970 (see attached), even though the selected date range is Nov 29 00-23hrs. That could explain why it thinks the data points are out of range.

What do you think?

Thanks,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco.com Login Page
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

mefraimsson · December 5, 2017, 4:03pm

Yeah that’s probably why you have some problems.

If you use ms and use the timerange to filter between the day of 1970-01-01 - do you get a result?

Marcus

musca999 · December 5, 2017, 5:11pm

Marcus,

I’m not sure what you mean. If I use “ms”, the entire query will fail because influx doesn’t find anything when I use “ms” in a manual query.

I tried specifying 1970 in the time range but didn’t see anything in the graph, which makes sense because the data points in influx are all Nov 29.

But then I added 10 data points starting at 00000000000000 (1970-01-01) and when I specify the same time range (ie 1970-01-01 00:00:00), I see the data on the graph.

Do you think this is a bug?

Thank you,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: https://mycase.cloudapps.cisco.com/case
Cisco Worldwide Contact: http://www.cisco.com/c/en/us/support/web/tsd-cisco-worldwide-contacts.html

mefraimsson · December 6, 2017, 8:34am

Hi,

Sorry for being unclear. I was thinking that it may be related to some timezone issue and that Grafana thinks your data is written at 1970. Was just curious if you could find your datapoint by a regular grafana query (with ms) using a timerange from 1970. Forget this.

Did you write these 10 datapoints in the same manner as the first datapoint?

Feels like we’re going round in circles here. Hard for me to determine if it’s a bug if I cannot reproduce it. I’ll try to reproduce it now by writing the same data as you to my influxdb instance. Can I get some instructions from you?

Can you give me an example content of the @/tmp/outfile file and show me how you wrote the 10 datapoints?

Marcus

musca999 · December 6, 2017, 4:03pm

Hi Marcus,

I got the same feeling as well and it’s probably because I never provided a e2e explanation of how I get where I am, so here it is:

In Influx:

drop database bulkstats
create database bulkstats
exit

Post data to influx

curl -i -XPOST ‘http://localhost:8086/write?db=bulkstats’ --data-binary @/tmp/foo

where /tmp/foo is:

[admin@httstools ~]$ more /tmp/foo
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=10 0000001000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=20 0000002000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=30 0000003000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=40 0000004000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=50 0000005000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=60 0000006000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=70 0000007000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=80 0000008000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=90 0000009000000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=10 1511979610000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=20 1511979620000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=30 1511979630000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=40 1511979640000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=50 1511979650000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=60 1511979660000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=70 1511979670000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=80 1511979680000
P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=90 1511979690000

Verify data is in influx

use bulkstats
Using database bulkstats
select * from “P2P”
name: P2P
time Nodename p2p-duration-name p2p-duration-value p2p-protocol

1000000 ALPRGAGQPND 0 10 lync-file-transfer <<< timestamp starts Jan 1 1970 00:00
2000000 ALPRGAGQPND 0 20 lync-file-transfer
3000000 ALPRGAGQPND 0 30 lync-file-transfer
4000000 ALPRGAGQPND 0 40 lync-file-transfer
5000000 ALPRGAGQPND 0 50 lync-file-transfer
6000000 ALPRGAGQPND 0 60 lync-file-transfer
7000000 ALPRGAGQPND 0 70 lync-file-transfer
8000000 ALPRGAGQPND 0 80 lync-file-transfer
9000000 ALPRGAGQPND 0 90 lync-file-transfer
1511979610000 ALPRGAGQPND 0 10 lync-file-transfer <<< timestamp starts Nov 29 2017 6:20pm
1511979620000 ALPRGAGQPND 0 20 lync-file-transfer
1511979630000 ALPRGAGQPND 0 30 lync-file-transfer
1511979640000 ALPRGAGQPND 0 40 lync-file-transfer
1511979650000 ALPRGAGQPND 0 50 lync-file-transfer
1511979660000 ALPRGAGQPND 0 60 lync-file-transfer
1511979670000 ALPRGAGQPND 0 70 lync-file-transfer
1511979680000 ALPRGAGQPND 0 80 lync-file-transfer
1511979690000 ALPRGAGQPND 0 90 lync-file-transfer

Now go to Grafana and look for the data points (see slide 1 for output). Result “No data points”.
Run query manually from Query Inspector (nothing is returned)

SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’ AND “p2p-protocol” = ‘lync-file-transfer’) AND time >= 1511917026000ms and time <= 1511999826000ms

Run same query but removing “ms” from timestamps

SELECT “p2p-duration-value” FROM “P2P” WHERE (“Nodename” = ‘ALPRGAGQPND’ AND “p2p-protocol” = ‘lync-file-transfer’) AND time >= 1511917026000 and time <= 1511999826000
name: P2P
time p2p-duration-value

1511979610000 10
1511979620000 20
1511979630000 30
1511979640000 40
1511979650000 50
1511979660000 60
1511979670000 70
1511979680000 80
1511979690000 90

Influx version

[admin@httstools ~]$ influx -version
InfluxDB shell version: 1.4.2

Grafana version

[admin@httstools ~]$ grafana-server -v
Version 4.6.2 (commit: 8db5f08)

I’m hoping that will be enough for you to reproduce. If with all that you still can’t reproduce then maybe there is something wrong with my installation?

Thank you,
Dan Musca
Cisco High Touch Technical Support
Phone: 469-255-3768
Cell: 972-768-0937
Email/Jabber: dmusca@cisco.com mailto:dmusca@cisco.com
HTTS Hotline: 800-495-9121
To update your case: Cisco
Cisco Worldwide Contact: Cisco Worldwide Support Contacts - Cisco

mefraimsson · December 7, 2017, 9:25am

Hi,

Now I’ve had the time to try your example out and I finally think I know why your having problems.

I first created a data.txt file with the following content

P2P,Nodename=ALPRGAGQPND,p2p-duration-name=0,p2p-protocol=lync-file-transfer p2p-duration-value=90 1511979690000

Inserted data and verified the written data with the following commands

curl -i -XPOST 'http://localhost:8086/write?db=bulkstats' --data-binary @data.txt
curl -g http://localhost:8086/query?db=bulkstats --data-urlencode "q=select * from P2P"

Got the following result:

{
	"results": [
		{
			"statement_id": 0,
			"series": [
				{
					"name": "P2P",
					"columns": [
						"time",
						"Nodename",
						"p2p-duration-name",
						"p2p-duration-value",
						"p2p-protocol"
					],
					"values": [
						[
							"1970-01-01T00:25:11.97969Z",
							"ALPRGAGQPND",
							"0",
							90,
							"lync-file-transfer"
						]
					]
				}
			]
		}
	]
}

As you can see the written timestamp is 1970-01-01 even though I used a timestamp value of 1511979690000 (2017-11-29). This made me read up a bit on the influxdb line prototcol and the timestamp field.

So if you don’t specify a precision of your timestamps influxdb will by default use nanosecond-precision Unix time. Looking on your timestamps they’re in millisecond-precision Unix time.

When I changed my insert command to include the millisecond precision the correct timestamp was interpreted by influxdb:

curl -i -XPOST 'http://localhost:8086/write?db=bulkstats&precision=ms' --data-binary @data.txt

And I can see the value written in Grafana:

Please try this out and let me know if that solves your problems

Marcus

Topic		Replies	Views
10-minute difference between timestamps in InfluxDB and Grafana InfluxDB influxdb	11	3447	August 31, 2022
Grafana builds bad InfluxDB query InfluxDB	1	486	February 7, 2019
How to add Time series queries with grafana and MySQL? Time Series Panel mysql	62	153914	July 7, 2023
Jmeter, InfluxDB, Grafana andJMeter Load Testby NovaTec-APM InfluxDB	3	1585	February 5, 2018
Table Panel: Filter and show data Table Panel influxdb , table-panel	19	19428	March 3, 2022

Enclosing numerical values in quotes in SELECT WHERE statement

Related topics