topic Re: Syntax help to get distinct results from two queries from two different timeframes in Splunk Search

Syntax help to get distinct results from two queries from two different timeframes

JHorst — Fri, 07 Oct 2022 15:28:24 GMT

Hello all,

I would like a single splunk query that does the following:

Query "APP_A" for a specific log message, returning two values (key, timestamp)
Query "APP_B" for a specific log message, returning two values (key, timestamp)
Data takes roughly five min to process from APP_A to APP_B. So, to ensure I am getting the most accurate view of the data as possible, I want to offset the queries by 600 seconds. This likely means configuring query one to look back five min
Produce a table / report that lists ONLY the keys that are distinct to each table

EX:

QUERY 1 RESULTS

a	1665155553
b	1665155554
c	1665155555
d	1665155556

QUERY 2 RESULTS

a	1665155853
c	1665155854
d	1665155855
e	1665155856

OVERY ALL RESULTS (what I really want)

b	1665155554
e	1665155856

For better or worse, here is what I have so far...

| set diff

[search
index="<REDACTED>"
cf_org_name="<REDACTED>"
cf_app_name="<REDACTED>"
event_type="LogMessage" "msg.logger_name"="<REDACTED>" |
rex field="msg.message" "<REDACTED>" |
table masterKey timestamp |
]

[search
index="<REDACTED>"
cf_org_name="<REDACTED>"
cf_app_name="<REDACTED>"
event_type="LogMessage" "msg.logger_name"="<REDACTED>" |
table masterKey timestamp |
]

My syntax is for sure off, because the diff is not producing distinct results. Also, I haven't tried to tackle the time off set problem yet. Any help would be greatly appreciated. Thanks in advanced.

Re: Syntax help to get distinct results from two queries from two different timeframes

johnhuang — Fri, 07 Oct 2022 15:48:42 GMT

Could you provide some sample raw data from app_a and app_b? Do they exist in the same index?

Re: Syntax help to get distinct results from two queries from two different timeframes

yuanliu — Sat, 08 Oct 2022 06:20:22 GMT

set is an expensive operation. Use stats. Assuming that you want to look back 30 minutes, and that cf_app_name can have APP_A and APP_B, where APP_A should have a 5-minute lookback. Do something like

(index="<REDACTED>" cf_org_name="<REDACTED>" cf_app_name="APP_A" earliest=-35m latest=-5m event_type="LogMessage" "msg.logger_name"="<REDACTED>") OR (index="<REDACTED>" cf_org_name="<REDACTED>" cf_app_name="APP_B" earliest=-30m latest=now event_type="LogMessage" "msg.logger_name"="<REDACTED>") | rex field="msg.message" "<REDACTED>" | stats values(cf_app_name) by key timestamp | where mvcount('values(cf_app_name)') = 1

Hope this helps.