Knowledge Base

Ask A Question

Questions

2

sql query question

Im trying to make a report using SQL queries and want to basically combine the below two queries. The top one gives me - host, IP, OS, number of Crit, major and minor vulnerabilities. I want to add in some Tags to the list.. which the below query gives me but unable to figure out how to combine them. Any info or help would be great. Thanks. SELECT da.asset_id AS "Asset ID", da.ip_address AS "IP Address", da.host_name AS "Host Name", dos.description AS "Operating System",fa.critical_vulnerabilities AS "Critical Vulnerabilities", fa.severe_vulnerabilities AS "Severe Vulnerabilities", fa.moderate_vulnerabilities AS "Moderate Vulnerabilities", fa.vulnerabilities AS "Total Vulnerabilities", to_char(round(fa.riskscore::numeric,0),'999G999G999') AS "Risk Score" FROM dim_asset da JOIN fact_asset fa USING (asset_id) JOIN dim_operating_system dos USING (operating_system_id) ORDER BY da.host_name ---- WITH custom_tags AS ( SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags FROM dim_tag JOIN dim_tag_asset USING (tag_id) WHERE tag_type = 'CUSTOM' GROUP BY asset_id ), location_tags AS ( SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS location_tags FROM dim_tag JOIN dim_tag_asset USING (tag_id) WHERE tag_type = 'LOCATION' GROUP BY asset_id ), owner_tags AS ( SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags FROM dim_tag JOIN dim_tag_asset USING (tag_id) WHERE tag_type = 'OWNER' GROUP BY asset_id ) SELECT ip_address, host_name, sites, dos.description AS operating_system, ct.custom_tags, lt.location_tags, ot.owner_tags FROM dim_asset JOIN dim_operating_system dos USING (operating_system_id) LEFT OUTER JOIN custom_tags ct USING (asset_id) LEFT OUTER JOIN location_tags lt USING (asset_id) LEFT OUTER JOIN owner_tags ot USING (asset_id)

Posted by Christian StPierre 11 months ago

4

Agent communication issue

