-2
SELECT name as "Device Name",description as "Description",last_seen_at  as "Last Seen At",collector as "Collector",alias_name as "Alias Name",
model as "Model",device_types as "Device Types",ip_function_type as "IP Function Type",system_oid as "System OID",vendor as "Vendor",
host_name as "Host Name",primary_ip_address as "Primary IP Address",city as "City",state as "State",
collection_system as "Collection System",location as "Location",location_description as "Location Description",
std_device_name as "Standard Device Name",collector_alternate_id as "Collector Alternate Id",create_time as "Create Time",
device_model_id as "Device Model Id",location_id as "Location Id",display_name as "Display Name",
display_description as "Display Description",supported_protocols as "Supported Protocols",polled_item_count as "Polled Iten Count",
life_cycle_state as "Life Cycle State",life_cycle_state_last_chg_on as "Life Cycle State Last Change On",
contact_status as "Contact Status",calculated_contact_status as "Calculated Contact Status",last_updated_at as "Last Updated At",npm_status as "NPM Status",node_id as "Node Id"
FROM  NPM_DEVICES
WHERE  --device_id > 20000000 and 
(Lower(name) LIKE Lower('%$Search%') or Lower(description) LIKE Lower('%$Search%') or Lower(collector) LIKE Lower('%$Search%')
or Lower(alias_name) LIKE Lower('%$Search%') or Lower(model) LIKE Lower('%$Search%') or Lower(device_types) LIKE Lower('%$Search%')
or ip_function_type LIKE '%$Search%' or Lower(display_name) LIKE Lower('%$Search%') or
Lower(display_description) LIKE Lower('%$Search%') or Lower(system_oid) LIKE Lower('%$Search%') or
Lower(location) LIKE Lower('%$Search%') or Lower(location_description) LIKE Lower('%$Search%') or
Lower(vendor) LIKE Lower('%$Search%') or Lower(life_cycle_state) LIKE Lower('%$Search%') or
Lower(contact_status) LIKE Lower('%$Search%') or Lower(state) LIKE Lower('%$Search%') or
Lower(city) LIKE Lower('%$Search%') or Lower(std_device_name) LIKE Lower('%$Search%') or
collector_alternate_id LIKE '%$Search%' or primary_ip_address LIKE '%$Search%'
or  Lower(host_name) LIKE Lower('%$Search%'))
LIMIT $resultlimit;

Above query need to get devices which have been last seen within the last 30 days

Bryan Clark
  • 2,542
  • 1
  • 15
  • 19
VishnuVardhan
  • 1
  • 2

2 Answers2

0

You can use last_updated_at column to get last 30 day records.

where last_updated_at BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE()
Dhyanam Trivedi
  • 1
  • 1
  • We have a column last_seen_at so we can use that ,because to get devices that have been seen at 30days and when used this condition " where last_updated_at BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE()" getting sytanx error as pq: syntax error at or near "30" – VishnuVardhan Aug 26 '21 at 06:58
0

Add something like this in the WHERE clause:

AND CAST(last_updated_at AS DATE) BETWEEN DATEADD(DAY, -30, CAST(NOW() AS DATE)) AND NOW()
KrisKul
  • 13
  • 3
  • Thanks for the solution, but we dont have column day in NPM_DEVICES table – VishnuVardhan Aug 25 '21 at 08:26
  • In that example DAY is part of the SQL Function DATEADD to indicate the interval. It's just that PostgreSQL doesn't implement DATEADD, the first answer shows how the same requirement is addressed in postgres. Hope this helps. – greenweeds Aug 25 '21 at 10:01