topic Re: How to compare counts on two different indexes on fields in Splunk Search

How to compare counts on two different indexes on fields

AshChakor — Mon, 14 Sep 2020 13:54:31 GMT

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.

Re: How to compare counts on two different indexes on fields

gcusello — Mon, 14 Sep 2020 14:08:21 GMT

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

Re: How to compare counts on two different indexes on fields

AshChakor — Mon, 14 Sep 2020 15:04:00 GMT

@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

Re: How to compare counts on two different indexes on fields

gcusello — Mon, 14 Sep 2020 15:53:17 GMT

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

Re: How to compare counts on two different indexes on fields

AshChakor — Mon, 14 Sep 2020 19:36:22 GMT

@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

Re: How to compare counts on two different indexes on fields

gcusello — Tue, 15 Sep 2020 06:52:12 GMT

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

Re: How to compare counts on two different indexes on fields

AshChakor — Tue, 15 Sep 2020 14:23:15 GMT

@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

Re: How to compare counts on two different indexes on fields

gcusello — Tue, 15 Sep 2020 14:41:43 GMT

Hi @AshChakor,

good!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: How to compare counts on two different indexes on fields

AshChakor — Tue, 15 Sep 2020 14:50:42 GMT

@gcusello Thank you!!