topic Re: How to cross reference 2 fields (or back reference)... in Splunk Search

How to cross reference 2 fields (or back reference)...

999chris — Thu, 20 Oct 2016 09:44:04 GMT

Hi,

Here are a few log examples (I've just shown the fields extracted for simplicity):

00:19:07 -
jobId=527A63
vamAssetId=815164

00:37:15 -
jobId=527A63
status=encoding
progress=20

10:08:28 -
jobId=EE7086
vamAssetId=2359740

10:08:37 -
jobId=EE7086
status=starting

...

So I'd like to present the statuses of each vamAssetId in a table - thus:

|vamAssetId|status      |progress
|815164        |encoding|20
|2359740      |starting   |0

Trouble is the "vamAssetId" fields are not referenced in the same events as a "status" or "progress". The vamAssetId is assigned a jobId early on and the jobId is the only common reference between the two.

I have the current search query, but I cant finish off and display this information logically. Could you help please?

index=ateme [search index=ateme vamAssetId=815164 | fields jobId]
| eval progress=if(status="complete",100,if(status="starting",0,progress))
| table jobId status progress

Re: How to cross reference 2 fields (or back reference)...

richgalloway — Thu, 20 Oct 2016 12:06:20 GMT

Try this.

index=ateme | transaction jobId | eval progress=case(status="complete",100, status="starting",0, 1=1, progress) | table vamAssetId jobId status progress

Re: How to cross reference 2 fields (or back reference)...

999chris — Thu, 20 Oct 2016 12:19:28 GMT

Hi Rich,

Thanks for your reply. But this is not matching any events.

Re: How to cross reference 2 fields (or back reference)...

sundareshr — Thu, 20 Oct 2016 12:52:01 GMT

Try this

index=ateme | eventstats values(vamAssetId) as vamAssetId by jobid | eval progress=if(status="complete",100,if(status="starting",0,progress)) | stats latest(status) as status latest(progress) as progress by vamAssetId | fillnull

*OR*

index=ateme | eventstats values(vamAssetId) as vamAssetId by jobid | eval progress=if(status="complete",100,if(status="starting",0,progress)) | where isnotnull(status) OR isnotnull(progress) | table vamAssetId status progres

Re: How to cross reference 2 fields (or back reference)...

999chris — Thu, 20 Oct 2016 13:30:24 GMT

Hi,

Thanks for your try but that still isnt quite there.

Simply put I think I need to add the vamAssetId field and value to each event that matches the specific jobId.

I.e if there is one event with:

jobId=527A63
vamAssetId=815164

I need every event with jobId=527A63 to have vamAssetId=815164 added to it. This seems like the simplest solution but I've run out of brain power to do it.

Re: How to cross reference 2 fields (or back reference)...

999chris — Mon, 24 Oct 2016 10:38:27 GMT

Hi,

Thanks for your try but that still isnt quite there.

Simply put I think I need to add the vamAssetId field and value to each event that matches the specific jobId.

I.e if there is one event with:

jobId=527A63
vamAssetId=815164

I need every event with jobId=527A63 to have vamAssetId=815164 added to it. This seems like the simplest solution but I've run out of brain power to do it.

Re: How to cross reference 2 fields (or back reference)...

999chris — Mon, 24 Oct 2016 17:05:09 GMT

index=ateme [search index=ateme vamAssetId=$asset_id$ | fields jobId] | transaction jobId maxspan=3d | stats first(status) as Status max(progress) as Progress by filename | eval Progress=case(Status="complete",100, status="starting",0, 1=1, Progress) | rename filename as Filename

Was the modified version. This worked perfectly. Cheers