Splunk question on stats/multiple stats

[asarolkar]

Hi,

I have two sourcetypes with disparate pieces of information that i want to bring together.

Note that there are MULTIPLE MODULES per ORG

sourcetype="orglog" -> OrgName, OrgAccountNumber, OrgModuleNum
sourcetype="modulelog" -> ModuleNo, ModuleType

I am doing a coalesce which works just fine for me ( I recently discovered that a coalesce is the closest splunk will ever get to a full outer join - which to my understanding the JOIN type=outer command does not do)

sourcetype="orglog" OR sourcetype="modulelog" | eval ModuleID=coalesce(OrgModuleNum,ModuleNo) | dedup ModuleID | *

What if i want to generate a report in this format.

OrgName | OrgAccountNumber | ModuleType 1 | ModuleType 2 | ModuleType3 | ModuleType4

  ACME INC          123            12        99              1384              232
  FAKE CORPINC      6673           0         199             787               101

I know that what I want will not come from a simple stats (that some complicated god-knows-what operation is needed here). Bear in Mind, we are only interested in counting the number of modules (NOT INTERESTED in the actual ModuleID)

Am I going about this the wrong way ? I am not lazy, just trying to figure out the best possible solution (not necessarily the most elegant one)

[rtadams89 · Accepted solution]

I'm not sure I am clear on what it is you are doing. The sourcetype "orglog" has fields "OrgName", "OrgAccountNumber", and "OrgModuleNum". The sourcetype "modulelog" has fields "ModuleNo" and "ModuleType". The only thing in common between the events is "OrgModuleNum" and "ModuleNum" which are really jsut differnt names for the same type of value. If that is correct, you do want to do a join, but first you should rename the fields so that the field that represent the module number has the same field name for both sourcetypes.

I would think something like this would work:

sourcetype=orglog | eval ModuleID=OrgModuleNum | join ModuleID [search source=modulelog | eval ModuleID=ModuleNo] | stats count(ModuleType) by OrgName, ModuleType

However, if all you want is the number of modules for each OrgName, I don't get why you can't just do:

sourcetype=orglog | stats count(OrgModuleNum) by OrgName, OrgModuleNum

[asarolkar]

Thought of that too.

Unfortunately lookup table is not applicable here (both sourcetypes are created on the fly)

[rtadams89]

Glad that worked. I have edited the answer to include the required explicit "search" command. On a side note, if the relationship between ModuleNo and ModuleType doesn't change, you might want to put this in data in a lookup table instead of indexing it as events. If you need help with this, check the documentation on lookup tables or post another question.

[asarolkar]

Actually, I take that back.

That worked like a charm !

I just added a "search sourcetype" in the inner sub-search.

MAN ! You just saved me a ton of grief ! WOHOOO

[asarolkar]

The FORMER is true.
The only thing in common between the events is "OrgModuleNum" and "ModuleNum" which are really just differnt names for the same type of value. THAT IS CORRECT

If I run your search it does not give me anything from modulelog. Just FYI