Combining 3 queries output to produce table

[jjohn149]

|union

[ search index=osp source=xxx EVENT_TYPE=xxx  EVENT_SUBTYPE=xxx
field1=* field3=xxx field4=""
| eval DATE = strftime(strptime(xxx, "%Y%m%d"), "%Y-%m-%d")
| stats latest(source) as example1  by field5 field6 DATE]

[ search index=osp source=xxx EVENT_TYPE=xxx  EVENT_SUBTYPE=xxx
field1=* field3=xxx  field3=xxx field4=""
| eval DATE = strftime(strptime(xxx, "%Y%m%d"), "%Y-%m-%d")
| stats latest(source) as example2  by field5 field6 DATE]

[ search index=osp source=xxx EVENT_TYPE=xxx  EVENT_SUBTYPE=xxx
field1=* field3=xxx NOT field3=xxx field4=""
| eval DATE = strftime(strptime(xxx, "%Y%m%d"), "%Y-%m-%d")
| stats latest(source) as example3  by field5 field6 DATE]

| stats count(example1) as "example 1", count(example2) as "example 2", count(example3) as "example 3"   by DATE

The data is populating correctly for example 1 and example 3, individually, and if I just use two queries. However, I need all 3 queries for my data but data is missing from example 2.

[gcusello]

Hi @jjohn149 ,

Maybe it's an impression, but the searches seem the same, probably the values ​​in the conditions are different, but I would put the three searches in one, thus also avoiding the limit of 50,000 results of the subsearch; so in my example I will use condition1, condition2 and condition3 to adapt to your real need:

index=osp source=xxx EVENT_TYPE IN (event_type1, event_type2, event_type3)  EVENT_SUBTYPE IN (event_subtype1, event_subtype2, event_subtype3)
field1=* field3 IN (field31, field32, field33) field4=""
| eval DATE=strftime(strptime(_time, "%Y%m%d"), "%Y-%m-%d")
| stats 
     latest(eval(if(field3=field31))),source,"") AS example1
     latest(eval(if(field3=field32))),source,"") AS example2
     latest(eval(if(field3=field33))),source,"") AS example3
     by field5 field6 DATE

Ciao.

Giuseppe

[jjohn149]

Hey, Maybe this is a better example.

|union

index=osp source=xxx EVENT_TYPE=xxx EVENT_SUBTYPE=xxx
| search PLNF=* REN=INT OKELS=""
| eval DATE = strftime(strptime(BADAT, "%Y%m%d"), "%Y-%m-%d")
| stats count as example1 by FNHB FNPO DATE
| eval SourceType="example 1"

[
search index=osp source=xxx EVENT_TYPE=xxx EVENT_SUBTYPE=xxx
PLNF=* REN=INT HTSZ=R OKELS="" EHUH=FIERY
| eval DATE = strftime(strptime(BADAT, "%Y%m%d"), "%Y-%m-%d")
| stats count as example2 by FNHB FNPO DATE
| eval SourceType="example 2"
]
[
search index=osp source=xxx EVENT_TYPE=xxx EVENT_SUBTYPE=xxx
PLNF=* REN=INT HTSZ=R OKELS="" NOT EHUH=FIERY
| eval DATE = strftime(strptime(BADAT, "%Y%m%d"), "%Y-%m-%d")
| stats count as example3 by FNHB FNPO DATE
| eval SourceType="example 3"
]

| stats count(example1) as "example 1" count(example2) as "example 2" count(example3) as "example 3" by DATE

I didn't quite understand how you were filtering the search fields on that second line

[jjohn149]

Also, when I enter:

| eval start_date=relative_time(now(), "-90d@d")
| eval end_date=now()
| where DATE >= start_date AND DATE <= end_date

below the stats count command line at the end, the query does not filter my data 

[gcusello]

Hi @jjohn149 ,

please try something like this:

index=osp source=xxx EVENT_TYPE=xxx EVENT_SUBTYPE=xxx PLNF=* REN=INT OKELS=""
| eval example=case(
	HTSZ="R" AND NOT EHUH="FIERY", "example 3",
	HTSZ="R", "example 2",
	true(), "example 1"
	)
| eval DATE = strftime(strptime(BADAT, "%Y%m%d"), "%Y-%m-%d")
| stats 
   count(eval(example="example 1")) AS example1_count
   count(eval(example="example 2")) AS example2_count
   count(eval(example="example 3")) AS example3_count
   BY FNHB FNPO DATE
| stats 
	sum(example1_count) AS "example 1" 
	sum(example3_count) AS "example 2"
	sum(example3_count) AS "example 3"
	BY DATE

Ciao.

Giuseppe

[yuanliu]

As everyone will tell you, you are better off not using union and join, especially as your mock code suggests their similarity.

The best way to get help is to follow these golden rules that I call four commandments:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at.
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.