Splunk Search

Count the three different value from one response message

JyotiP
Path Finder

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 ?

Tags (2)
0 Karma
1 Solution

jpolvino
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

jpolvino
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

JyotiP
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

Adrian_ftx
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

jpolvino
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

JyotiP
Path Finder

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

0 Karma

Adrian_ftx
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

Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...