Splunk Search

How to retain a column after an append

mjbaig
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 "total_call_count" 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

sjohnson_splunk
Splunk Employee
Splunk Employee

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

Cur_Success Cur_Failure Policy_Failure ResponseTime count SERVICE date_mday 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

mjbaig
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

sundareshr
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

mjbaig
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,date_mday, HTTPMETHOD,CLIENT, CurrentVolume, Cur_Success, Cur_Failure, Policy_Failure, ResponseTime, count, ThreeMonthAverageVolume

And there are no empty columns

The columns yeilded from the whole query are:
CLIENT SERVICE CurrentVolume SuccessPercent Policy_Failure AbsoluteSuccessPercent AVGmilliseconds HTTPMETHOD date_mday 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

sundareshr
Legend

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...