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

[DEAD_BEEF]

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.

[masonmorales]

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

[DEAD_BEEF]

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

[sundareshr]

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

[DEAD_BEEF]

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)

[somesoni2 · Accepted solution]

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

[DEAD_BEEF]

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.

[DEAD_BEEF]

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!