How to compare counts on two different indexes on fields

[AshChakor]

I have two indexes and it has similar fields and need to compare counts on these two indexes.

For example

Index A

Id	status_code
1	b
2	a
3	a
4	m
5	b
6	c

Index B

ID	category_code1	from_dt	To_dt
101	p	01/01/2019	09/14/2018
102	b	01/01/2019	null
103	a	01/01/2019	null
104	m	01/01/2019	null
105	a	01/01/2019	null

Expected output

	Index A count	Index B Count	difference
Category A	2	2
Category B	2	1	1
Category C	1	0

How do I get this count by categories on two different indexes. 

[gcusello · Accepted solution]

Hi @AshChakor,

if status_code and category_code1 are the same kay, you can use rename to have one key and then use index to divide values, something like this:

index=indexA OR index=indexB
| rename status_code AS category_code1
| stats count(eval(index=indexA)) AS "Index A count" count(eval(index=indexB)) AS "Index B count" BY category_code1
| eval difference="Index A count"-"Index B count"
| table category_code1 "Index A count" "Index B count" difference

 Ciao.

Giuseppe

[AshChakor]

@gcusello  Thanks for the reply.  I have to determine Categories based on the following values for example 

| eval CategoryACount = if( (IN(category_code1, "a", "m") OR like(category_code1, "q%"))  AND isnull(TO_DT), 1, 0)

| eval CategoryBCount = if( (like(category_code1, "?%") OR like(category_code1, "b%"))  AND isnull(TO_DT), 1, 0) | stats count(CategoryACount ) as CategoryA, count(CategoryBCount ) as CategoryB .

Also one more problem is TO_DT doesn’t apply on Index A. I am not sure how to include the above into the above query you mentioned

[gcusello]

Hi @AshChakor,

sorry I forgot it, you have to use the values option in stats count:

| stats count(CategoryACount) AS CategoryA count(CategoryBCount) AS CategoryB values(TO_DT) AS TO_DT BY category_code1

Ciao.

Giuseppe

[AshChakor]

@gcusello I tried the suggestions but rename field and the query didn't work. Sorry that I didn't display my eval experssions earlier.  Here is my basic query which results into two rows and 6 columns. 

index=indexA OR index=indexB

| eval indexA_CategoryA = if( (IN(status_code, "m", "p") OR like(status_code, "n%")) , 1, 0)

| eval indexA_CategoryB = if( like(status_code, "s%") , 1, 0)

| eval indexA_CategoryC = if( (like(status_code, "^%") OR like(status_code, "h%")) , 1, 0)

| eval indexB_CategoryA = if( (IN(category_code1, "m", "p") OR like(category_code1, "n%")) AND isnull(TO_DT), 1, 0)

| eval indexB_CategoryB = if( like(category_code1, "s%") AND isnull(TO_DT), 1, 0)

| eval indexB_CategoryC = if( (like(category_code1, "^%") OR like(category_code1, "h%"))  AND isnull(TO_DT), 1, 0)

| stats sum(indexA_CategoryA) as indexA_CategoryA, sum(indexA_CategoryB) as indexA_CategoryB, sum(indexA_CategoryC) as indexA_CategoryC, sum(indexB_CategoryA) as indexB_CategoryA, sum(indexB_CategoryB) as indexB_CategoryB, sum(indexB_CategoryC) as indexB_CategoryC by index

index	indexA_CategoryA	indexA_CategoryB	indexA_CategoryC	indexB_CategoryA	indexB_CategoryB	indexB_CategoryC
IndexA	45	201	75	0	0	0
IndexB	0	0	0	45	189	76

[gcusello]

Hi @AshChakor,

what do you mean with "didn't work": do you have wrong results or didn't you have any results?

If wrong results, check one by one each eval statement, maybe anyone isn't correct.

If you haven't results, delete the stats command and see for each index which values you have.

Ciao.

Giuseppe

[AshChakor]

@gcusello Your solution is working now I tweaked a little to get the category definition 

index=indexA OR index=indexB
| rename status_code AS category_code1
| eval category = case( (category_code1 IN(, "m", "p") OR like(category_code1, "n%")) , CategoryA, like(category_code1, "s%"), CategoryB, (like(category_code1, "^%") OR like(category_code1, "h%"), CategoryC ))
| stats count(eval(index=indexA)) AS "Index A count" count(eval(index=indexB)) AS "Index B count" BY category| eval difference="Index A count"-"Index B count" | table category "Index A count" "Index B count" difference

[gcusello]

Hi @AshChakor,

good!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[AshChakor]

@gcusello  Thank you!!