how to caculate the ip address life time?
Now I mod the crontab's shell,it run every 10minutes, only ip changed,it write into influxdb,database looks like:
Can I caculate the none NULL data's ELAPSED time ?
> select * from "exec_wanip"
name: exec_wanip
time host value
---- ---- -----
1542951090000000000 monitor
1542951140000000000 monitor
1542951150000000000 monitor 118.114.187.199
1542951160000000000 monitor
1542951170000000000 monitor
1542951180000000000 monitor
1542951190000000000 monitor
1542951200000000000 monitor
1542951210000000000 monitor
1542951810000000000 monitor
1542952410000000000 monitor
1542953010000000000 monitor
I record WAN ip via crontab,here is the data in influxdb,and ip address is variable.
how could I caculate the life time of each ip address ?
> select * from exec_wanip
name: exec_wanip
time host value
---- ---- -----
1542856250000000000 monitor 118.114.187.208
1542856850000000000 monitor 118.114.187.208
1542857450000000000 monitor 118.114.187.208
1542858050000000000 monitor 118.114.187.208
1542858650000000000 monitor 118.114.187.208
1542859250000000000 monitor 118.114.187.208
1542859850000000000 monitor 118.114.187.208
1542860450000000000 monitor 118.114.187.208
1542861050000000000 monitor 118.114.187.208
1542861650000000000 monitor 118.114.187.208
1542862250000000000 monitor 118.114.187.208
1542862850000000000 monitor 118.114.187.208
1542863450000000000 monitor 118.114.187.208
1542864050000000000 monitor 118.114.187.208
1542864650000000000 monitor 118.114.187.208
1542865250000000000 monitor 118.114.187.208
influxdb
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
add a comment |
Now I mod the crontab's shell,it run every 10minutes, only ip changed,it write into influxdb,database looks like:
Can I caculate the none NULL data's ELAPSED time ?
> select * from "exec_wanip"
name: exec_wanip
time host value
---- ---- -----
1542951090000000000 monitor
1542951140000000000 monitor
1542951150000000000 monitor 118.114.187.199
1542951160000000000 monitor
1542951170000000000 monitor
1542951180000000000 monitor
1542951190000000000 monitor
1542951200000000000 monitor
1542951210000000000 monitor
1542951810000000000 monitor
1542952410000000000 monitor
1542953010000000000 monitor
I record WAN ip via crontab,here is the data in influxdb,and ip address is variable.
how could I caculate the life time of each ip address ?
> select * from exec_wanip
name: exec_wanip
time host value
---- ---- -----
1542856250000000000 monitor 118.114.187.208
1542856850000000000 monitor 118.114.187.208
1542857450000000000 monitor 118.114.187.208
1542858050000000000 monitor 118.114.187.208
1542858650000000000 monitor 118.114.187.208
1542859250000000000 monitor 118.114.187.208
1542859850000000000 monitor 118.114.187.208
1542860450000000000 monitor 118.114.187.208
1542861050000000000 monitor 118.114.187.208
1542861650000000000 monitor 118.114.187.208
1542862250000000000 monitor 118.114.187.208
1542862850000000000 monitor 118.114.187.208
1542863450000000000 monitor 118.114.187.208
1542864050000000000 monitor 118.114.187.208
1542864650000000000 monitor 118.114.187.208
1542865250000000000 monitor 118.114.187.208
influxdb
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like thisselect count(value) from exec_wanip group by ipshould give you number of occurrences per ip. Multiply by cron period to get life time.
– Yuri Lachin
Nov 22 '18 at 17:05
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19
add a comment |
Now I mod the crontab's shell,it run every 10minutes, only ip changed,it write into influxdb,database looks like:
Can I caculate the none NULL data's ELAPSED time ?
> select * from "exec_wanip"
name: exec_wanip
time host value
---- ---- -----
1542951090000000000 monitor
1542951140000000000 monitor
1542951150000000000 monitor 118.114.187.199
1542951160000000000 monitor
1542951170000000000 monitor
1542951180000000000 monitor
1542951190000000000 monitor
1542951200000000000 monitor
1542951210000000000 monitor
1542951810000000000 monitor
1542952410000000000 monitor
1542953010000000000 monitor
I record WAN ip via crontab,here is the data in influxdb,and ip address is variable.
how could I caculate the life time of each ip address ?
> select * from exec_wanip
name: exec_wanip
time host value
---- ---- -----
1542856250000000000 monitor 118.114.187.208
1542856850000000000 monitor 118.114.187.208
1542857450000000000 monitor 118.114.187.208
1542858050000000000 monitor 118.114.187.208
1542858650000000000 monitor 118.114.187.208
1542859250000000000 monitor 118.114.187.208
1542859850000000000 monitor 118.114.187.208
1542860450000000000 monitor 118.114.187.208
1542861050000000000 monitor 118.114.187.208
1542861650000000000 monitor 118.114.187.208
1542862250000000000 monitor 118.114.187.208
1542862850000000000 monitor 118.114.187.208
1542863450000000000 monitor 118.114.187.208
1542864050000000000 monitor 118.114.187.208
1542864650000000000 monitor 118.114.187.208
1542865250000000000 monitor 118.114.187.208
influxdb
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
Now I mod the crontab's shell,it run every 10minutes, only ip changed,it write into influxdb,database looks like:
Can I caculate the none NULL data's ELAPSED time ?
> select * from "exec_wanip"
name: exec_wanip
time host value
---- ---- -----
1542951090000000000 monitor
1542951140000000000 monitor
1542951150000000000 monitor 118.114.187.199
1542951160000000000 monitor
1542951170000000000 monitor
1542951180000000000 monitor
1542951190000000000 monitor
1542951200000000000 monitor
1542951210000000000 monitor
1542951810000000000 monitor
1542952410000000000 monitor
1542953010000000000 monitor
I record WAN ip via crontab,here is the data in influxdb,and ip address is variable.
how could I caculate the life time of each ip address ?
> select * from exec_wanip
name: exec_wanip
time host value
---- ---- -----
1542856250000000000 monitor 118.114.187.208
1542856850000000000 monitor 118.114.187.208
1542857450000000000 monitor 118.114.187.208
1542858050000000000 monitor 118.114.187.208
1542858650000000000 monitor 118.114.187.208
1542859250000000000 monitor 118.114.187.208
1542859850000000000 monitor 118.114.187.208
1542860450000000000 monitor 118.114.187.208
1542861050000000000 monitor 118.114.187.208
1542861650000000000 monitor 118.114.187.208
1542862250000000000 monitor 118.114.187.208
1542862850000000000 monitor 118.114.187.208
1542863450000000000 monitor 118.114.187.208
1542864050000000000 monitor 118.114.187.208
1542864650000000000 monitor 118.114.187.208
1542865250000000000 monitor 118.114.187.208
influxdb
influxdb
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
edited Nov 23 '18 at 6:18
mrco
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
asked Nov 22 '18 at 6:00
mrcomrco
1,252156
1,252156
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like thisselect count(value) from exec_wanip group by ipshould give you number of occurrences per ip. Multiply by cron period to get life time.
– Yuri Lachin
Nov 22 '18 at 17:05
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19
add a comment |
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like thisselect count(value) from exec_wanip group by ipshould give you number of occurrences per ip. Multiply by cron period to get life time.
– Yuri Lachin
Nov 22 '18 at 17:05
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like this
select count(value) from exec_wanip group by ip should give you number of occurrences per ip. Multiply by cron period to get life time.– Yuri Lachin
Nov 22 '18 at 17:05
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like this
select count(value) from exec_wanip group by ip should give you number of occurrences per ip. Multiply by cron period to get life time.– Yuri Lachin
Nov 22 '18 at 17:05
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19
add a comment |
1 Answer
1
active
oldest
votes
Your modified script is still not setting ip as tag.
And my guess is that you actually store empty strings as value - not NULLs.
The query for this data:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip WHERE value<>''
Using filtering on values in WHERE clause is not good for InfluxDB performance in general. I would recommend to change the cron script to put data with line protocol lines like these into your db:
exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208
exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199
...
using curl POST or cli INSERT equivalent.
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208'
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199'
After this is done you'll be able to calculate ip life time with query like this:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip GROUP BY ip
Add WHERE clause to query with time range selection as needed.
Note: anything can be used as value here, not necessarily ip address, for example 'up'/'down', etc.
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424720%2fhow-to-caculate-the-ip-address-life-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your modified script is still not setting ip as tag.
And my guess is that you actually store empty strings as value - not NULLs.
The query for this data:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip WHERE value<>''
Using filtering on values in WHERE clause is not good for InfluxDB performance in general. I would recommend to change the cron script to put data with line protocol lines like these into your db:
exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208
exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199
...
using curl POST or cli INSERT equivalent.
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208'
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199'
After this is done you'll be able to calculate ip life time with query like this:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip GROUP BY ip
Add WHERE clause to query with time range selection as needed.
Note: anything can be used as value here, not necessarily ip address, for example 'up'/'down', etc.
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
add a comment |
Your modified script is still not setting ip as tag.
And my guess is that you actually store empty strings as value - not NULLs.
The query for this data:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip WHERE value<>''
Using filtering on values in WHERE clause is not good for InfluxDB performance in general. I would recommend to change the cron script to put data with line protocol lines like these into your db:
exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208
exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199
...
using curl POST or cli INSERT equivalent.
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208'
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199'
After this is done you'll be able to calculate ip life time with query like this:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip GROUP BY ip
Add WHERE clause to query with time range selection as needed.
Note: anything can be used as value here, not necessarily ip address, for example 'up'/'down', etc.
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
add a comment |
Your modified script is still not setting ip as tag.
And my guess is that you actually store empty strings as value - not NULLs.
The query for this data:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip WHERE value<>''
Using filtering on values in WHERE clause is not good for InfluxDB performance in general. I would recommend to change the cron script to put data with line protocol lines like these into your db:
exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208
exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199
...
using curl POST or cli INSERT equivalent.
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208'
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199'
After this is done you'll be able to calculate ip life time with query like this:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip GROUP BY ip
Add WHERE clause to query with time range selection as needed.
Note: anything can be used as value here, not necessarily ip address, for example 'up'/'down', etc.
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
Your modified script is still not setting ip as tag.
And my guess is that you actually store empty strings as value - not NULLs.
The query for this data:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip WHERE value<>''
Using filtering on values in WHERE clause is not good for InfluxDB performance in general. I would recommend to change the cron script to put data with line protocol lines like these into your db:
exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208
exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199
...
using curl POST or cli INSERT equivalent.
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.208 value=118.114.187.208'
curl -XPOST "http://localhost:8086/write?db=mydb" --data-binary 'exec_wanip,host=monitor,ip=118.114.187.199 value=118.114.187.199'
After this is done you'll be able to calculate ip life time with query like this:
SELECT 600*count(value) AS Lifetime_seconds FROM exec_wanip GROUP BY ip
Add WHERE clause to query with time range selection as needed.
Note: anything can be used as value here, not necessarily ip address, for example 'up'/'down', etc.
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
answered Nov 23 '18 at 8:36
Yuri LachinYuri Lachin
73226
73226
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
add a comment |
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
Thank you so much!! you give me great inspire..
– mrco
Nov 23 '18 at 15:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424720%2fhow-to-caculate-the-ip-address-life-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
That's hardly possible with your current schema. If you use 'ip' as tag and cronjob is periodic than query like this
select count(value) from exec_wanip group by ipshould give you number of occurrences per ip. Multiply by cron period to get life time.– Yuri Lachin
Nov 22 '18 at 17:05
Hi,I mod my shell script,could you please help me ?
– mrco
Nov 23 '18 at 6:19