Splunk Search

Generate a table of keywords based on the correlation of a username across three rules?

Builder

I have a tool that has three different rules, each rule is composed of a list of unique keywords. A rule is triggered when a specific keyword is observed in the users network traffic. What I want to do is generate a list of the keywords that a user is hitting when a user has triggered rule1, rule2, and rule3.

I can generate a list of all keywords triggered by all users, but I don't want the results when a user has only triggered 1 or 2 of the rules. A user must trigger all three rules, then output a list of keywords observed.

The string that has the keyword is in the format:

keyword;username;date

I have a query that shows me a list of users that have triggered at least each rule once or more

index=tool | rex field=string "(?<USER>(?<=;)[^;]*(?=;))" | stats count AS USER by rule | where rule1>0 AND rule2>0 AND rule3>0

How can I take the output from this and then generate a list of the keywords? I tried using the above query as a subsearch but that didn't seem to work.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this a try

index=tool | rex field=string "(?<USER>(?<=;)[^;]*(?=;))" | stats dc(rule) as rules by USER | where rules=3 | rex field=USER "(?<keyword>[^;]+);(?<user>[^;]+);(?<date>[^;]+)" | stats list(keyword) as keyword by user

Update#1

Give this a try

index=tool | rex field=Message "(?<keyword>[^;]+);(?<user>[^;]+);(?<date>[^;]+)" | stats dc(Rule) as Rule list(keyword) as keyword by user | where Rule=3 | stats count by keyword

View solution in original post

Influencer

Can you post some sample data and maybe a mock-up of what your expected output should be?

0 Karma

Builder

Sample raw data:

Aug  3 18:56:19 Product="MS XPS" Rule="Rule1" Message="amazon;<sender1@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule1" Message="age;<sender1@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule3" Message="cat;<sender1@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule1" Message="apple;<sender2@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule2" Message="bear;<sender1@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule3" Message="chair;<sender3@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule2" Message="bag;<sender2@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule1" Message="apple;<sender4@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule2" Message="bear;<sender4@email.com>;recipient@email.com"
Aug  3 18:56:19 Product="MS XPS" Rule="Rule3" Message="chimp;<sender4@email.com>;recipient@email.com"

Current Output

Username             Rule1  Rule2  Rule3
sender1@email.com    2      1      1
sender4@email.com    1      1      1

Desired Output (note: these are the keywords observed when a user has triggered rule1, rule2, and rule3 at least once or more.

Keywords   Count
amazon     1
apple      1
age        1
bear       2
cat        1
chimp      1
0 Karma

Legend

Try this

index=tool | rex field=string "(?<keyword>\w+);(?<user>\w+);(?<date>.*)" | stats dc(rule) as rules list(keyword) as keywords) by USER | where rules=3
0 Karma

Builder

Can you check the syntax? It lists the users, then the rule hit count (all of them are 3), and then the keywords column which is blank..! Here is what I am using based on your post:

index=tool Rule=TEST* | rex field=string "(?<keyword>\w+);(?<user>\w+);(?<date>.*)" | stats dc(Rule) as rules list(keyword) as keywords by user| where rules=3

Rule=TEST* because it is TEST1, TEST2, and TEST3 (the three rules)

0 Karma

SplunkTrust
SplunkTrust

Give this a try

index=tool | rex field=string "(?<USER>(?<=;)[^;]*(?=;))" | stats dc(rule) as rules by USER | where rules=3 | rex field=USER "(?<keyword>[^;]+);(?<user>[^;]+);(?<date>[^;]+)" | stats list(keyword) as keyword by user

Update#1

Give this a try

index=tool | rex field=Message "(?<keyword>[^;]+);(?<user>[^;]+);(?<date>[^;]+)" | stats dc(Rule) as Rule list(keyword) as keyword by user | where Rule=3 | stats count by keyword

View solution in original post

Builder

You did it! Thank you so much somesoni2! Update#1 has the exact syntax I needed and gives a simple list of the keyword and number of occurrences when each comes from a user that has hit triggered all 3 rules. I have been struggling for over a week trying to get this to work. Thank you so very much! I love this community.

Builder

This generates a list of users and has a keyword column next to it. The keyword column is blank though 😞

To backtrack to the beginning just so that I am clear:

  • I have three rules that generate 10,000 alerts
  • When I look for the same user triggering at least each rule once (or more) I now have 1,000 alerts (my existing query)
  • How do I list the keywords for those 1,000 alerts? I don't care about the 10,000 alerts as they aren't correlated.

This has been driving me crazy. I cannot seem to figure it out!

0 Karma