Splunk Search

Any way to combine these records?

tkwaller_2
Communicator

SO I understand WHY I get the results I get but I am having a difficult time, most likely due to me, getting the results I want. So heres whats happening, I have a search that returns survey data. Each record returned has a bunch of data including a single QuestionID and QuestionAnswer. 1 question and answer per record. Most surveys have around 30 or so questions so 30 or so records per survey. I join in some other data to this. I get a table like this

rrname  md_id   data_det_q1 data_det_q2 data_det_q3 PII QuestionID  QuestionAnswer  count
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR01    Temp Name, Inc Internal 1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR01a   ['Other (please describe in Additional Comments below)']    1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR01b   1- 10,000   1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR01c   Daily   1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR02    Temp Name, Inc Internal 1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR02a   ['Other (please describe in Additional Comments below)']    1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR02b   1- 10,000   1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR02c   Daily   1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR03    Yes 1
Temp Name, Inc. Not Found 07/24/2018    0000-0003-3609  TRUE    TRUE    TRUE    FALSE   RR04    Yes 0

I want to view this data horizontally in columns so I put it into pivot. So instead I get one row of all data split columns by QuestionID and column values of QuestionAnswer
so I get a pivot table like(this is 1 row of the table):

RR_Submission   ID  Created_By  Created_On_Behalf_Of    Temp Name_LOB   Temp Name_Division  Temp Name_Operating_Company Risk_Determination_Qu_1 Risk_Determination_Qu_2 Risk_Determination_Qu_3 Temp Name_Enterprise_Architect_Primary_Contact  Temp Name_IT_Demand_Primary_Contact Temp Name_Procurement_Primary_Contact   Temp Name_Project_Manager_Primary_Contact   Temp Name_Security_Ambassador   _Suite_Contract_ID  Suite_Vendor    Other_Contact_1 Other_Contact_2 Other_Contact_3 Other_Vendors   Primary_Vendor_Contact_Email    Primary_Vendor_Contact_Name Primary_Vendor_Contact_Phone    Risk_Rank_Submit_Status Secondary_Vendor_Contact_Email  Secondary_Vendor_Contact_Name   Secondary_Vendor_Contact_Phone  RR01    RR01a   RR01b   RR01c   RR02    RR02a   RR02b   RR02c   RR03    RR04    RR05    RR06    RR07    RR08    RR08a   RR08b   RR08c   RR09    RR10    RR10a   RR11    RR12    RR13    RR13a   RR13b   RR14    RR15    RR16    RR17    RR18    RR19    RR20    RR21
Temp Name, Inc 06/21/2018   0000-0000-1904  tasfasdf@srg.com    NA  NA  NA  NA  FALSE   TRUE    FALSE   NA  NA  Todd  Peterman  NA  NA  NA  NA  NA  NA  NA  EPRI    gsergfsdft@srr.com  Ben Jammin Phillips 555-555-2728    Complete    NA  NA  NA  Temp Name Internal  ['Other (please describe in Additional Comments below)']    1- 10,000   Monthly Temp Name Internal  ['Other (please describe in Additional Comments below)']    10,001 - 100,000    Monthly Yes No  Yes No  No  No              N/A No      > $1,000,000 No  No          Less than 4 Weeks   No  No  No  No  3-Jan   51% - 100%  1 Year - 5 Years

there are 2 fields I need to add to this table but when I do I get duplicate records because of how the data works. I have 2 fields Inbound and Outbound that are

Inbound = if (QuestionID = RR01a and QuestionAnswer="*PII*" ) then list the QuestionAnswer

Outbound if(QuestionID = RR02a and QuestionAnswer="*PII*") then list the QuestionAnswer

So what happens is all the surveys that dont have either one of those = true I get one line like I want but where those fields DO = an answer like PII I get 3 records: 1 record for Inbound, One record for Outbound and 1 record for the rest of the survey results. Heres an example:

RR_Submission   rg_Risk_Rank    Created_By  Created_On_Behalf_Of    AllTempiD_LOB   AllTempiD_Division  AllTempiD_Operating_Company Risk_Determination_Qu_1 Risk_Determination_Qu_2 Risk_Determination_Qu_3 AllTempiD_Enterprise_Architect_Primary_Contact  AllTempiD_IT_Demand_Primary_Contact AllTempiD_Procurement_Primary_Contact   AllTempiD_Project_Manager_Primary_Contact   Suite_Contract_ID   Suite_Vendor    Inbound Outbound    Other_Contact_1 Other_Contact_2 Other_Contact_3 Other_Vendors   Primary_Vendor_Contact_Email    Primary_Vendor_Contact_Name Primary_Vendor_Contact_Phone    Secondary_Vendor_Contact_Email  Secondary_Vendor_Contact_Name   Secondary_Vendor_Contact_Phone  RR01    RR01a   RR01b   RR01c   RR02    RR02a   RR02b   RR02c   RR03    RR04    RR05    RR06    RR07    RR08    RR08a   RR08b   RR08c   RR09    RR10    RR10a   RR11    RR12    RR13    RR13a   RR13b   RR14    RR15    RR16    RR17    RR18    RR19    RR20    RR21
AllTempiD, Inc. - 9872345 0000-0000-1308    twall@sec.com   dbcooper@sec.com    BD and Delivery NA  NA  FALSE   TRUE    FALSE   NA  NA  Don Johnson  John Potts NA  NA  None    None    NA  NA  NA  NA  joe.frank@sec.com   Joe Frank   555-555-2356    NA  NA  NA  AllTempiD Confidential Special Handling     1- 10,000   Daily   AllTempiD Confidential Special Handling     1- 10,000   Daily   No  No  No  No  No  No              No Mobile Solution Available    No      > $1,000,000 No  No          Less than 5 Days    Yes Yes No  No  10-Apr  51% - 100%  1 Year - 5 Years

AllTempiD, Inc. - 9872345 0000-0000-1308    twall@sec.com   dbcooper@sec.com    BD and Delivery NA  NA  FALSE   TRUE    FALSE   NA  NA  Don Johnson John Potts  NA  NA  None    ['PII'] NA  NA  NA  NA  joe.frank@sec.com   Joe Frank   555-555-2356    NA  NA  NA                      ['PII']

AllTempiD, Inc. - 9872345  0000-0000-1308   twall@sec.com   dbcooper@sec.com    BD and Delivery NA  NA  FALSE   TRUE    FALSE   NA  NA  Don Johnson John Potts  NA  NA  ['PII'] None    NA  NA  NA  NA  joe.frank@sec.com   Joe Frank   555-555-2356    NA  NA  NA      ['PII']

Any ideas how I can get this to not split the records? I have tried all sorts of things, like a single field with a case statement but the issue is the Inbound/Outbound fields.

Thanks!

0 Karma

kmaron
Motivator

it would be easier to help if you shared your query

0 Karma

tkwaller_2
Communicator

I'll wait one more day and then take this down, since no answers

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...