Data mismatch in a table

[Ram2]

Query1:
index=test-index "ERROR" Code=OPT OR Code=ONP |bin _time span=1d |stats count as TOATL_ONIP1 by Code _time.
Query2:
index=test-index "WARN" "User had issues with code" Code=OPT OR Code=ONP 
|search code_ip IN(1001, 1002, 1003, 1004)
|bin _time span=1d 
|stats count as TOATL_ONIP2 by Code _time.

Query3:
index=test-index "INFO" "POST" NOT "GET /authenticate/mmt"
|search code_data IN(iias, iklm, oilk)
|bin _time span=1d 
|stats count as TOATL_ONI3 by Code _time.

Combined query:
index=test-index "ERROR" Code=OPT OR Code=ONP |bin _time span=1d |stats count as TOATL_ONIP1 by Code _time
|appendcols
[|search index=test-index "WARN" "User had issues with code" Code=OPT OR Code=ONP 
|search code_ip IN(1001, 1002, 1003, 1004)
|bin _time span=1d 
|stats count as TOATL_ONIP2 by Code _time]
|appendcols
[|search index=test-index "INFO" "POST" NOT "GET /authenticate/mmt" Code=OPT OR Code=ONP 
|search code_data IN(iias, iklm, oilk)
|bin _time span=1d 
|stats count as TOATL_ONI3 by Code _time]
|eval Start_Date=srftime(_time, "%Y-%m-%d")
|table Start_Date Code TOATL_ONIP1 TOATL_ONIP2 TOATL_ONIP3

Output for individual query1:

Start_Date	Code	TOTAL_ONIP1
2025-04-01	OPT	2
2025-04-02	OPT	4
2025-04-03	OPT	0
2025-04-01	ONP	1
2025-04-02	ONP	2
2025-04-03	ONP	3

Output for individual query2:

Start_Date	Code	TOTAL_ONIP2
2025-04-01	OPT	0
2025-04-02	OPT	0
2025-04-03	OPT	0
2025-04-01	ONP	4
2025-04-02	ONP	2
2025-04-03	ONP	3

Output for individual query3:

Start_Date	Code	TOTAL_ONIP3
2025-04-01	OPT	0
2025-04-02	OPT	0
2025-04-03	OPT	9
2025-04-01	ONP	0
2025-04-02	ONP	6
2025-04-03	ONP	8

Combined query output:

Start_Date	Code	TOTAL_ONIP1	TOTAL_ONIP2	TOTAL_ONIP3
2025-04-01	OPT	2	4	9
2025-04-02	OPT	4	2	6
2025-04-03	OPT	1	3	8
2025-04-01	ONP	2
2025-04-02	ONP	3
2025-04-03	ONP

When we combine the query the count is not matching with the individual queries.
For example: on April1st for ONP for TOTAL_ONIP2 is 4 but in combined one it is showing null,  and 4 value updated in OPT april 1st 

[livehybrid]

Hi @Ram2 

Another approach would be to use a single query without any subsearch/apppend etc:

index=test-index (("ERROR" Code=OPT OR Code=ONP) OR ("WARN" "User had issues with code" Code=OPT OR Code=ONP code_ip IN(1001, 1002, 1003, 1004)) OR ("INFO" "POST" NOT "GET /authenticate/mmt" Code=OPT OR Code=ONP code_data IN(iias, iklm, oilk))) 
| bin _time span=1d 
| eval TOATL_ONIP1=if(match(_raw, "ERROR") AND (Code="OPT" OR Code="ONP"), 1, 0) 
| eval TOATL_ONIP2=if(match(_raw, "WARN") AND match(_raw, "User had issues with code") AND (Code="OPT" OR Code="ONP") AND code_ip IN(1001, 1002, 1003, 1004), 1, 0) 
| eval TOATL_ONIP3=if(match(_raw, "INFO") AND match(_raw, "POST") AND NOT match(_raw, "GET /authenticate/mmt") AND (Code="OPT" OR Code="ONP") AND code_data IN(iias, iklm, oilk), 1, 0) 
| stats sum(TOATL_ONIP1) as TOATL_ONIP1 sum(TOATL_ONIP2) as TOATL_ONIP2 sum(TOATL_ONIP3) as TOATL_ONIP3 by Code _time 
| eval Start_Date=strftime(_time, "%Y-%m-%d") 
| table Start_Date Code TOATL_ONIP1 TOATL_ONIP2 TOATL_ONIP3

This determines the ONIP number based on fields in the event and then does a stats to count each ONIP by Code.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[PickleRick]

That's more or less what I was talking about 🙂

[livehybrid]

Ah @PickleRick yes spot on. Sorry hadnt seen your reply but seems like we are thinking along the same lines!

[ITWhisperer]

appendcols does not correlate values from existing columns, try using append and then final stats with values() and by Start_Date and Code

index=test-index "ERROR" Code=OPT OR Code=ONP |bin _time span=1d |stats count as TOATL_ONIP1 by Code _time
|append
[|search index=test-index "WARN" "User had issues with code" Code=OPT OR Code=ONP 
|search code_ip IN(1001, 1002, 1003, 1004)
|bin _time span=1d 
|stats count as TOATL_ONIP2 by Code _time]
|append
[|search index=test-index "INFO" "POST" NOT "GET /authenticate/mmt" Code=OPT OR Code=ONP 
|search code_data IN(iias, iklm, oilk)
|bin _time span=1d 
|stats count as TOATL_ONI3 by Code _time]
|eval Start_Date=srftime(_time, "%Y-%m-%d")
|stats values(TOATL_ONIP1) as TOATL_ONIP1 values(TOATL_ONIP2) as TOATL_ONIP2 values(TOATL_ONIP3) as TOATL_ONIP3 by Start_Date Code

[PickleRick]

Be also aware of the subsearch limitations. You can't run them for long and they have a limit for returned results. So if you run them over a big data set you might get incomplete results and since the subsearches will get silently finalized you won't even know about it.

So i  your case it would probably be better to search for all matching events initially and tag them according to matching specific set of conditions using conditional field assignment ( | eval something=if( [...] ))