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)
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
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
Thought of that too.
Unfortunately lookup table is not applicable here (both sourcetypes are created on the fly)
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.
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
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