Splunk Search

Find Ids of a table not in other search table

mangaldev
Engager

I have 2 type of search messages -

Problem #1

Problem #5

and other one goes like this -

Solved problem_id successful: 1

Solved problem_id successful: 2

Solved problem_id successful: 3

 

I want to return Problems which have not been solved yet. So in the above case it should result with 5 only.

What I tried :

 

 

Search1 ==> index="production" "Problem #" earliest=-3h latest=-1h | rex field=message ".*Problem #(?<problem_id>.*):.*" | stats count by problem_id |table problem_id

 

 

Extracting all Problem Ids(works fine)

Search 2==> 

 

 

search index="production" "Solved problem_id successful:" earliest=-3h | rex field=message ".*Solved problem_id successful: (?<problem_id>.*)" | stats count by problem_id |table problem_id 

 

 

Extracting all problem ids which have been solved. (works fine )

 

Now to find problems which are not solved -->

search1 | search not [search2] 

 

 

index="production" "Problem #" earliest=-3h latest=-1h | rex field=message ".*Problem #(?<problem_id>.*):.*" | stats count by problem_id |table problem_id | search NOT [search index="production" "Solved problem_id successful: " earliest=-3h | rex field=message ".*Solved problem_id successful: (?<problem_id>.*)" | stats count by problem_id |table problem_id ]

 

 

Now above query doesn't work and for some reason just returns the result from search1. It seems not is not working for some reason. Thanks in advance.

PS: I have modified the queries little bit on the fly to remove sensitive info.

 

Labels (3)
Tags (3)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

I would place posed problems  and solved problems into separate sets, then use eventstats to mash them up for comparison.  Like this:

index="production" "Problem #" earliest=-3h latest=-1h
| rex field=message ".*Problem #(?<problem_id>.*):.*"
| stats count by problem_id
| append
 [search index="production" "Solved problem_id successful: " earliest=-3h
 | rex field=message ".*Solved problem_id successful: (?<solved_id>.*)"
 | stats values(solved_id) as solved]

| eventstats values(solved) as solved
| where isnotnull(problem_id) AND problem_id != solved

 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

I would place posed problems  and solved problems into separate sets, then use eventstats to mash them up for comparison.  Like this:

index="production" "Problem #" earliest=-3h latest=-1h
| rex field=message ".*Problem #(?<problem_id>.*):.*"
| stats count by problem_id
| append
 [search index="production" "Solved problem_id successful: " earliest=-3h
 | rex field=message ".*Solved problem_id successful: (?<solved_id>.*)"
 | stats values(solved_id) as solved]

| eventstats values(solved) as solved
| where isnotnull(problem_id) AND problem_id != solved

 

*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>