insert host name to ip addresses

[Alisher-Nabiev]

we are attempting to use an SQLite filter to capture incoming IP addresses in the index and then add a name to each IP address from the database.

the IPs are custom and not related to DNS. we want to take IPs and attach our client's hostnames.
the list in the DB will be updated frequently.

is there any way to achieve that (reverse DNS is not fit for this task)

sqlite {
db => '/home/folder/userdata_qa.db'
query => 'SELECT service from ip_list WHERE ip_number=(?) limit 1;'
source_field => 'ip_v4'
target_field => 'host_name'
}

this is not working well for us.

[lmangani]

Hello @Alisher-Nabiev

this is not working well for us.

Please outline the issue in as much detail as possible. What is working, what is not, what are the expected vs. current results, etc.

[Alisher-Nabiev]

ok, so this is our table:

this is filter config:
sqlite {
db => '/home/monogoto/userdata_qa.db'
query => 'SELECT host_name from ip_list WHERE ip_number = (?) limit 1;'
source_field => l4_src_port
target_field => host_name
}

and we don't see host_name in the index.
log:
[TAILING] Tailing last 15 lines for [0] process (change the value with --lines option)
/root/.pm2/logs/pastash-logs-error.log last 15 lines:
/root/.pm2/logs/pastash-logs-out.log last 15 lines:
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash- | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883

0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883
0|pastash-logs | [Wed, 03 Jul 2024 10:37:51 GMT] INFO test query! SELECT host_name from ip_list WHERE ip_number = (?) limit 1; 8883

[lmangani]

The following test succeeds for me. I created a dummy sqlite db

.open /tmp/test.db

CREATE TABLE IF NOT EXISTS lookup (
	id INTEGER PRIMARY KEY,
   	ip TEXT NOT NULL,
	host TEXT DEFAULT 0
);

INSERT INTO lookup (ip, host) VALUES('127.0.0.1', 'localhost');
INSERT INTO lookup (ip, host) VALUES('127.0.0.2', 'docker');

Then installed the sqlite plugin

npm install -g @pastash/filter_sqlite@1.1.0

And execute the following test recipe:

input {
  stdin{}
}

filter {
  sqlite {
    db => "/tmp/test.db"
    query => "SELECT host from lookup WHERE ip=(?) limit 1;"
    source_field => "message"
    target_field => "host"
  }
}
output {
  stdout{}
}

I get the expected results:

[Wed, 03 Jul 2024 14:19:38 GMT] INFO Config loaded.
127.0.0.1
[Wed, 03 Jul 2024 14:19:40 GMT] INFO 127.0.0.1 127.0.0.1 { host: 'localhost' }
[STDOUT] {
  "source": "stdin",
  "message": "127.0.0.1",
  "host": "localhost",
  "@timestamp": "2024-07-03T14:19:40.785Z",
  "@version": "1",
  "correlation_id": "nothing"
}
127.0.0.2
[Wed, 03 Jul 2024 14:19:43 GMT] INFO 127.0.0.2 127.0.0.2 { host: 'docker' }
[STDOUT] {
  "source": "stdin",
  "message": "127.0.0.2",
  "host": "docker",
  "@timestamp": "2024-07-03T14:19:43.408Z",
  "@version": "1",
  "correlation_id": "nothing"
}

[Alisher-Nabiev]

Hi, thank you for your response.

i tried to create a new database with the same configuration as your example. I just substituted my hosts.
test:
root@ip-:/home/ubuntu# sqlite3 /home/ubuntu/qa.db "SELECT host FROM lookup WHERE ip = '8.8.8.8';"
Google-DNS

but nothing on elastic side no new field in the indecies.

