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.
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
Can you post some sample data and maybe a mock-up of what your expected output should be?
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
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
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)
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
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.
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:
This has been driving me crazy. I cannot seem to figure it out!