Splunk Search

How Do I Change Append to Multisearch

IRHM73
Motivator

Hi, I wonder whether someone may be able to help me please.

I'm using the following query to create a table in my dashboard:

index=main auditSource=frontend auditType=ExitSurvey detail.manageList!="None"
| append [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" manageList NOT auditSource=* 
    | spath 
    | search auditType=ExitSurvey detail.manageList!="None"] 
| replace /agent/survey With "Manage List" 
| contingency tags.path detail.manageList  
| append [search index=main auditSource=agent-frontend auditType=ExitSurvey detail.viewPayments!="None" 
    | append [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" viewPayments NOT auditSource=* 
        | spath 
        | search auditType=ExitSurvey detail.viewPayments!="None"] 
| replace /agent/survey With "View Payments" 
| contingency tags.path detail.viewPayments ] 
| search tags.path!="TOTAL" 
| rename tags.path TO "Question"  
| fields Question, 1, 2, 3, 4, 5 
| addtotals label=Total 
| rename 1 TO "Very Dissatisfied", 2 TO "Dissatisifed", 3 TO "Neither", 4 TO "Satisfied", 5 TO "Very Satisfied"

The query works, but I'm now coming up against the problem of the 50,000 row limit because of the 'append' command.

I'm now looking to change this and incorporate the 'multisearch' command instead of the 'append'.

I can get so far a show below,

| multisearch
[ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.manageList!="None" | replace /agent/survey With "Manage List"]
[ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" manageList NOT auditSource=* 
    | spath 
    | search auditType=ExitSurvey detail.manageList!="None"]
[ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.viewPayments!="None" | replace /agent/survey With "View Payments"]
[ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" viewPayments NOT auditSource=* 
        | spath 
        | search auditType=ExitSurvey detail.viewPayments!="None"]
| contingency tags.path detail.manageList
| contingency tags.path detail.viewPayments
| search tags.path!="TOTAL" 
| rename tags.path TO "Question"  
| fields Question, 1, 2, 3, 4, 5 
| addtotals label=Total 
| rename 1 TO "Very Dissatisfied", 2 TO "Dissatisifed", 3 TO "Neither", 4 TO "Satisfied", 5 TO "Very Satisfied"

but the problem I have is with these rows:

| replace.....
| contingency....

I just wondered whether someone could possible look at this please and offer some guidance on how I may be able to accomplish this.

Many thanks and kind regards

Chris

0 Karma
1 Solution

woodcock
Esteemed Legend

I am not proposing this as something that you should always do but here is a trick that will allow you to switch to multisearch (which is not limited), appendpipe (which is not limited) + inputlookup (limited to 1000000000) to get out of this problem:

The first step is to use multisearch to save off each dataset to it's own file:

multisearch
   [ search index=main auditSource=frontend auditType=ExitSurvey detail.manageList!="None"
         | outputcsv MySearchTemp1.csv ]
   [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" manageList NOT auditSource=* 
         | spath 
         | search auditType=ExitSurvey detail.manageList!="None"
         | outputcsv MySearchTemp2.csv ] 
   [ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.viewPayments!="None"
        | outputcsv MySearchTemp3.csv ]
   [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" viewPayments NOT auditSource=* 
        | spath 
        | search auditType=ExitSurvey detail.viewPayments!="None"
        | outputcsv MySearchTemp4.csv ]

Next, we drop all the events:

| where isnotnull(ThisFieldCannotExistSoThisDropsAllEventsSoFar)

Lastly, we pull the data back in with inputcsv and switch append to appendpipe:

| appendpipe [ |inputcsv MySearchTemp1.csv ]
| appendpipe [ |inputcsv MySearchTemp2.csv ] 
| replace /agent/survey With "Manage List" 
| contingency tags.path detail.manageList  
| appendpipe [ |inputcsv MySearchTemp3.csv
    | appendpipe [ |inputcsv MySearchTemp4.csv ] 
    | replace /agent/survey With "View Payments" 
    | contingency tags.path detail.viewPayments ] 
| search tags.path!="TOTAL" 
| rename tags.path TO "Question"  
| fields Question, 1, 2, 3, 4, 5 
| addtotals label=Total 
| rename 1 TO "Very Dissatisfied", 2 TO "Dissatisifed", 3 TO "Neither", 4 TO "Satisfied", 5 TO "Very Satisfied"

