Count the three different value from one response message

[JyotiP]

I have the following response :
Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser_FSQ6'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser_FSQ7'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.45]
Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ6'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ6'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser_FSQ4'. Reason: Failed to open the explicitly specified database 't_01_FSQ7'. [CLIENT: 197.168.3.49]
Message=Login failed for user 'testuser_FSQ4'. Reason: Failed to open the explicitly specified database 't_01_FSQ7'. [CLIENT: 197.168.3.49]

like this I have 3K records, I want to count the number of user like and the corresponding count of database like and the corresponding client.
So my expected output should be like the following:
UserName DatabaseName ClientName Count Reason
testuser_FSQ5 t_01_FSQ5 197.168.3.44 1 Failed to open the explicitly specified database
testuser_FSQ6 t_01_FSQ5 197.168.3.44 1 Failed to open the explicitly specified database
testuser_FSQ7 t_01_FSQ5 197.168.3.45 1 Failed to open the explicitly specified database
testuser_FSQ5 t_01_FSQ6 197.168.3.44 2 Failed to open the explicitly specified database
testuser_FSQ4 t_01_FSQ7 197.168.3.49 2 Failed to open the explicitly specified database

could someone help me with this ?

[jpolvino · Accepted solution]

Here is one way to do it:

| makeresults 
| eval data="Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.44],
Message=Login failed for user 'testuser_FSQ6'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.44],
Message=Login failed for user 'testuser_FSQ7'. Reason: Failed to open the explicitly specified database 't_01_FSQ5'. [CLIENT: 197.168.3.45],
Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ6'. [CLIENT: 197.168.3.44],
Message=Login failed for user 'testuser_FSQ5'. Reason: Failed to open the explicitly specified database 't_01_FSQ6'. [CLIENT: 197.168.3.44],
Message=Login failed for user 'testuser_FSQ4'. Reason: Failed to open the explicitly specified database 't_01_FSQ7'. [CLIENT: 197.168.3.49],
Message=Login failed for user 'testuser_FSQ4'. Reason: Failed to open the explicitly specified database 't_01_FSQ7'. [CLIENT: 197.168.3.49]," 
| makemv data delim="," 
| mvexpand data 
| table data
| rex field=data "Message=Login failed for user '(?<UserName>[^']+)'\. Reason: (?<Reason>[^']+)'(?<DatabaseName>[^']+)'\. \[CLIENT: (?<ClientName>[^\]]+)\]"
| eventstats count AS Vol by UserName DatabaseName
| table UserName DatabaseName ClientName Vol Reason
| dedup UserName DatabaseName | rename Vol as "Count"

Output:

UserName    DatabaseName    ClientName  Count   Reason
testuser_FSQ5   t_01_FSQ5   197.168.3.44    1   Failed to open the explicitly specified database
testuser_FSQ6   t_01_FSQ5   197.168.3.44    1   Failed to open the explicitly specified database
testuser_FSQ7   t_01_FSQ5   197.168.3.45    1   Failed to open the explicitly specified database
testuser_FSQ5   t_01_FSQ6   197.168.3.44    2   Failed to open the explicitly specified database
testuser_FSQ4   t_01_FSQ7   197.168.3.49    2   Failed to open the explicitly specified database

[JyotiP]

@jpolvino the message contains 3k records, is it feasible to put it all int the eval function or only eval=Message ?

[Adrian_ftx]

Hi, he uses the eval command only for the example I think.
For your search you just need the part which begin at line 12
Can you confirm that, please @jpolvino?

Best regards,
Adrian

[jpolvino]

Correct. Lines 1-11 are for illustration purposes to prove out the solution. The assumption is that @JyotiP has events that look like this, and can get to them with a standard search. Line 12 exists in case the fields are not already extracted (if the rex is needed, remove field=data).

[JyotiP]

thank you so much for the clarification, @jpolvino and so as well @Adrian_ftx

[Adrian_ftx]

Hi

Can you try something like:

index=<your index>

| rex field=Message "\'(?<UserName>[^']+)"
| rex field=Message "Reason\:[A-Za-z ]+\'(?<DatabaseName>[^']+)"
| rex field=Message "\[CLIENT\: (?<ClientName>[0-9\.]+)"
| rex field=Message "Reason\:(?<reason>[^']+)"

| stats count by UserName DatabaseName ClientName Reason

The way I extract fields with rex command is not the most suitable but I think it works.
Anyway, I think the main problem you encountered was the field extraction, with this request you can adapt the search (especially the stats count command)
Hope it helps

Best regards,
Adrian