How to derive percentage of expected and actual count from two searches with different count strategies

[alexa]

Hi,
I have two separate searches that are working independently (expected count, actual count).  I want to combine the searches to get a percentage for actual count to expected count; however append, appendcols, and other ways to add the searches together have so far not worked for me.  Curious if there's a better way to use stats, eval, transaction commands to achieve the combination of these searches. The end goal is to provide a visualization to understand if there's an issue when the actual count does not match the expected count - so open to suggestions on better ways to achieve that goal.

Search 1 (counting all records that are sent through producer class not part of refresh process):

index=index | search ("ProducerClass" AND "*Sending message:*") NOT "*REFRESH*" | stats count as actual_count

Search 2 (sum of record counts on files processed through opportunity class): 

index=index | search "OpportunityClass" AND "Processing file: file_name" | rex field=_raw "Processing file: file_name with (?<record_count>[^\s]+) records" | stats sum(record_count) as expected_count

I have tried append like this and it has not worked:

index=index | search ("ProducerClass" AND "*Sending message:*" ) NOT "*REFRESH*"
| stats count as actual_count
| append [
search index=index "OpportunityClass" AND "Processing file: "
| rex field=_raw "Processing file: file_name with (?<record_count>[^\s]+) records"
| stats sum(record_count) as expected_count]
| eval percent =expected_count/actual_count * 100

appendcols similarly did not work ("Aborting Long Running Search").  Assuming I am incorrectly understanding how I am combining these searches and it is causing issues when using append type commands.  Using an OR on the searches works, but unsure how to use other commands to group the results properly after:

index=index | search (("ProducerClass" AND "*Sending message:*" ) NOT "*REFRESH*") OR ("OpportunityClass" AND "Processing file: ")
| ...

[yuanliu]

---

I have tried append like this and it has not worked:

When you have a requirement like this, the method @Richfez presented should be your first choice and not thinking in terms of append or appendcols.  Meanwhile, there shouldn't be any reason why append would not work if you combine the two rows with another stats

index=index ("ProducerClass" AND "*Sending message:*" ) NOT "*REFRESH*"
``` ^^^ do not separate filter from index search ```
| stats count as actual_count
| append
  [search index=index "OpportunityClass" AND "Processing file: "
  | rex field=_raw "Processing file: file_name with (?<record_count>[^\s]+) records"
  | stats sum(record_count) as expected_count]
| stats values(*) as *
| eval percent =expected_count/actual_count * 100

Note in the first line, do not use a pipe sign to add a search filter if the filter applies directly to index search.  Adding all applicable filters in index search will greatly improve performance.

Similarly, there is no reason why appendcols will not work to your expectation.

index=index ("ProducerClass" AND "*Sending message:*" ) NOT "*REFRESH*"
``` ^^^ do not separate filter from index search ```
| stats count as actual_count
| appendcols
  [search index=index "OpportunityClass" AND "Processing file: "
  | rex field=_raw "Processing file: file_name with (?<record_count>[^\s]+) records"
  | stats sum(record_count) as expected_count]
| eval percent =expected_count/actual_count * 100

