DataFilterID in ReportBroadcast table

Brendan Codrington shared this question 7 days ago
Awaiting Reply

Hi, I'm expanding the yellowfin audit views to get additional data out about broadcasts.

The ReportBroadcast table has a DataFilterID which appears to relate to the filter value applied to the broadcast. And it would be great to be able to report against what filter values are saved against an individual broadcast. But I can't find that exact field in any other table, or any matches for the datafilterID values against broadcasts in other tables that have filterID fields or similar, eg ReportFilter, ReportInstanceFilter,CachedFilter.

Does such a table (filter values set against a broadcast) exist at all, directly or indirectly?

Thanks,

Brendan

Comments (7)

photo
1

Hello Brendan,

Thank you for reaching out to us.

I'm afraid that I am not to sure what the DataFilterID links to. But for the detail you are looking for I would recommend using the broadcast field within the ReportFilter table to join to your broadcast or you could use a clause to find these filters by using the ReportFilter.FilterAlertCode field for the string "BROADCASTFILTER".

The reportfilter table will show you the where clause operator, column operator and the values used in the filters. You can then use the FieldTemplateID to reference this to the ReportFieldTemplate table which would allow you to find the field affected by this filter.

I hope this helps. Please let me know if you have any further questions.

Kind regards,

Nathan Goddard

photo
1

Hi Nathan,

Thanks for your help with this - so the reportfilter table gives me the alert settings and values, and the ability to see what column(s) each alert is linked to. What about the assign filters settings for basic broadcast (current/default/bookmarked values) or Advanced filters (value applied per recipient per run)? Is it potentially through the reportinstance and reportinstancefilter tables if there's a way to link an instance to each broadcast event?

Kind regards,

Brendan

photo
1

Hi Brendan,

Thank you for getting back to me.


Is it potentially through the reportinstance and reportinstancefilter tables if there's a way to link an instance to each broadcast event?

- Yes, you should be able to find what you are looking for within the reportinstancefilter or reportfilter tables. As for seeing each broadcast event, you could use the reportbroadcastresult table, this will show the individual broadcasts sent, to who, the reportID, broadcastID, etc.

I hope this helps, please let me know if you have any further questions.

Kind regards,

Nathan Goddard

photo
1

Thanks Nathan, I did some more testing and comparison of fields yesterday and have managed to find a way to pull out saved filter values for bookmarks in different scenarios, and link up with advanced broadcast settings as well as event data. Happy to close this ticket.

Appreciate your help!

Kind regards,

Brendan

photo
1

Hi Brendan,

I am glad to hear that you have managed to find what you need.

I have now marked this questions as answered.

Have a fantastic week.

Kind regards,

Nathan Goddard

photo
1

Thanks again Nathan. Just to polish off how far I can push this broadcast reporting... some final questions:

  • Is there a known way to convert the formats for timestamp fields like LastModifiedGMTDateTime (eg 20190814060456) and LastModifiedTime (eg 21896) in the broadcast table, similarly ScheduledLocalRunTime (eg 26100) in the TaskSchedule table into a timestamp eg hh:mm:ss? I've tried the various converters in the view to no avail.
  • Is there a reason why the EventTime field values are all 0 (even though the actual event dates are recorded in EventDate)

Kind regards,

Brendan

photo
1

Hi Brendan,

These are some very good questions, hopefully the below should help;

1. Convert timestamp (eg 20190814060456, there are a few elements here, depending on the DB type you are using the syntax may vary, I will be using MySQL in my example.

1a. For the date you can use the date_format() function, this will extract the date in any standard date format, examples can be found here.

1b. For the time you can use the time_format() function, examples found here.

1c. Or if you would like to do all of this within YF, you can select the field as a timestamp and use the timestamp formatter seen below.

/21199186e18e3cb00af98cbf41145755


2. Last Modified Time or Scheduled Local Run Time (eg 21896) these times are stored as seconds from midnight. So in the example below you can see that the last modified time is 38248 seconds which is 10:37:28. Within MySQL you can use the sec_to_time() Function to extract this from this form. Alternatively you could use the LastModifiedGMTDateTime field and use the options from point 1.

3. EventTime, I have checked all of my local databases to confirm (11), and these all show 0 in event time as well. I would assume that these field may have been used to store time in previous versions of Yellowfin but this is now stored in the GMTDateTime field in newer releases.


I hope this helps.

Kind regards,

Nathan Goddard