I would like to be able to add the first detected date of fixlet on the reports I am working on. However, it looks like I am not getting anywhere when trying to add the first detected date for each row.
trs of htmls (unique values of (it as string) of (td of (id of computer of it as string & id of fixlet of it as string)
& td of (id of computer of it as string) & td of (if exists name of computer of it then name of computer of it else "<none>")
& td of (if exists operating system of computer of it then operating system of computer of it else "<none>")
& td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "")
& td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>"))
& td of (if (exists first became relevant of it) then ("1") else ("0"))
& td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0")
& td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")
& td of (id of fixlet of it as string)
& td of (if exists source severity of fixlet of it then source severity of fixlet of it else "<none>")
& td of (if (exists Last Became NonRelevant of it) then Last Became NonRelevant of it as string else "0")) of results of bes fixlets
)
The following query executes fine however the last piece I need to be able to add the (minimum of first became relevants of results of … clause for each row
so that I can calculate the time difference in fixlets general first found date and the computers remediation date for that filet
Full tag, you must select the fixlet results in the filter.
<?relevance trs of htmls (unique values of (it as string) of (td of (id of computer of it as string & id of fixlet of it as string)
& td of (id of computer of it as string) & td of (if exists name of computer of it then name of computer of it else "<none>")
& td of (if exists operating system of computer of it then operating system of computer of it else "<none>")
& td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "")
& td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>"))
& td of (if (exists first became relevant of it) then ("1") else ("0"))
& td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0")
& td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")
& td of (id of fixlet of it as string)
& td of (if exists source severity of fixlet of it then source severity of fixlet of it else "<none>")
& td of (if (exists Last Became NonRelevant of it) then Last Became NonRelevant of it as string else "0")) of results of bes fixlets
)?>
trs of htmls (elements of set of (it as string) of (td of (id of computer of it as string & id of fixlet of it as string) & td of (id of computer of it as string) & td of (if exists name of computer of it then name of computer of it else "<none>") & td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & html (if (exists first became relevant of it) then ((if (not relevant flag of it) then ("<td>" & (last became nonrelevant of it as string) & "</td><td>" & (first became relevant of it as string) & "</td><td>" & ((last became nonrelevant of it - first became relevant of it) as string) & "</td>") else ("<td>Still Relevant</td><td>" & (first became relevant of it as string) & "</td><td>N/A</td>"))) else ("N/A" & "Never Relevant" & "N/A")) & td of (id of fixlet of it as string) & td of (if exists source severity of fixlet of it then source severity of fixlet of it else "<none>")) of results of bes fixlets)
it looks like it is missing the “)” at the end and this does not provide the Minimum first became relevant of the fixlet to any computer not the computer inline. This would provide the “First Detected Date” of the fixlet.
Oh, I thought you were trying to get the first found (relevant) date for each computer, not the earliest first became relevant date across all computers. That kind of query would be much more complicated and significantly impact the performance of the report unless you change it to query for all the data in javascript, and then pull out the minimum and build the resulting table from there.
If you’re not air-gapped, I’d recommend using the source release date of the fixlet, which should be equal or quite close to the earliest relevant date. This would maintain the current performance level of the query, and probably more accurately reflect when that vulnerability could first be identified/remediated in your env.
@otag, here is an example that might work for you.
I simplify the statements to make it readable.
<table border="1" cellpadding="5" style="border-collapse:collapse;">
<th>Computer</th><th>Fixlet</th><th>Severity</th><th>Remediation Date</th><th>Release Date</th><th>Delta Days</th>
<?relevance
concatenation of
trs of htmls (
unique values of (it as string) of (
td of (name of computer of it) &
td of (name of fixlet of it) &
td of (source severity of fixlet of it) &
td of ((it as string) of date (local time zone) of last became nonrelevant of it) &
td of ((it as string) of source release date of fixlet of it) &
td of (it as string) of (date (local time zone) of last became nonrelevant of it - source release date of fixlet of it)
) of results of bes fixlets)
?>
</table>
There is a request to add a column for the admin of the server. I can find the information by running the query separately with the following. However, any idea how this can be included in the above query for each device?
Current columns are
Computer Fixlet Severity Rememdiation Release Delta
Requested columns are
Computer Fixlet Severity Rememdiation Release Delta ManagedBy
(values of (results (bes properties whose (name of it = “Managed_By”), it))) of bes computers
<table border="1" cellpadding="5" style="border-collapse:collapse;">
<th>Computer</th><th>Fixlet</th><th>Severity</th><th>Remediation Date</th><th>Release Date</th><th>Delta Days</th><th>Managed By</th>
<?relevance
concatenation of
trs of htmls (
unique values of (it as string) of (
td of (name of computer of it) &
td of (name of fixlet of it) &
td of (source severity of fixlet of it) &
td of ((it as string) of date (local time zone) of last became nonrelevant of it) &
td of ((it as string) of source release date of fixlet of it) &
td of (it as string) of (date (local time zone) of last became nonrelevant of it - source release date of fixlet of it) &
td of (concatenation "," of values of results from (bes properties whose (name of it = "Managed_By")) of computer of it)
) of results of bes fixlets)
?>
</table>
I tried combining some of what @leewei and what @steve posted, to get past the nonexistent errors, still need to include some of the date formatting that @leewei included, but here is what I currently have. I believe I have the else strings right, the Still Relevant in the Delta Column appears to be showing up when it should. I’ll add another post when I’ve prettied it up a bit.
<table border="1" cellpadding="5" style="border-collapse:collapse;">
<th>Computer</th><th>Fixlet</th><th>Severity</th><th>Remediation Date</th><th>Release Date</th><th>Delta Days</th>
<?relevance
concatenations of
trs of htmls (
unique values of (it as string) of (
td of (name of computer of it) &
td of (name of fixlet of it) &
td of (source severity of fixlets of it) &
td of ( if(exists first became relevant of it) then ( if (not relevant flag of it) then (last became nonrelevant of it as string) else "NA") else "NA") &
td of ((it as string) of source release date of fixlet of it) &
td of ( if(exists first became relevant of it) then ( if (not relevant flag of it) then ( (last became nonrelevant of it - first became relevant of it) as string) else "Still Relevant" ) else "NA" )
) of results of bes fixlets)
?>
</table>
