Search example: Daily employee reports

The following example describes how to use a complex advanced search query to see specific employee information.

For identity management purposes, you decide to generate a daily report of the user activity in QRadar. The report must include information about the employee, such as their user names, their serial number, their manager, and their activities.

An employee might have multiple user names in QRadar. You use the RESTful API to build a reference map that returns all associated user names to the employee's name, Global_User. For the serial number and the manager's name, you create another reference data set and add it to the reference map.

Employee activities can range from login failures to QRadar tasks, such as deleting objects. These events are recorded by QRadar. By specifying the frequency of the events in the map, you can gauge when suspicious activity occurs. You group the data by the employee's name and the event name, and then sort the data by the highest event frequency within a 24-hour time frame.

To see this daily report, you log in to QRadar Console. In the Advanced Search text box on the Log Activity tab, you type the following search query:

select REFERENCEMAP('GlobalID_Mapping', username) as Global_User, QIDNAME(qid) as 'Event Name', count(*) as 'Event Count', FIRST(username) as UserId, REFERENCETABLE('employee_data','SerialNum', Global_user) as 'Serial Number', REFERENCETABLE('employee_data','Manager',Global_User) as Manager from events where (Global_User IS NOT NULL) GROUP BY Global_user,'Event Name' ORDER BY 'Event Count' DESC last 1 DAYS