List and export all enabled Log Sources using psql query in QRadar

In order to export a list of all enabled log sources, SIEM administrators can run one of the following commands basd on psql query in QRadar. The commands are available from the Console back end, so using SSH, log in to the QRadar Console as the root user. To enter the command line for the database, type

psql -U qradar

or

psql -Uqradar

Once you enter QRadar database, then you see different prompt than in the command line bash. To view a list of enabled log sources with the ID, hostname, last seen event time, type

select id, hostname, devicename, devicedescription,to_timestamp(timestamp_last_seen/1000) as last_seen from sensordevice where deviceenabled='t';

This method is not the best if you are not familiar with sql and for example with LIMIT clause. Simply exit database using \q command and return to the command line bash.

The same command issued from bash let us write down results directly to the file. Simply do this with the following syntax (-c stands for command)

psql -Uqradar -c "select id, hostname, devicename, devicedescription,to_timestamp(timestamp_last_seen/1000) as last_seen from sensordevice where deviceenabled='t'"> /root/enabled_log_sources

The command exports a list of enabled log sources to a file named enabled_log_sources in /root folder.

Advanced psql query in QRadar

Getting more familiar with sql language, let us to generate more advanced queries, which help us to retrieve more advanced information from QRadar database. For example, to view a list of enabled log sources by most recent events seen, properly formatted in csv comma delimited format just type the following command:

psql -Uqradar 

\copy (select devicename, devicedescription, age(to_timestamp(timestamp_last_seen/1000)) as last_seen from sensordevice where deviceenabled='t' and age(to_timestamp(timestamp_last_seen/1000)) > interval '2' day order by timestamp_last_seen desc) To '/root/lastseenLS.csv' With CSV

The above command exports a list of enabled log sources to a file named lastseenLS.csv. The file is properly formatted in comma delimited CSV format, which can be easily imported to MS Excel.

Alternatively, the mentioned command can be run without entering the database and used just to print the information to the screen.

psql -U qradar -c "select devicename, devicedescription,age(to_timestamp(timestamp_last_seen/1000)) as last_seen from sensordevice where deviceenabled='t' and age(to_timestamp(timestamp_last_seen/1000)) > interval '2' day order bytimestamp_last_seen desc"

Please note, that in this case at the very end you can omit a ; (semicolon) sign, which is mandatory when you are in database prompt. Also, it is worth to mention, that in QRadar database any time is usually written in Epoch miliseconds, so for this purpose we use to_timestamp clause and divide by 1000. If you are not familiar with Epoch in IT, it is number of miliseconds elapsed since 1 January 1970 00:00:00 UTC. This is when IT world has started.