topic Re: How to "join" two different searches with no common fields? in Splunk Search

How to "join" two different searches with no common fields?

CarmineCalo — Thu, 18 Jan 2018 13:19:47 GMT

Splunkers!

I need to join the follow inputlookup + event searche in order to have, for each AppID, the full set of month buckets given from the time range picker
Example:

Search 1 (Fromm inputlookup):
App1
App2
...

Search 2 (from index search)
Month 1
Month 2
...

Desired outcome:

App1 Month1
App1 Month2
App1 ...
App2 Month1
App2 Month2
App2 ...
... ...

Here the code for the two searches

Search 1

| inputlookup DOM_ApplicationCatalogue
| search Status="Production"

| stats count by ApplicationID

Search 2
| search index=Incidents
| dedup id_inc
| timechart span=1mon count
| eval datemonth_year=strftime(_time,"%Y-%m")
| fields count datemonth_year]

Any help?

Tks!
Carmine

Re: How to "join" two different searches with no common fields?

mayurr98 — Thu, 18 Jan 2018 13:40:09 GMT

you can try something like

| inputlookup DOM_ApplicationCatalogue 
| search Status="Production" 
| stats count by ApplicationID 
| appendcols 
    [ search index=Incidents 
    | dedup id_inc 
    | timechart span=1mon count 
    | eval datemonth_year=strftime(_time,"%Y-%m") 
    | fields count datemonth_year]

let me know if this helps !

Re: How to "join" two different searches with no common fields?

CarmineCalo — Thu, 18 Jan 2018 14:04:47 GMT

No, unfortunately it's not working...

It generate something like (hyp that Month = (Month 1, Month 2)

App1 Month 1
App2 Month 2
App3
App4
...

Carmine

Re: How to "join" two different searches with no common fields?

mayurr98 — Thu, 18 Jan 2018 14:28:35 GMT

if you are interested in just desired outcome then you can try something like this I may be wrong...but you will not be able to show count in this because logically linking count is not possible i think

app1 month1
app1 month2
app2 month1
app2 month2
..and so on

| inputlookup DOM_ApplicationCatalogue 
| search Status="Production" 
| stats count by ApplicationID 
| fields ApplicationID 
| appendcols 
    [ search index=Incidents 
    | dedup id_inc 
    | timechart span=1mon count 
    | eval datemonth_year=strftime(_time,"%Y-%m") 
    | fields datemonth_year ] 
| stats list(ApplicationID) as ApplicationID list(datemonth_year) as datemonth_year 
| mvexpand ApplicationID 
| mvexpand datemonth_year

let me know if this helps!

Re: How to "join" two different searches with no common fields?

CarmineCalo — Thu, 18 Jan 2018 14:35:21 GMT

Now It works!
Tks!

Re: How to "join" two different searches with no common fields?

CarmineCalo — Thu, 18 Jan 2018 15:00:39 GMT

Only one additional thing...

list(ApplicationID) create a field with "only" 100 value inside (my list of APpID is 4k+!)
How can i increase the number of values to listed?
Unfortunately "limit" option not works with stats...

Re: How to "join" two different searches with no common fields?

mayurr98 — Thu, 18 Jan 2018 15:25:04 GMT

Hey use values(ApplicationID) as ApplicationID

Re: How to "join" two different searches with no common fields?

CarmineCalo — Thu, 18 Jan 2018 18:27:29 GMT

Great! It works now 🙂