Reached the subsearch limits, so I'm trying to use stats and having trouble with my search

[KJDII]

Hello,

new to splunk, I was able to create a the following query:

index="ops" sourcetype="tradeaudit3Q17" | table Symbol CUSIP "Trade Date" Price "Portfolio Code" Activity
| join Symbol,"Trade Date" [search index=ops host=app | rename Price as vendorPrice | eval "Trade Date"=strftime(_time, "%1m/%1d/%Y" ) | eval Symbol=lower(ID) | table Symbol vendorPrice "Trade Date"] 
| eval delta=((vendorPrice-Price)/vendorPrice)*100 | eval absDelta=abs(delta) | where absDelta>10
| rename vendorPrice as "SoD Price"
| rename delta as "Actual % Change"
| rename absDelta as "Absolute Change"
| table "Portfolio Code" Activity "Trade Date" Symbol CUSIP Price "SoD Price" "Actual % Change" "Absolute Change"

This works for a weeks worth of data, but not for 3 months. I reach the subsearch limit of 50,000. I have tried to convert to a stats:

index="ops" (host=app) OR (host="tradeaudit3Q17" ) etc. but I cant figure out how to match on Trade Date and Symbol and compare the two different Prices with stats.

[DalJeanis · Accepted solution]

Try something like this

index="ops" (sourcetype="tradeaudit3Q17" OR host="app")

 | rename COMMENT as "Keep only the fields we want from either record type"
 | fields _time, Symbol, ID, CUSIP, "Trade Date", Price, "Portfolio Code", Activity

 | rename COMMENT as "Calculate fields that are changed or different from each other"
 | eval vendorPrice=case(host="app", Price)
 | eval Price=case(sourcetype="tradeaudit3Q17",Price)
 | eval "Trade Date"=coalesce("Trade Date",strftime(_time,"%1m/%1d/%Y")) 
 | eval Symbol=coalesce(lower(Symbol),lower(ID)) 

 | rename COMMENT as "roll values from audit records onto the app records, then drop the audit records"
 | eventstats earliest(vendorPrice) as vendorPrice by Symbol "Trade Date"
 | where sourcetype="tradeaudit3Q17"

 | rename COMMENT as "calculate deltas and drop low deltas"
 | eval delta=((vendorPrice-Price)/vendorPrice)*100 
 | eval absDelta=abs(delta) 
 | where absDelta>10

 | rename COMMENT as "rename and present data"
 | rename vendorPrice as "SoD Price"
 | rename delta as "Actual % Change"
 | rename absDelta as "Absolute Change"
 | table "Portfolio Code" Activity "Trade Date" Symbol CUSIP Price "SoD Price" "Actual % Change" "Absolute Change"

[KJDII]

So this seems to get me really close, but I dont seem to be pulling values for eval = case() - here are the updates I made to match my data:

index="ops" (sourcetype="apxtradeaudit3Q17" OR sourcetype="csv-EzeFactset")
  | rename COMMENT as "Keep only the fields we want from either record type"
  | fields Date, Symbol, Axys_ID, CUSIP, "Trade Date", Price, "Portfolio Code", Activity
  | rename COMMENT as "Calculate fields that are changed or different from each other"
  | eval vendorPrice=case(sourcetype="csv-EzeFactset", Price)
  | eval Price=case(sourcetype="apxtradeaudit3Q17", Price)
  | eval sodtimestamp=strptime(Date,"%Y%m%d")
  | eval sodDate=strftime(sodtimestamp,"%1m/%1d/%Y")
  | eval "Trade Date"=coalesce('Trade Date',sodDate) 
  | eval Symbol=coalesce(lower(Symbol),lower(Axys_ID)) 
  | rename COMMENT as "roll values from audit records onto the app records, then drop the audit records"
  | eventstats earliest(vendorPrice) as vendorPrice by Symbol "Trade Date"
  | where sourcetype="apxtradeaudit3Q17"
  | rename COMMENT as "calculate deltas and drop low deltas"
  | eval delta=((vendorPrice-Price)/vendorPrice)*100 
  | eval absDelta=abs(delta) 
  | where absDelta>10
  | rename COMMENT as "rename and present data"
  | rename vendorPrice as "SoD Price"
  | rename delta as "Actual % Change"
  | rename absDelta as "Absolute Change"
  | table "Portfolio Code" Activity "Trade Date" Symbol CUSIP Price "SoD Price" "Actual % Change" "Absolute Change"

I also found that i needed to convert the "Data" field for sourcetype="csv-EzeFactset" into time, and then into the sourcetype="apxtradeaudit3Q17" format so it would coalesce.

[KJDII]

Finally got it working. Thank you.

The two items I needed to change: the Date eval for the coalesce and adding sourcetype to the interesting fields. the eval case statements started working after that.

---

| fields Date, Symbol, Axys_ID, CUSIP, "Trade Date", Price, "Portfolio Code", Activity, sourcetype

---

| eval sodtimestamp=strptime(Date,"%Y%m%d")
| eval sodDate=strftime(sodtimestamp,"%1m/%1d/%Y")
| eval matchDate=coalesce('Trade Date',sodDate)

you got me 99% there, really appreciate the help!