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