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 865.405.8830 _______________________________________________________ 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

Technology Resource Implementation and Resource Links:

 Travis Hutchings Technology Resource Implementation and Resource Links Monitoring Scripts and Resources Linux Projects and Scripts      - GrayLog Server process flow and installation notes:                                 GrayLog Server process flow and installation notes      - Qradar Scripts and Articles:                  Qradar Disk Space Alert and Persistent Queue                  Qradar EPS by Log Source Script                 Postgres Research for EPS report by logsource Syslog-NG Resources and Links Syslog-ng. conf manipulation and changes     -Linux Scripts Windows Projects and Scripts Seterus Active Directory Project proposal Active Directory Migrations Microsoft Azure Resources and Links: Azure KQL Resources and Commands Arti...
Post a Comment
Read more