How to append two fields extracted from different events having same ID field without using join?

[kabiraj]

Hi All,

I have three fields error, Bandwidth & ID. error & Bandwidth are fields for two different events while both the events have same or different ID field. I want to append error with Bandwidth, something like error:Bandwidth only for those events which have same ID field. How will i do it without using join or append command?

Below is a sample of the two events:

<RECORD><DATE>2015-09-21 07:19:37.865</DATE><LEVEL>INFO</LEVEL><SEVERITY></SEVERITY><CLASS>com.bt.vosp.webendpoint.impl.processor.MPlayCFIService</CLASS><METHOD>requestToPlayJSON</METHOD><FILE>MPlayCFIService.java</FILE><LINE>440</LINE><CORRELATIONID>1442816367016:prod-trusted/28574451_42274</CORRELATIONID><TEXT>RequestToPlay Response :{"RequestToPlayResponse":{"errorCode":"0","errorMessage":"Success","ContentInformationObjectResponse":[{"ContentLicenseResponse":{},"ContentURL":"http:\/\/csp1-1.cdd.bt.net\/content\/enc_BBJ877667A_CAR.wmv?SIGV=2&IS=0&ET=1442902777&CIP=81.151.236.213&KO=1&KN=1&US=296d49cd5782d1cca21d832011cecb533f70282e","ReleasePID":"_5E_7oFAqz7_","ReleaseID":"http:\/\/bt.data.media.theplatform.eu\/media\/data\/Release\/17686597485","IsMediaDRMProtected":"true","FileSize":"6871900","Duration":"32080","SlotType":"Trailer"}],"cid":"1442816367016:prod-trusted\/28574451_42274"}}</TEXT></RECORD>

<RECORD><DATE>2015-09-21 07:19:37.818</DATE><LEVEL>INFO</LEVEL><SEVERITY></SEVERITY><CLASS>com.bt.vosp.daa.storefront.impl.helper.PortalHttpServerHelper</CLASS><METHOD>readXml</METHOD><FILE>PortalHttpServerHelper.java</FILE><LINE>91</LINE><CORRELATIONID>1442816367016:prod-trusted/28574451_42274</CORRELATIONID><TEXT>Response Code from Apache Server:: 200 - Response Message from Apache Server :: <asset targetBandwidth="SD" LinkedTitleID="" TitleID="877667" xmlSource="TV_BT_Scheduled" schedulerChannel="TV" subGenre="" subtitles="" keywords="" productOfferingType="Promotion" type="Promotion" External_CP_ID="" ClassicAssetId="BBJ877667A" CountryOfOrigin="" category_id="TV" hd="0" duration="PT00H00M32S" rating="U" language="English" genre="All" availability="2015-09-16T00:00:00~2015-09-28T23:59:00" ClientAssetId="BBJ877667A" id="17686597339"> <TOV artist="" music="0" episodeNumber="" episodeTitle="" seriesNumber="" seriesTitle="" year="0" synopsis="It's time to Man Up on BT with some testosterone filled shows that require more than a little stiff upper lip.From mountain summits with Bear Grylls to icy Norwegian highways to deep undercover work in notorious biker gangs, you'd better strap in because things are about to get rough. Running Wild With Bear Grylls. Ice Road Rescue. Gangland Undercover" title="Man Up" id=""/> <subscription scode="S0115263"/> <media> <release targetBandwidth="SD" aspectratio="16:9" encodingformat="WM" dur="PT0H00M32.080S" scheme="wmDrm" protected="1" updated="2015-09-14T03:48:56" filename="http://csp1-1.cdd.bt.net/content/enc_BBJ877667A_CAR.wmv" filesize="6871900" exclude="0" HD="0" service="CAR" serviceFormat="Cardinal-SD" releaseid="http://bt.data.media.theplatform.eu/media/data/Release/17686597485" PID="_5E_7oFAqz7_"> <subtitle path="" track="" lang=""/> <audio type="" purpose="" track="" mix="" lang=""/> </release> <release targetBandwidth="SD" aspectratio="16:9" encodingformat="MPEG" dur="PT0H00M32.844S" scheme="marlin" protected="1" updated="2015-09-14T03:46:49" filename="http://csp1-1.cdd.bt.net/content/enc_BBJ877667A_YOU.bbts" filesize="5607476" exclude="0" HD="0" service="YOU" serviceFormat="Youview-SD" releaseid="http://bt.data.media.theplatform.eu/media/data/Release/17687621034" PID="amz0IIIKOE_o"> <subtitle path="" track="" lang=""/> <audio type="" purpose="" track="" mix="" lang=""/> </release> </media> <contentProvider name="BT Vision PVODS" id="BTV"/></asset></TEXT></RECORD>                     

