Archive
Highlighted

Count the three different value from one response message

Path Finder

I have the following response :
Message=Login failed for user 'testuserFSQ5'. Reason: Failed to open the explicitly specified database 't01FSQ5'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser
FSQ6'. Reason: Failed to open the explicitly specified database 't01FSQ5'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuserFSQ7'. Reason: Failed to open the explicitly specified database 't01FSQ5'. [CLIENT: 197.168.3.45]
Message=Login failed for user 'testuser
FSQ5'. Reason: Failed to open the explicitly specified database 't01FSQ6'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuserFSQ5'. Reason: Failed to open the explicitly specified database 't01FSQ6'. [CLIENT: 197.168.3.44]
Message=Login failed for user 'testuser
FSQ4'. Reason: Failed to open the explicitly specified database 't01FSQ7'. [CLIENT: 197.168.3.49]
Message=Login failed for user 'testuserFSQ4'. Reason: Failed to open the explicitly specified database 't01_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
testuserFSQ5 t01FSQ5 197.168.3.44 1 Failed to open the explicitly specified database
testuser
FSQ6 t01FSQ5 197.168.3.44 1 Failed to open the explicitly specified database
testuserFSQ7 t01FSQ5 197.168.3.45 1 Failed to open the explicitly specified database
testuser
FSQ5 t01FSQ6 197.168.3.44 2 Failed to open the explicitly specified database
testuserFSQ4 t01_FSQ7 197.168.3.49 2 Failed to open the explicitly specified database

could someone help me with this ?

Tags (2)
0 Karma
Highlighted

Re: Count the three different value from one response message

Path Finder

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

Highlighted

Re: Count the three different value from one response message

Builder

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

View solution in original post

Highlighted

Re: Count the three different value from one response message

Path Finder

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

0 Karma
Highlighted

Re: Count the three different value from one response message

Path Finder

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

0 Karma
Highlighted

Re: Count the three different value from one response message

Builder

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).

0 Karma
Highlighted

Re: Count the three different value from one response message

Path Finder

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

0 Karma