Splunk Search
Highlighted

How to retain a column after an append

New Member

Hi guys,

I'm really new to Splunk, and probably have no idea what's actually going on with my search, so please bear with me.

I have the following two searches:

index=rtm RelativeURI "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" NOT "/applications*" NOT "/applicants*" NOT "/qualification*" NOT "/loan-solutions*" NOT "/decision*" NOT "/closing-costs*" NOT Client="enterpriseapi-5jLUHRmf9X59QNmgxWucnr54" NOT "SBBCREDITPATH"
| eval CLIENT=if(match(CorrelationID,"ALX-ECO*"),"Alexa",CLIENT)
| lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition
| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version
| eval SuccessPercent = (Cur_Success /(Cur_Success + Cur_Failure ))*100
| eval AbsoluteSuccessPercent = (Cur_Failure-Policy_Failure)/(if(Cur_Failure==0,1,Cur_Failure))*100
| eval SuccessPercent=round(SuccessPercent,2)
| eval AbsoluteSuccessPercent = round(AbsoluteSuccessPercent,2)
| eval ResponseTime = round(ResponseTime,1)
| eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
| append
[search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition | stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)]
|join type=outer SERVICE date_mday HTTPMETHOD CLIENT
[search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | stats sum(total_call_count) as ThreeMonAverageVolume by SERVICE date_mday HTTPMETHOD CLIENT]
| eval AVGmilliseconds=round(ResponseTime)
| eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
| eval VolPercentChange = round((CurrentVolume - ThreeMonAverageVolume) / ThreeMonAverageVolume * 100)
| table CLIENT SERVICE CurrentVolume SuccessPercent SuccessPercent Policy_Failure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD date_mday Version ThreeMonAverageVolume VolPercentChange
| sort by SuccessPercent +, CurrentVolume +

and

index=rtm RelativeURI "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" NOT "/applications*" NOT "/applicants*" NOT "/qualification*" NOT "/loan-solutions*" NOT "/decision*" NOT "/closing-costs*" NOT Client="enterpriseapi-5jLUHRmf9X59QNmgxWucnr54" NOT "SBBCREDITPATH"
| eval CLIENT=if(match(CorrelationID,"ALX-ECO*"),"Alexa",CLIENT)
| lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition
| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version
| eval SuccessPercent = (Cur_Success /(Cur_Success + Cur_Failure ))*100
| eval AbsoluteSuccessPercent = (Cur_Failure-Policy_Failure)/(if(Cur_Failure==0,1,Cur_Failure))*100
| eval SuccessPercent=round(SuccessPercent,2)
| eval AbsoluteSuccessPercent = round(AbsoluteSuccessPercent,2)
| eval ResponseTime = round(ResponseTime,1)
| eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
| append
[search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition | stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)]
| eval AVGmilliseconds=round(ResponseTime)
| eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
| eval VolPercentChange = round((CurrentVolume - ThreeMonAverageVolume) / ThreeMonAverageVolume * 100)
| table CLIENT SERVICE CurrentVolume SuccessPercent SuccessPercent Policy_Failure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD date_mday Version ThreeMonAverageVolume VolPercentChange

The two yield the same fields, but "ThreeMonAverageVolume" and "VolPercentChange" contain no data after the search runs. This is because the second search can't get the value of "totalcallcount" from the appended table. Does anyone know how I can get that value without joining the same table I just appended to the first table? It seems redundant.

Thanks!

Tags (4)
0 Karma
Highlighted

Re: How to retain a column after an append

Legend

Can you explain what you are hoping to achieve with this search. There are several segments in your rather complex search that are hard to follow. For example, in you first search, you have a eval ThreeMonAverageVolume=round(ThreeMonAverageVolume) but ThreeMonAverageVolume is not defined anywhere, unless its a field in your dataset.

Also, you have several joins and subsearches, that, I'm not sure are adding much value. For example, you have a subsearch to get the day of the month, will be easier (and cheaper) to just do strftime(now()-86400, "%d").

Long story short, can explain the end result you are expecting and share some raw events?

0 Karma
Highlighted

Re: How to retain a column after an append

New Member

Hi, It won't let me update the question, so I'll add the current state of my query here.

I'm trying to get all of the columns from my appended table (which has every column and none of the columns are blank)

search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(avg_Response_Time) as ResponseTime avg(total_call_count) as count sum(total_call_count) as ThreeMonAverageVolume by SERVICE date_mday HTTPMETHOD CLIENT