Started deployment of the agents, but only very few started reporting...posting log for ref: 2017-12-11 08:57:00,202 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Probe response received: timed out 2017-12-11 08:57:00,202 [WARNING] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Have no previous good connections, will not reconnect 2017-12-11 08:57:00,452 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:04,646 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:04,646 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:57:05,462 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:10,471 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:15,481 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:15,670 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:15,670 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:57:20,490 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:25,497 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:26,625 [INFO] agent.agent_beacon: Agent Info -- ID: b12899a8d0a85f89bf3f52265c91f8e1 Version: 1.4.72 (1509978973) 2017-12-11 08:57:26,703 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:26,703 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:57:27,705 [WARNING] agent.agent_socket.HAS.7328496: BeaconThread no servers available yet 2017-12-11 08:57:29,741 [WARNING] agent.agent_socket.HAS.7328496: HASocket - No servers available 2017-12-11 08:57:29,741 [ERROR] agent.agent_beacon: Failed to send beacon: No response from server 2017-12-11 08:57:29,741 [WARNING] agent.agent_beacon: Beacon did not run successfully! 2017-12-11 08:57:30,210 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Creating new connection for probe query 2017-12-11 08:57:30,507 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:35,512 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:37,733 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:37,733 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:57:40,219 [WARNING] agent.agent_socket.AGS.7355280.endpoint.ingress.rapid7.com:443: SocketTracker-endpoint.ingress.rapid7.com:443 attempt 1 - Failed: timed out 2017-12-11 08:57:40,219 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Probe response received: timed out 2017-12-11 08:57:40,219 [WARNING] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Non-responsive - jailing for 48s 2017-12-11 08:57:40,516 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:45,521 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:48,835 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:48,835 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:57:50,523 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:55,526 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:57:59,751 [INFO] agent.agent_beacon: Agent Info -- ID: b12899a8d0a85f89bf3f52265c91f8e1 Version: 1.4.72 (1509978973) 2017-12-11 08:57:59,892 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:57:59,892 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:00,533 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:00,893 [WARNING] agent.agent_socket.HAS.7328496: BeaconThread no servers available yet 2017-12-11 08:58:02,941 [WARNING] agent.agent_socket.HAS.7328496: HASocket - No servers available 2017-12-11 08:58:02,941 [ERROR] agent.agent_beacon: Failed to send beacon: No response from server 2017-12-11 08:58:02,941 [WARNING] agent.agent_beacon: Beacon did not run successfully! 2017-12-11 08:58:05,537 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:10,545 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:10,936 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:58:10,936 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:15,553 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:20,560 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:21,982 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:58:21,982 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:25,569 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:28,309 [WARNING] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Have no previous good connections, will not reconnect 2017-12-11 08:58:28,309 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Creating new connection for probe query 2017-12-11 08:58:30,577 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:32,956 [INFO] agent.agent_beacon: Agent Info -- ID: b12899a8d0a85f89bf3f52265c91f8e1 Version: 1.4.72 (1509978973) 2017-12-11 08:58:33,003 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:58:33,003 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:34,006 [WARNING] agent.agent_socket.HAS.7328496: BeaconThread no servers available yet 2017-12-11 08:58:35,587 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:36,041 [WARNING] agent.agent_socket.HAS.7328496: HASocket - No servers available 2017-12-11 08:58:36,041 [ERROR] agent.agent_beacon: Failed to send beacon: No response from server 2017-12-11 08:58:36,041 [WARNING] agent.agent_beacon: Beacon did not run successfully! 2017-12-11 08:58:38,309 [WARNING] agent.agent_socket.AGS.7355280.endpoint.ingress.rapid7.com:443: SocketTracker-endpoint.ingress.rapid7.com:443 attempt 1 - Failed: timed out 2017-12-11 08:58:38,309 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Probe response received: timed out 2017-12-11 08:58:38,309 [WARNING] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Have no previous good connections, will not reconnect 2017-12-11 08:58:40,594 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:44,021 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:58:44,021 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:45,603 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:50,611 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:58:55,087 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:58:55,087 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:58:55,619 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:00,628 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:05,635 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:06,041 [INFO] agent.agent_beacon: Agent Info -- ID: b12899a8d0a85f89bf3f52265c91f8e1 Version: 1.4.72 (1509978973) 2017-12-11 08:59:06,152 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:59:06,152 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:59:07,155 [WARNING] agent.agent_socket.HAS.7328496: BeaconThread no servers available yet 2017-12-11 08:59:09,202 [WARNING] agent.agent_socket.HAS.7328496: HASocket - No servers available 2017-12-11 08:59:09,202 [ERROR] agent.agent_beacon: Failed to send beacon: No response from server 2017-12-11 08:59:09,202 [WARNING] agent.agent_beacon: Beacon did not run successfully! 2017-12-11 08:59:10,642 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:15,653 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:17,219 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:59:17,219 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:59:17,313 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Creating new connection for probe query 2017-12-11 08:59:20,661 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:25,665 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:27,323 [WARNING] agent.agent_socket.AGS.7355280.endpoint.ingress.rapid7.com:443: SocketTracker-endpoint.ingress.rapid7.com:443 attempt 1 - Failed: timed out 2017-12-11 08:59:27,323 [INFO] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Probe response received: timed out 2017-12-11 08:59:27,323 [WARNING] agent.agent_socket.SMT.58038672.endpoint.ingress.rapid7.com:443: Have no previous good connections, will not reconnect 2017-12-11 08:59:28,308 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:59:28,308 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:59:30,668 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:35,671 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:39,204 [INFO] agent.agent_beacon: Agent Info -- ID: b12899a8d0a85f89bf3f52265c91f8e1 Version: 1.4.72 (1509978973) 2017-12-11 08:59:39,392 [WARNING] agent.agent_socket.HAS.7328496: JobMessageSender no servers available yet 2017-12-11 08:59:39,392 [INFO] agent.message_bus.sndr.5348: Sent 0B of 0B from cache 2017-12-11 08:59:40,394 [WARNING] agent.agent_socket.HAS.7328496: BeaconThread no servers available yet 2017-12-11 08:59:40,675 [INFO] agent.job_manager: Verifying the status of the following running jobs: [] 2017-12-11 08:59:42,443 [WARNING] agent.agent_socket.HAS.7328496: HASocket - No servers available 2017-12-11 08:59:42,443 [ERROR] agent.agent_beacon: Failed to send beacon: No response from server 2017-12-11 08:59:42,443 [WARNING] agent.agent_beacon: Beacon did not run successfully! I guess there is some connection issue, any help appreciated

Posted by Abhijeet nawale 11 months ago