Here if you can see both the events have CORRELATIONID field. I want to append "errorCode" value in 1st event with "targetBandwidth" value from asset tag in 2nd event like "errorCode:targetBandwidth" if both the events have matching CORRELATIONID fields without using join or append command. e.g. "0:SD". can it be done?

[somesoni2]

Assuming the fields COORELATIONID, errorCode and targetBandwidth is already extracted, try something like this

your base search | stats values(errorCode) as errorCode values(targetBandwidth) as targetBandwidth

[woodcock · Accepted solution]

Like this

sourcetype=source_with_error OR sourcetype=source_with_bandwidth | stats values(*) AS * BY ID | where isnotnull(error) AND isnotnull(Bandwidth) | eval errorAndBandwidth = error . ":" . Bandwidth

[kabiraj]

Hi woodcock,

Thanks for the solution. It worked but in my case i have multivalued "error" field. So if want to exclude lets say 1 value from a 3 multivalued field, then the solution is not working.

e.g. error=0,8002,8003(all in one event) bandwidth=SD(in another event). According to your solution, SD is appending only with 0 like 0:SD and not with 8002 or 8003. So if i exclude 0 by putting error!=0, solution is not working.

[woodcock]

Try this:

 sourcetype=source_with_error OR sourcetype=source_with_bandwidth | stats values(*) AS * BY ID | where isnotnull(error) AND isnotnull(Bandwidth) | mvexpand error | eval errorAndBandwidth = error . ":" . Bandwidth

[kabiraj]

Sorry Woodcock, It was working fine previously, I put the time timepicker as last 15 minutes, therefore, results were not coming. Anyway, thanks a lot for your reply.
Cheers!

My query:

sourcetype="mplay" (((MP_Method="requestToPlayJSON" OR MP_Method="requestToPlayXML") MP_Response_Code=* MP_Correlation_Id=*) OR (("Product Info Content ::" OR "Response Code from Apache Server::") MP_Correlation_Id=* MP_TargetBandwidth="*")) | rex max_match=100 "(?im)errorCode(\":\"|\>)(?<error>\d+)" | stats values(error) as error values(MP_TargetBandwidth) as MP_TargetBandwidth by MP_Correlation_Id | mvexpand error | search error!=0 | where isnotnull(error) | eval error = if(isnull(MP_TargetBandwidth), error, error.":".MP_TargetBandwidth) | stats count by error

[kabiraj]

hi woodcock,

the query i posted shows me results but the count is not correct. i changed the position of mvexpand in the query like below:

sourcetype="mplay" (((MP_Method="requestToPlayJSON" OR MP_Method="requestToPlayXML") MP_Response_Code=* MP_Correlation_Id=*) OR (("Product Info Content ::" OR "Response Code from Apache Server::") MP_Correlation_Id=* MP_TargetBandwidth="*")) | rex max_match=100 "(?im)errorCode(\":\"|\>)(?<error>\d+)" | mvexpand error | search error!=0 | stats values(error) as error values(MP_TargetBandwidth) as MP_TargetBandwidth by MP_Correlation_Id  | where isnotnull(error) | eval error = if(isnull(MP_TargetBandwidth), error, error.":".MP_TargetBandwidth) | stats count by error

The above shows me the correct count but the bandwidth is not appending with error, infact i checked the bandwidth field was empty for all errors.

For 1st query i posted, bandwidth is appending with errors but only with single valued fields and thus returning incorrect count, Hence, if i remove 0 then other values along with 0 in the same event are also getting removed.

For the above query, all values(single & multi) only except 0 are showing(correct count) but the bandwidth is not getting appended to any of them.

Any suggestions?

[kabiraj]

here is an example of multivalued event:

<RECORD><DATE>2015-09-22 08:23:14.594</DATE><LEVEL>INFO</LEVEL><SEVERITY></SEVERITY><CLASS>com.bt.vosp.webendpoint.impl.processor.MPlayCFIService</CLASS><METHOD>requestToPlayXML</METHOD><FILE>MPlayCFIService.java</FILE><LINE>742</LINE><CORRELATIONID>1442906598740:bt/30750517_76957</CORRELATIONID><TEXT>Response of RequesttoPlay call is:<?xml version="1.0" encoding="UTF-8"?>
<RequestToPlayResponse>
<errorCode>0</errorCode>
<errorMessage>Success</errorMessage>
<cid>1442906598740:bt/30750517_76957</cid>
<ContentInformationObjectResponse>
<ContentLicenseResponse>
</ContentLicenseResponse>
<errorCode>8016</errorCode>
<errorMessage>Error In Validating Entitlements</errorMessage>
<ReleasePID>Ce7UIXZodi7o</ReleasePID>
<SlotType>feature</SlotType>
</ContentInformationObjectResponse>
</RequestToPlayResponse></TEXT></RECORD>