topic Re: 2 different fields from different csv. in Getting Data In

2 different fields from different csv.

mrccasi — Thu, 09 Mar 2017 06:48:19 GMT

Hi. can you please help me in making a search command for the below result?
"resource name" is a field name of my csv file then "eid" is a field name of another csv file. I would like to compare the values of both field and if they are the same the value in "codp" should be 1. if they dont match the value is 0.

kindly help me.

resource name eid codp
mary.rose.c.casi mary.rose.c.casi 1
ley 0
ghi 0

Re: 2 different fields from different csv.

Richfez — Thu, 09 Mar 2017 12:35:11 GMT

sourcetype1 OR sourcetype2
| rename "resource name" AS eid
| transaction maxspan=5m eid
| eval codp=if(eventcount>1,1,0)

That uses transaction, which could be improved from a performance standpoint with stats (especially in a clustered environment, not so much in a single machine one). Not knowing how you will put this to use I wasn't sure what would be easier, but I think transaction is more clear from an example usage. Adjust maxspan=X to as short as will work (keeps it efficient).

You may need the first line to be something like

(index=X AND eid=*) OR (sourcetype=Y AND index=Z)

It's a list of the criteria you need to show the first set of data ORed with the criteria you need to show the second set of data so they all show up together.

Re: 2 different fields from different csv.

mrccasi — Tue, 29 Sep 2020 13:09:48 GMT

hi rich7177, thanks for your time in answering my question. It does not return the results I want. I used lookup table for the 1st csv file.
this is my search command. Can you tell me what is wrong on the command? Thanks. Newbie splunk user here 🙂

|inputlookup cams.csv
| join type=left EID
[search index="cams_2" sourcetype="cams2_csv" "Capability Cycle 9" = "Data & Analytics" OR "LocalCapabilityRollUp Cycle 9" = "Data & Analytics" OR "Capability Cycle 10" = "Data & Analytics" OR "LocalCapabilityRollUp Cycle 10" = "Data & Analytics"]
| fields "Resource Name"
| dedup "Resource Name"
| rename "Resource Name" as EID
| eval "CoDp Mapping"=if(isnotnull(EID),1,0)

the result it returns are all 1, though not every eid has a match in resource name.

Re: 2 different fields from different csv.

Richfez — Fri, 10 Mar 2017 12:07:41 GMT

OK, so I think I see some things wrong, but could you paste in one or two events of each type? One from the inputlookup and one from the cams_2 sourcetype of the appropriate type? I think this will be easier than guessing... 🙂

Be sure to paste them in using the code button (the 101010 button at the top of the editor window).

Thanks,
-Rich

Re: 2 different fields from different csv.

mrccasi — Wed, 15 Mar 2017 01:40:02 GMT

Here are the events in inputlookup

Capability  FY         Resource Name
Microsoft   FY15    arnold.b.o.alegarbes
Microsoft   FY15    jennifer.garcia

and here are the events for cams_csv

SAP ID          Personnel Name          EID
10027   Arnold Alegarbes    arnold.b.o.alegarbes
10816   Maricar Reyes           maricar.reyes

Re: 2 different fields from different csv.

Richfez — Tue, 29 Sep 2020 13:17:25 GMT

So, if you use a lookup instead of an inputlookup, you might be able to do this more easily. Please excuse me only giving pseudo-SPL ... There will be a little homework but if you get stuck I or others of us can probably help. I may have the two data types backwards, so you might need to flop it. (You say one of the above two is the inputlookput, which is named cams.csv, and the other is for cams_csv so I'm not sure which is actually which, assuming cams_csv is sourcetype cams2_csv)

search index="cams_2" sourcetype="cams2_csv" "Capability Cycle 9" = "Data & Analytics" OR "LocalCapabilityRollUp Cycle 9" = "Data & Analytics" OR "Capability Cycle 10" = "Data & Analytics" OR "LocalCapabilityRollUp Cycle 10" = "Data & Analytics"
| lookup cams.csv "Resource Name" AS EID OUTPUTNEW FY Capability

At this point you should have individual events that have BOTH "sets" of records in them. At least probably. 🙂

So, to the above, you can do things. Like, for only records that have FY, add to the end | search FY=*. Doing that ought to then only give you records where they match up (because if they don't match, that will filter it out). Or you could ...

| eval hasTwoRecords=if(isnull(FY),0,1)

Which, unless I have my 1/0 backwards, should give you a new field hasTwoRecords which when set to 1 means you have both records, if not then there was no corresponding cams.csv record.

The sky's the limit, hopefully this will get your juices flowing.

If you need more help, please "redescribe" which is the inputlookup and which is the events in the index. 🙂

Re: 2 different fields from different csv.

mrccasi — Mon, 20 Mar 2017 02:16:46 GMT

Hi rich7177, thank you so much for helping me and giving me ideas 🙂 It helps me a lot.

thank you!

Re: 2 different fields from different csv.

Richfez — Fri, 24 Mar 2017 14:43:45 GMT

I am glad I could help. 'if helpful enough, could you please mark this "Accepted" so others know the solution is in this answer somewhere?

Thanks!