topic Re: Lookup table with two primary keys in Splunk Search

Lookup table with two primary keys

tyronetv — Mon, 28 Sep 2020 15:13:10 GMT

My company uses ConnectDirect (C:D) as a tool for file transfer. Within the connect direct logs the hosts are referred to by the variables PNOD and SNOD where PNOD is the primary node and SNOD is the secondary node.

Because C:D can either receive or send a host can be either PNOD or SNOD.

The hosts are configured in a file such that:

nodename Verbose_name
ACCT1 "My Client's LA Site"
ACCT2 "Internal PHX Site"
etc...

Therefore PNOD can be one of the local machine ID or the remote machine and the same for SNOD.

The assumption is that when the SNOD is the local machine it is inbound traffic. When the PNOD is the local machine it is outbound traffic.

Now, my question: Is it possible to have one lookup file that works for both SNOD and PNOD providing the Verbose name in reports.

I.e --

File_1 Received from My Clients LA Site
File_2 Sent to My Clients LA Site

The only difference is in the first one it is PNOD and in the second one it is SNOD.

Is this easy and I am just over looking it?

Currently I have two lookup files, exactly the same, except for one is headered "SNOD,Account" and the second is headered "PDOD,SAccount".

Re: Lookup table with two primary keys

alacercogitatus — Wed, 06 Nov 2013 14:10:21 GMT

Need a little more clarification. Do you want a single lookup? Are the Accounts different on different nodes? So can NODE1 have 2 Accounts?

Re: Lookup table with two primary keys

richgalloway — Wed, 06 Nov 2013 14:22:51 GMT

It seems that PNODs and SNODs are just nodes so why not have a single lookup file with "Node" and "Account" fields? Then do your lookups using '... | lookup nodes.csv Node AS PNOD OUTPUT Account | lookup nodes.csv Node AS SNOD OUTPUT Account AS SAccount | ...'.

Re: Lookup table with two primary keys

tyronetv — Wed, 06 Nov 2013 14:24:25 GMT

I have five machines providing the C:D service. Some provide service over private circuits and one provides 'public' access.

So the PNOD can be one of my five machines with the SNOD being one of 145 clients OR PNOD can be someone in a subset of my clients with SNOD being one of my five machines.

I am generating reports per-machine currently but my long term goal is one report that shows the file movement within the organization.

Re: Lookup table with two primary keys

tyronetv — Wed, 06 Nov 2013 14:34:55 GMT

Here's my current search generating a report using my two lookup tables. Luckily C:D logs are all key/value pairs.

sourcetype=connectdirect host = HOST_NAME ( RECI=CTRC ) |
transaction keepevicted=true PNUM RECI STAR STOP |
eval VOLUME=(SBYX/1048576)|
search VOLUME>0 |
table STAR PNUM SAccount Account SFIL DFIL

Are you saying I can do :

table STAR PNUM PNOD SNOD SFIL DFIL |lookup nodes.csv Node AS PNOD OUTPUT Account | lookup nodes.csv Node AS SNOD OUTPUT Account AS SAccount

Re: Lookup table with two primary keys

richgalloway — Wed, 06 Nov 2013 14:42:10 GMT

I don't see where you currently use your two lookup tables.
The lookup statements should precede the table statement. lookup ... | table STAR PNUM PNOD Account SNOD SAccount SFIL DFIL

Re: Lookup table with two primary keys

alacercogitatus — Wed, 06 Nov 2013 14:43:39 GMT

Since the Node Names would be different between your 5 and your 145 clients, you can use a single lookup.

sourcetype=connectdirect host=HOST_NAME (RECI=CTRC) | eval nodename = coalesce(PNOD,SNOD) | eval type = if(isnotnull(PNOD),"primary","secondary") | lookup nodes.csv Node AS nodename OUTPUT Account | eval {type}_account = Account | transaction keepevicted = true PNUM RECI STAR STOP | eval VOLUME = (SBYX/1048576) | search VOLUME>0 | table STAR PNUM PNOD SNOD SFIL DFIL primary_account secondary_account

this is a little more verbose than you were probably expecting, but it limits the number of reads to the file system and does them before the transaction.

Re: Lookup table with two primary keys

alacercogitatus — Wed, 06 Nov 2013 14:48:59 GMT

Additionaly, I'm on the Efnet #splunk IRC for direct questions.

Re: Lookup table with two primary keys

tyronetv — Wed, 06 Nov 2013 15:44:40 GMT

My original search uses automatic lookups. Hence SAccount and Account vs PNOD and SNOD