So long as you are not using a multivalued fields, this should work fine for you ( outputcsv has the mostly unknown and, until recently undocumented, impact of calling nomv on all multivalued fields). If you switch from using files to using a KV Store, then this negative side-effect does not happen.

View solution in original post

0 Karma

IRHM73
Motivator

Hi for those of you who may be interested, my colleague came up with an alternative solution.

| multisearch
[ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.manageList!="None"
  | eval Question="Managed List"
  | rename detail.manageList as rating
  | fields Question rating]
[ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" manageList NOT auditSource=* 
  | spath 
  | search auditType=ExitSurvey detail.manageList!="None"
  | eval Question="Managed List"
  | rename detail.manageList as rating
  | fields Question rating]
[ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.viewPayments!="None"
  | eval Question="View Payments"
  | rename detail.viewPayments as rating
  | fields Question rating]
[ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" viewPayments NOT auditSource=* 
   | spath 
   | search detail.viewPayments!="None"
   | eval Question="View Payments"
   | rename detail.viewPayments as rating
   | fields Question rating]
| stats count by Question rating
| xyseries Question rating count
| rename 1 TO "Very Dissatisfied", 2 TO "Dissatisifed", 3 TO "Neither", 4 TO "Satisfied", 5 TO "Very Satisfied"
| addtotals

Regards

Chris

0 Karma

woodcock
Esteemed Legend

I am not proposing this as something that you should always do but here is a trick that will allow you to switch to multisearch (which is not limited), appendpipe (which is not limited) + inputlookup (limited to 1000000000) to get out of this problem:

The first step is to use multisearch to save off each dataset to it's own file:

multisearch
   [ search index=main auditSource=frontend auditType=ExitSurvey detail.manageList!="None"
         | outputcsv MySearchTemp1.csv ]
   [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" manageList NOT auditSource=* 
         | spath 
         | search auditType=ExitSurvey detail.manageList!="None"
         | outputcsv MySearchTemp2.csv ] 
   [ search index=main auditSource=agent-frontend auditType=ExitSurvey detail.viewPayments!="None"
        | outputcsv MySearchTemp3.csv ]
   [ search index=main "\"auditSource\":\"agent-frontend\"" "\auditType\":\"ExitSurvey\"" viewPayments NOT auditSource=* 
        | spath 
        | search auditType=ExitSurvey detail.viewPayments!="None"
        | outputcsv MySearchTemp4.csv ]

Next, we drop all the events:

| where isnotnull(ThisFieldCannotExistSoThisDropsAllEventsSoFar)

Lastly, we pull the data back in with inputcsv and switch append to appendpipe:

| appendpipe [ |inputcsv MySearchTemp1.csv ]
| appendpipe [ |inputcsv MySearchTemp2.csv ] 
| replace /agent/survey With "Manage List" 
| contingency tags.path detail.manageList  
| appendpipe [ |inputcsv MySearchTemp3.csv
    | appendpipe [ |inputcsv MySearchTemp4.csv ] 
    | replace /agent/survey With "View Payments" 
    | contingency tags.path detail.viewPayments ] 
| search tags.path!="TOTAL" 
| rename tags.path TO "Question"  
| fields Question, 1, 2, 3, 4, 5 
| addtotals label=Total 
| rename 1 TO "Very Dissatisfied", 2 TO "Dissatisifed", 3 TO "Neither", 4 TO "Satisfied", 5 TO "Very Satisfied"

So long as you are not using a multivalued fields, this should work fine for you ( outputcsv has the mostly unknown and, until recently undocumented, impact of calling nomv on all multivalued fields). If you switch from using files to using a KV Store, then this negative side-effect does not happen.

0 Karma

IRHM73
Motivator

No @woodcock, forgive me, your solution was great because it helps build my capability, so I wasn't knocking it any way.

Kind Regards

Chris

0 Karma

IRHM73
Motivator

Hi @woodcock, sincere thanks for this.

Kind Regards

Chris

0 Karma

woodcock
Esteemed Legend

Keep in mind, that I am by no means saying that this is the best approach to do whatever it is that you are trying to do. I answered your question very literally, without considering your end goal and whether there is a smarter/better approach to use. The truth is that I have been looking for an opportunity to disclose this approach and your question was the perfect occasion.

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 ...