into the full table.

 index=rtm RelativeURI "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" NOT "/applications*" NOT "/applicants*" NOT "/qualification*" NOT "/loan-solutions*" NOT "/decision*" NOT "/closing-costs*" NOT Client="enterpriseapi-5jLUHRmf9X59QNmgxWucnr54" NOT "SBBCREDITPATH"
     | eval CLIENT=if(match(CorrelationID,"ALX-ECO*"),"Alexa",CLIENT)
     | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition
     | stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version
     | eval SuccessPercent = (Cur_Success /(Cur_Success + Cur_Failure ))*100
     | eval AbsoluteSuccessPercent = (Cur_Failure-Policy_Failure)/(if(Cur_Failure==0,1,Cur_Failure))*100
     | eval SuccessPercent=round(SuccessPercent,2)
     | eval AbsoluteSuccessPercent = round(AbsoluteSuccessPercent,2)
     | eval ResponseTime = round(ResponseTime,1)
     | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
     | append
     [search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(avg_Response_Time) as ResponseTime avg(total_call_count) as count sum(total_call_count) as ThreeMonAverageVolume by SERVICE date_mday HTTPMETHOD CLIENT]
     | eval AVGmilliseconds=round(ResponseTime)
     | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
     | eval VolPercentChange = round((CurrentVolume - ThreeMonAverageVolume) / ThreeMonAverageVolume * 100)
     | table CLIENT SERVICE CurrentVolume SuccessPercent SuccessPercent Policy_Failure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD date_mday Version count ThreeMonAverageVolume VolPercentChange
     | sort by SuccessPercent +, CurrentVolume +

The columns that come out of the sub search are:
SERVICE,datemday, HTTPMETHOD,CLIENT, CurrentVolume, CurSuccess, CurFailure, PolicyFailure, ResponseTime, count, ThreeMonthAverageVolume

And there are no empty columns

The columns yeilded from the whole query are:
CLIENT SERVICE CurrentVolume SuccessPercent PolicyFailure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD datemday Version count ThreeMonthAverageVolume VolPercentChange

Where all of the yielded columns are fine, but "count," "ThreeMonthAverageVolume" and "VolPercentChange" are all empty columns.

I thought append would merge the two datasets columns, by "SERVICE," "date_mday," "HTTPMETHOD," and "CLIENT," but that doesn't seem to be the case.

0 Karma
Highlighted

Re: How to retain a column after an append

Legend

Since all three are calculated from totalcallcount, can you verify is that field has numeric values. Is that a field in your dataset?

0 Karma
Highlighted

Re: How to retain a column after an append

Contributor

After you run your stats command the only fields left are the fields listed:

CurSuccess CurFailure PolicyFailure ResponseTime count SERVICE datemday HTTPMETHOD CLIENT Version

You would need to include ThreeMonAverageVolume as one of the by fields or else if it always has a single value use max(ThreeMonAverageVolume) or min(ThreeMonAverageVolume) so that the field is available to the subsequent search statements.

0 Karma
Highlighted

Re: How to retain a column after an append

New Member

So I've changed the search that I'm trying to append to the following, which should have addressed everything that you've stated.

| append
[search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(avg_Response_Time) as ResponseTime avg(total_call_count) as count sum(total_call_count) as ThreeMonAverageVolume by SERVICE date_mday HTTPMETHOD CLIENT]

And here's is the full query:

  index=rtm RelativeURI "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" NOT "/applications*" NOT "/applicants*" NOT "/qualification*" NOT "/loan-solutions*" NOT "/decision*" NOT "/closing-costs*" NOT Client="enterpriseapi-5jLUHRmf9X59QNmgxWucnr54" NOT "SBBCREDITPATH"
    | eval CLIENT=if(match(CorrelationID,"ALX-ECO*"),"Alexa",CLIENT)
    | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition
    | stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(ResponseTime) as ResponseTime avg(total_call_count) as count by SERVICE date_mday HTTPMETHOD CLIENT Version
    | eval SuccessPercent = (Cur_Success /(Cur_Success + Cur_Failure ))*100
    | eval AbsoluteSuccessPercent = (Cur_Failure-Policy_Failure)/(if(Cur_Failure==0,1,Cur_Failure))*100
    | eval SuccessPercent=round(SuccessPercent,2)
    | eval AbsoluteSuccessPercent = round(AbsoluteSuccessPercent,2)
    | eval ResponseTime = round(ResponseTime,1)
    | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
    | append
    [search index=rtm3_eapi_summary "mortgages" OR "home-equity-loans" OR "home-loans" OR "serviceable-instances" earliest=-1mon@mon latest=@mon [search index=rtm3_eapi_summary earliest=-1d@d latest=@d "/loans/home-loans/accounts" | fields date_mday ]  | eval total_call_count=total_call_count/3 | lookup hl_servicing_dispostion_lookup Service as SERVICE OUTPUT Disposition as disposition| stats count as CurrentVolume count(eval(Status="0")) as Cur_Success count(eval(Status!="0")) as Cur_Failure count(eval(Status!="0" AND disposition=="POLICY")) as Policy_Failure avg(avg_Response_Time) as ResponseTime avg(total_call_count) as count sum(total_call_count) as ThreeMonAverageVolume by SERVICE date_mday HTTPMETHOD CLIENT]
    | eval AVGmilliseconds=round(ResponseTime)
    | eval ThreeMonAverageVolume=round(ThreeMonAverageVolume)
    | eval VolPercentChange = round((CurrentVolume - ThreeMonAverageVolume) / ThreeMonAverageVolume * 100)
    | table CLIENT SERVICE CurrentVolume SuccessPercent SuccessPercent Policy_Failure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD date_mday Version count ThreeMonAverageVolume VolPercentChange
    | sort by SuccessPercent +, CurrentVolume +

The two searches yeild the correct values alone, but my huge query still doesn't want to give me "ThreeMonAverageVolume" and "VolPercentChange." Any ideas?

Thanks for the help btw.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.