topic How to populate column in results using two indexes? in Splunk Search

How to populate column in results using two indexes?

splunker1981 — Mon, 11 Mar 2019 15:52:45 GMT

Hello all -

Trying to figure out how to return the table below when using two index/sourcetypes. I'd like to do some eval by index and then check whether the respective index has an event so I can add yes | no to the respective column. The common field would be IP and either the IP will have both or one or the other defined.

indexA
ip=1.1.1.1 app-id=3.09
ip=3.3.3.3 app-id=2.11

indexB
ip=1.1.1.1 rel=release39
ip=2.2.2.2 rel=release12

Desired Results
IP        Release    App-id
1.1.1.1   yes           yes
2.2.2.2   yes          no
3.3.3.3   no            yes

Re: How to populate column in results using two indexes?

chrisyounger — Mon, 11 Mar 2019 15:58:10 GMT

Does this do what you need?

index=indexA OR index=indexB 
| stats values(rel) as Release values(app-id) as "App-id" by ip
| rename ip as IP

Re: How to populate column in results using two indexes?

gcusello — Mon, 11 Mar 2019 16:13:16 GMT

Hi splunker1981,
if you want to list all the IPs where there's at least Release=yes or App-id=yes, try something like this:

index=indexA OR index=indexB
| stats dc(rel) AS rel dc(app-id) AS app-id BY ip
| eval Release=if(rel>0,"yes","not"), App-id=if(app-id>0,"yes","not")
| rename ip AS IP
| table IP Release App-id

If you could have also both Release=not and App-id=not, you have to try something like this

index=indexA OR index=indexB
| stats dc(rel) AS rel dc(app-id) AS app-id BY ip
| eval Release=if(rel>0,"yes","not"), App-id=if(app-id>0,"yes","not")
| append [  search index=indexA NOT app-id=* | eval App-id="not" | fields ip App-id]
| append [  search index=indexB NOT rel=* | eval Release="not" | fields ip Release ]
| stats values(Release) AS Release values(App-id) AS App-id BY ip
| rename ip AS IP
| table IP Release App-id

Bye.
Giuseppe

Re: How to populate column in results using two indexes?

gcusello — Wed, 31 Jul 2019 12:52:26 GMT

Hi splunker1981,
if you're satisfied by this answer, please accept and/or upvote it.

Bye, see next time.
Giuseppe