log:
[TAILING] Tailing last 15 lines for [all] processes (change the value with --lines option)
/root/.pm2/pm2.log last 15 lines:
PM2 | 2024-07-03T14:37:18: PM2 log: Stopping app:pastash-logs id:0
PM2 | 2024-07-03T14:37:18: PM2 log: App [pastash-logs:0] exited with code [1] via signal [SIGINT]
PM2 | 2024-07-03T14:37:18: PM2 log: pid=1941659 msg=process killed
PM2 | 2024-07-03T14:37:18: PM2 log: App [pastash-logs:0] starting in -fork mode-
PM2 | 2024-07-03T14:37:18: PM2 log: App [pastash-logs:0] online
PM2 | 2024-07-03T14:43:41: PM2 log: Stopping app:pastash-logs id:0
PM2 | 2024-07-03T14:43:41: PM2 log: App [pastash-logs:0] exited with code [1] via signal [SIGINT]
PM2 | 2024-07-03T14:43:41: PM2 log: pid=1942553 msg=process killed
PM2 | 2024-07-03T14:43:41: PM2 log: App [pastash-logs:0] starting in -fork mode-
PM2 | 2024-07-03T14:43:41: PM2 log: App [pastash-logs:0] online
PM2 | 2024-07-03T14:58:15: PM2 log: Stopping app:pastash-logs id:0
PM2 | 2024-07-03T14:58:15: PM2 log: App [pastash-logs:0] exited with code [1] via signal [SIGINT]
PM2 | 2024-07-03T14:58:15: PM2 log: pid=1947045 msg=process killed
PM2 | 2024-07-03T14:58:15: PM2 log: App [pastash-logs:0] starting in -fork mode-
PM2 | 2024-07-03T14:58:15: PM2 log: App [pastash-logs:0] online

/root/.pm2/logs/pastash-logs-error.log last 15 lines:
/root/.pm2/logs/pastash-logs-out.log last 15 lines:
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Initialized Omit filter!
0|pastash- | Loading npm module... @pastash/filter_sqlite
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Initializing filter sqlite
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Initializing Filter Sqlite3: Database {
0|pastash- | name: '/home/ubuntu/qa.db',
0|pastash- | open: true,
0|pastash- | inTransaction: false,
0|pastash- | readonly: false,
0|pastash- | memory: false
0|pastash- | }
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Initialized Filter Sqlite3
0|pastash- | Loading npm module... @pastash/input_netflowv9
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Initializing input Netflowv9
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Opening Netflowv9 socket on port 30001
0|pastash- | [Wed, 03 Jul 2024 14:58:16 GMT] INFO Config loaded.

[lmangani]

Have you installed the same version?

npm install -g @pastash/filter_sqlite@1.1.0

I do not see any useful output. You might want to add an stdout{} block to see what's happening in the logs.

output {
  stdout{}
}

[Alisher-Nabiev]

hi, yes we install the new version.

i will share with you the stdout{} in privet

[lmangani]

As you can see above my example works and its served with a fully reproducible case and each step used.
Not sure what the issue might be on your system, but since it cannot be reproduced, there's not much we can do to help.

You should rather find out why your process is exiting with errors in loop

PM2 | 2024-07-03T15:04:04: PM2 log: App [pastash-logs:0] exited with code [1] via signal [SIGINT]

[Alisher-Nabiev]

As you can see, the error occurred yesterday, possibly because I restarted the PM2 when making new changes in the configuration file.

Also, we receive data constantly in the Elastic, and we don't observe any unexpected exits from the PM2 process.

[lmangani]

I'm sorry but the issue is on your side and our example appears to be working - you need to investigate your setup, as without more information, I'm afraid there's nothing we can do. Are you even sure the pastash process has permissions to read your .db files?

[Alisher-Nabiev]

yes i gave to the db -rwx permission and still same issue

[lmangani]

Is there any reason why you did not attempt running our provided "demo" example? It might reveal the issue.

[Alisher-Nabiev]

what do you mean by your example?

i created the same DB as you are but only with my IPs.

and i put the same filter on the config file.

if i am running the query on the instance it's retrieving the correct hostname.

but on the elastic side, i can't see it.

If you want me to test it in a different way, please let me know.

[lmangani]

Our example is stand alone. It uses user input string (IP) to validate the pipeline. It has no elastic or other transformations. This is why in case of issues its useful to troubleshoot a single component as opposed to the full configuration. Try it and confirm it works before moving ahead with integration in your workflow.