Skip to main content

Qradar- PSQL Report Development for EPS by log source result

EPS by logsource with QRADAR PSQL query tests and research

By

Travis Hutchings

thutch901@gmail.com

971.226.6732


 psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, sensordevice.devicename, sensordevicetype.devicetypename, to_timestamp(sensordevice.timestamp_last_seen/1000) from sensordevice, sensordevicetype where sensordevice.devicetypeid = sensordevicetype.id and sensordevice.deviceenabled = 't' and sensordevice.devicename not ilike '%wincollect%' and to_timestamp(sensordevice.timestamp_last_seen/1000) > now() - interval '30 days' order by to_timestamp(timestamp_last_seen/1000) desc"


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, sensordevice.devicename, sensordevicetype.devicetypename, to_timestamp(sensordevice.timestamp_last_seen/1000), to_timestamp(round(sensordevice.creationdate/1000) from sensordevice, sensordevicetype where sensordevice.devicetypeid = sensordevicetype.id and sensordevice.deviceenabled = 't' and sensordevice.devicename not ilike '%wincollect%' and to_timestamp(sensordevice.timestamp_last_seen/1000) > now() - interval '30 days' order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select sensordevice.devicename as LogSource, to_timestamp(round(sensordevice.creationdate/1000)) as created, managedhost.hostname as collector from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '1 week' order by collector"


psql -U qradar -c "select sensordevice.devicename as LogSource, to_timestamp(sensordevice.timestamp_last_seen/1000)) as created, managedhost.hostname as collector from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '1 week' order by collector"

psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname from sensordevice"


to_timestamp(sensordevice.timestamp_last_seen/1000)

psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, to_timestamp(sensordevice.timestamp_last_seen/1000), to_timestamp(round(sensordevice.creationdate/1000)), managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id from sensordevice"


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, managedhost.hostname, to_timestamp(sensordevice.timestamp_last_seen/1000), to_timestamp(round(sensordevice.creationdate/1000)) from sensordevice"


psql -A -F"," -U qradar -c "select sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' from sensordevice"


psql -A -F"," -U qradar -c "select sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' from sensordevice"

managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t'


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, mangedhost.hostname from sensordevice"


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.devicename from sensordevice"


psql -A -F"," -U qradar -c "select sensordevice.hostname from sensordevice"


to_timestamp(round(sensordevice.creationdate/1000)






psql -U qradar -c "select sensordevice.devicename as LogSource, to_timestamp(round(sensordevice.creationdate/1000)) as created, managedhost.hostname as collector from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '1 week' order by collector" 


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname, to_timestamp(sensordevice.timestamp_last_seen/1000) as Last Seen, to_timestamp(round(sensordevice.creationdate/1000)) as Created Date, managedhost.hostname from sensordevice, deployed_component"


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname as LogSource, to_timestamp(sensordevice.timestamp_last_seen/1000), to_timestamp(round(sensordevice.creationdate/1000)) as Created from sensordevice"


as created, managedhost.hostname from sensordevice


 psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname as LogSource, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate , to_timestamp(round(sensordevice.creationdate/1000))as Createddate from sensordevice"



psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname as LogSource, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate , to_timestamp(round(sensordevice.creationdate/1000))as Createddate from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now()"


psql -A -F"," -U qradar -c "select sensordevice.id, sensordevice.hostname as LogSource, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate , to_timestamp(round(sensordevice.creationdate/1000))as Createddate from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now()"



WORKING:

psql -U qradar -c "select sensordevice.devicename as LogSource, to_timestamp(round(sensordevice.creationdate/1000)) as created, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate, managedhost.hostname as collector from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '1 week' order by collector"




psql -U qradar -c "select logsourcename(logsourceid) as LogSource, sum(eventcount) / 24*60*60 as EPS from events where logsourceid=logsourceid group by logsourceid order by EPS desc last 24 hours" 



psql -U qradar -c "select sensordevicetype.devicetypedescription, count(*) from sensordevicetype, sensordevice where sensordevice.deviceenabled = 't' and sensordevicetype.id = sensordevice.devicetypeid group by sensordevicetype.devicetypedescription order by count(*) desc"


psql -U qradar -c "select hostname, devicename, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' and not (devicename ilike '%wincollect%') order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select hostname, devicename, to_timestamp(timestamp_last_seen/1000) from sensordevice where deviceenabled = 't' and not (devicename ilike '%wincollect%') order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select hostname, devicename, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' and not (devicename ilike '%wincollect%') order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select hostname, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' and not (devicename ilike '%wincollect%') order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select hostname, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select hostname, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select sourceip, destinationip, XFORCE_IP_CONFIDENCE ('Botnet Command and Control Server', destinationip) as 'XFE C&C Score' from events where logsourcegroupname (devicegrouplist) ilike '% Proxy%' ORDER BY 'XFE C&C Score'"


psql -U qradar -c "select hostname, to eventcount, to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select logsourcename to_timestamp(round(sensordevice.creationdate/1000)), to_timestamp(timestamp_last_seen/1000) as created from sensordevice where deviceenabled = 't' order by to_timestamp(timestamp_last_seen/1000) desc"


psql -U qradar -c "select * from sensordevice where id IN (select id from sensordevice)

psql -U qradar -c "select * from sensordevice.id where


WORKING:

psql -U qradar -c "select sensordevice.devicename as LogSource, to_timestamp(round(sensordevice.creationdate/1000)) as created, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate, managedhost.hostname as collector from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '1 week' order by collector"


psql -U qradar -c "select sensordevice.hostname as LogSource, peakeps60s as EPS, to_timestamp(round(sensordevice.creationdate/1000)) as created, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate, managedhost.hostname as dclQradarServer from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '2 week' order by dclQradarServer" >> $listFile



Perfect: In Report

psql -U qradar -c "select sensordevice.hostname as LogSource, peakeps60s as EPS, to_timestamp(round(sensordevice.creationdate/1000)) as created, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate, managedhost.hostname as dclQradarServer from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '2 week' order by dclQradarServer" >> $listFile

psql -U qradar -c "select sensordevicetype.devicetypedescription as DeviceDescription, count(*) from sensordevicetype, sensordevice where sensordevice.deviceenabled = 't' and sensordevicetype.id = sensordevice.devicetypeid group by sensordevicetype.devicetypedescription order by count(*) desc" >> $listFile

psql -U qradar -c "select hostname, peakeps60s as EPS, to_timestamp(round(sensordevice.creationdate/1000)) as CreationDate, to_timestamp(timestamp_last_seen/1000) as Last_Seen from sensordevice where deviceenabled = 't' order by to_timestamp(timestamp_last_seen/1000) desc"


eps_rc_script.sh: Release

psql -U qradar -c "select sensordevice.hostname as LogSource, peakeps60s as EPS, to_timestamp(round(sensordevice.creationdate/1000)) as created, to_timestamp(sensordevice.timestamp_last_seen/1000)as LastReportdate, managedhost.hostname as dclQradarServer from sensordevice, deployed_component, managedhost where deployed_component.id = sensordevice.eccomponentid and deployed_component.managed_host_id = managedhost.id and deviceenabled='t' and to_timestamp(round(sensordevice.creationdate/1000)) > now() - interval '2 week' order by dclQradarServer" >> $listFile

psql -U qradar -c "select sensordevicetype.devicetypedescription as DeviceDescription, count(*) from sensordevicetype, sensordevice where sensordevice.deviceenabled = 't' and sensordevicetype.id = sensordevice.devicetypeid group by sensordevicetype.devicetypedescription order by count(*) desc" >> $listFile

/store/scripts/results/dcl_rc_eps.sh


Comments

Post a Comment

Popular posts from this blog

Qradar Scripts and Results Part 1. Disk Space and Qradar Persistent Queue

______________________________________________________________________________________________________ Script examples written by Travis Hutchings thutch901@gmail.com 971.226.6732 _______________________________________________________ Disk Space and Alerting Results specific to Qradar servers and environments. General Linux script concepts can also be applied to system administration concepts. This disk space script utilizes a few Qradar support functions. 1. Disk Space Alerting and results in /store volume List disk space for all Qradar servers: df- h /opt/qradar/support/all_servers.sh -a '15%' 'df -h /store' >> $listFile List the top of the directory to see file dates and times to determine if Persistent Queue is processing: /opt/qradar/support/all_servers.sh -a '15%' 'ls -l /store/persistent_queue/ecs-ec-ingress.ecs-ec-ingress | head -6' >> $listFile Bottom of the Persistent Queue and seeing if results are processing: /opt/qradar/suppor...
Post a Comment
Read more

Splunk Log Forwarding Configuration Steps

Splunk Log Forwarding Configuration Steps: There are multiple ways to send linux logs to splunk like using splunk linux app, splunk universal forwarder or syslog. Best and performance reliable way is to install splunk universal forwarder on linux machines for which you wish to forward data.Splunk universal forwarder will act as agent for log collection.It will collect logs and will forward to indexer.We can  also use syslog for log collection and then install splunk forwarder on it and then forward data from syslog server to splunk indexer.Below we have provided steps for most reliable method to add linux logs to splunk For syslog installation and configuration follow steps give at below link: Refer below steps to add linux logs to splunk Step 1: On Splunk server (receiver) Download/install Splunk TA for Unix and Linux to the Splunk server (receiver) and enabled it by going to Manager|Apps|Enable Step 2: On host you want to collect data from (sender) Download and install the Splu...
Post a Comment
Read more