How to use stats to join 2 indexes based on 1 field

[leonheart78]

Currently I have 2 indexes:
Index A contains ProgramID, User
Index B contains ID, Machine

I would like to use stats to join these 2 indexes based on ProgramID and ID, and get a table to reflect the User, Machine, and the Percent count of the Machine in the record.

How can I do it?
Thank you.

[mtranchita]

Would something like this get you what you are looking for?
index="Index A" | fields ProgramID, User | rename ProgramID AS ID | search [ index="Index B" | fields ID, Machine] | stats c(ID) as ID by User, Machine

[Runals · Accepted solution]

I'm not quite sure what you mean by percent count of the machine. To join the data and assuming ID = ProgramID I'd probably do something like

index=A OR index=B sourcetype=... OR sourcetype=... | foreach User Machine [eval <<FIELD>> = coalesce(<<FIELD>>, "-")] | eval ProgramID = coalesce(ProgramID, ID) | stats count by Machine, User, ProgramID

I'm first making sure the fields User and Machine have data since they will be used in the stats command. Then I'm collapsing ProgramID and ID (based on my assumptions). Lastly a simple stats command to get the number of events per each Machine, User & ProgramID combo. If you want percent of something related to machine (ie how often does this machine or program show up relative to the result set) you probably would then need to use an eventstats command.

[MuS]

Nice answer! Just to add a link to another answer which could be helpful in this case https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

cheers, MuS

[wrangler2x]

And here is a Splunk blog entry on coalesce: http://blogs.splunk.com/2014/03/21/search-command-coalesce/