(If you think appendcol didn't work, you should post your search, and give sample (anonymized) results, then explain why the results are wrong.)

Here are three simulations to illustrate how they give the same results

1. append

index=_internal
| stats count as actual_count
| append
    [search index=_audit
    | stats count as expected_count]
| stats values(*) as *
| eval percent = round(expected_count/actual_count * 100, 2)

actual_count	expected_count	percent
237531	4686	1.97

2. appendcols

index=_internal
| stats count as actual_count
| appendcols
    [search index=_audit
    | stats count as expected_count]
| eval percent = round(expected_count/actual_count * 100, 2)

actual_count	expected_count	percent
236458	4660	1.97

3. Rich's method

index = _internal OR index = _audit
| eval is_actual = if(searchmatch("index = _internal"), "true", null())
| eval is_expected = if(searchmatch("index = _audit"), "true", null())
| stats count(is_actual) as actual_count count(is_expected) as expected_count
| eval percent = round(expected_count/actual_count * 100, 2)

actual_count	expected_count	percent
245978	4861	1.98

(Any difference in illustrated numbers is due to data change between searches.)

[Richfez · Accepted solution]

There's a few different techniques for combining things like this.  The one I think you might find most useful could be...

OK, example off some silly data I have.  Once I work through that and explain, I'll make an attempt at doing your searches too. 

Anyway - Amazon Glacier uploads for my little server, every night it tries to push up new files.  I think it's similar enough to your data that the example may work, though forgive me for it being stupidly contrived in so many ways.  🙂

The idea is there are two messages.  One is contains "uploaded part" and the other contains "created an upload_id".  I don't have a real good "number" to rex out, but I have a PID I can steal the first two digits of to pretend I have numbers.  🙂

index="glacier" ( "uploaded part" OR "created an upload_id")
| eval is_actual = if(searchmatch("*created an upload_id*"), 1, 0)
| rex "PID\s+(?<dumb_counter>\d\d)"
| eval is_expected = if(searchmatch("*uploaded part*"), dumb_counter, 0)
| stats sum(dumb_counter) as is_expected, sum(is_actual) as is_actual

So looking at that, the first line gets all the data, both types.

The second line is using an eval to create "is_actual". And when the event matches 'created an upload_id', that is_actual will be set to 1.  Otherwise 0.

The third line is a rex, just like yours only more dumb.  It creates a field "dumb_counter" which will either be a two digit number, or will be null if it didn't match.  (Unfortunately, ALL my lines have a PID, so ... this is broken, but I fix it in the next line using logic just like in line 2.

Line 4 then is the fix, where I eval 'is_expected' to either be the dumb_counter I wrote IF the line matches what I need it to match, or 0 if it doesn't.  (I don't think you'll need this extra logic, but I do and it was easy enough to explain!)

The the last line just adds up the two independently.  Afterwords you can easily do a new eval for percent or whatever.  We'll do this when we try YOUR search.

And it's time for that now. We'll use the same technique, only it'll be messier because you have more conditions to work with.

index=index ( ("ProducerClass" AND "*Sending message:*") NOT "*REFRESH*") OR ("OpportunityClass" AND "Processing file: file_name")
| eval is_actual = if(searchmatch("*ProducerClass*") AND searchmatch("*Sending message:*"), 1, 0)
| rex field=_raw "Processing file: file_name with (?<record_count>[^\s]+) records"
| eval is_expected = if(searchmatch("*OpportunityClass*") AND searchmatch("*Processing file: *"), record_count, 0)
| stats sum(is_expected) as is_expected, sum(is_actual) as is_actual
| eval percent = (is_expected / is_actual) *100

Again, line 1 pulls all the data in.  (Special note, you use NOT ... which means those records won't be there and we can ignore them in the eval, you'll see!

Line 2 creates our is_actual.  This line could be left here or moved to after the rex - it won't really matter.

Line 3 is our rec to get our record count...

Which in line 4 we convert into a new field 'is_expected' ONLY if the event is the right event - this is very, very likely to not be needed, you could extract the field in line 3 with the name 'is_expected', remove this line, and it probably should all work the same.  But we're being careful here.  🙂

The we just sum those in line 5, and do some math in line 6.

So of special note!

If "file_name" actually stands in for the filename which changes, we'll have to work around that with a wildcard or something.  OR if you can drop in a line from each event type (appropriately obfuscated, of course) then we can just work it using one of the other methods.

For instance, we may be able to ignore "filename" in the base search, then just edit the rex a wee bit to work around it later, too.

Anyhow, give those a shot, and if it works for you (or is easily "fixed" because I'm sure there's some typos in it), then great!  Otherwise, let us know what's happening and we can help more.

Happy Splunking,

Rich

[alexa]

Thank you @Richfez - once I substituted my values into the search it works great!  And the explanation/example makes it very clear what is happening in the search.