topic Re: Combining 3 queries output to produce table in Splunk Search

Combining 3 queries output to produce table

jjohn149 — Wed, 07 Aug 2024 10:21:11 GMT

|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.

Re: Combining 3 queries output to produce table

yuanliu — Wed, 07 Aug 2024 05:18:46 GMT

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.

Re: Combining 3 queries output to produce table

gcusello — Wed, 07 Aug 2024 06:57:24 GMT

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

Re: Combining 3 queries output to produce table

jjohn149 — Wed, 07 Aug 2024 23:55:21 GMT

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

Re: Combining 3 queries output to produce table

jjohn149 — Wed, 07 Aug 2024 23:55:58 GMT

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

Re: Combining 3 queries output to produce table

gcusello — Thu, 08 Aug 2024 06:33:57 GMT

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