Adding cve id list to a report

I have created the following query in the Excel connector and it has everything that I want, except to get the CVE number. I have tried a couple different methods, but I just cannot get it to work. Any tips on how to do this or if it can be done?

(	item 0 of it as string  & "$x$" &
	item 1 of it as string  & "$x$" &
	item 2 of it as string  & "$x$" &
	item 3 of it as string  & "$x$" &
	(if (year of it as integer = 1980) 
		then ("<none>") 
		else 
			(year of it as string & "/" & 
			(month of it as two digits) as string & "/" & 
			(day_of_month of it as two digits) as string) ) 
				of (item 4 of it as date) & "$x$" &
	(if ((year of date (local time zone) of it) as integer = 1980) 
		then ("<none>") 
		else (
			(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) of time (local time zone) of it)) 
				of (item 5 of it as time) & "$x$" &
	(if ((year of date (local time zone) of it) as integer = 1980) 
		then ("<none>") 
		else (
			(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) of time (local time zone) of it)) 
				of (item 6 of it as time)) 
of (
	(if (exists Name of Computer of it) 
		then (concatenations "%0A" of (Name of Computer of it as string)) 
		else ("<none>")), 
	(if (exists Name of Fixlet of it) 
		then (concatenations "%0A" of (Name of Fixlet of it as string)) 
		else ("<none>")), 
	(if (exists Category of Fixlet of it) 
		then (concatenations "%0A" of (Category of Fixlet of it as string)) 
		else ("<none>")), 
	(if (exists cve id list of Fixlet of it) 
		then (concatenations "%0A" of (substrings separated by ";" of cve id list of Fixlet of it as string)) 
		else ("<none>")), 
    (if (exists Source Severity of Fixlet of it) 
		then (concatenations "%0A" of (Source Severity of Fixlet of it as string)) 
		else ("<none>")), 
	(if (exists Source Release Date of Fixlet of it) 
		then (Source Release Date of Fixlet of it as string) 
		else ("Fri, 15 Feb 1980")), 
	(if (exists First Became Relevant of it) 
		then (First Became Relevant of it as string) 
		else ("Fri, 15 Feb 1980 00:00:00 -0000")), 
	(if (exists Last Became Nonrelevant of it) 
		then (Last Became Nonrelevant of it as string) 
		else ("Fri, 15 Feb 1980 00:00:00 -0000"))) 
of 
	results
		  whose (((Name of Fixlet of it as lowercase does not contain "superseded") AND
			(Name of Fixlet of it as lowercase does not contain "corrupt") AND
			(Last Became Nonrelevant of it >= "Wed, 01 Jun 2016 00:00:00 -0600" as time) AND
			(Last Became Nonrelevant of it <= "Thu, 30 Jun 2016 00:00:00 -0600" as time))) 
	of bes fixlets 
		whose (
			(name of site of it = "Enterprise Security"))

@TheTick after you have added the CVE ID List, we need to also add a statement in the beginning to extract it:

item 4 of it as string

I will make a note to add CVE ID List as an attribute that can be selected from the Query Wizard.

(
    item 0 of it as string & "$x$" &
    item 1 of it as string & "$x$" &
    item 2 of it as string & "$x$" &
    item 3 of it as string & "$x$" &
    item 4 of it as string & "$x$" &
    (if (year of it as integer = 1980)
        then ("")
        else
        (year of it as string & "/" &
        (month of it as two digits) as string & "/" &
        (day_of_month of it as two digits) as string) )
        of (item 5 of it as date) & "$x$" &
    (if ((year of date (local time zone) of it) as integer = 1980)
        then ("")
        else (
        (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) of time(local time zone) of it))
        of (item 6 of it as time) & "$x$" &
    (if ((year of date (local time zone) of it) as integer = 1980)
        then ("")
        else (
        (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) of time(local time zone) of it))
        of (item 7 of it as time))
of (
    (if (exists Name of Computer of it) 
        then (concatenations "%0A" of (Name of Computer of it as string)) 
        else ("")),    
    (if (exists Name of Fixlet of it) 
        then (concatenations "%0A" of (Name of Fixlet of it as string)) 
        else ("")),
    (if (exists Category of Fixlet of it) 
        then (concatenations "%0A" of (Category of Fixlet of it as string)) 
        else ("")),    
    (if (exists cve id list of Fixlet of it) 
        then (concatenations "%0A" of (substrings separated by ";" of cve id list of Fixlet of it as string)) 
        else ("")),
    (if (exists Source Severity of Fixlet of it) 
        then (concatenations "%0A" of (Source Severity of Fixlet of it as string)) 
        else ("")),
    (if (exists Source Release Date of Fixlet of it)
        then (Source Release Date of Fixlet of it as string) 
        else ("Fri, 15 Feb 1980")),
    (if (exists First Became Relevant of it) 
        then (First Became Relevant of it as string) 
        else ("Fri, 15 Feb 1980 00:00:00 -0000")),
    (if (exists Last Became Nonrelevant of it) 
        then (Last Became Nonrelevant of it as string) 
        else ("Fri, 15 Feb 1980 00:00:00 -0000")))
of
results whose 
(((
  Name of Fixlet of it as lowercase does not contain "superseded") AND
  (Name of Fixlet of it as lowercase does not contain "corrupt") AND
  (Last Became Nonrelevant of it >= "Wed, 01 Jun 2016 00:00:00 -0600" as time) AND
  (Last Became Nonrelevant of it <= "Thu, 30 Jun 2016 00:00:00 -0600" as time)))
of bes fixlets whose (
  (name of site of it = "Enterprise Security"))
1 Like

Hi Lee,

Where is that headsmack emoticon?!

Thank you for the quick response.

Martin

@TheTick - I just checked and CVE ID List is available available as one of the properties.
It is under “Extended Properties”.
Can you double-check?

Hi Lee,

This is what I see.

I dod see it as an extended property for BES Fixlets, but not for Results of BES Fixlets.

@TheTick, ah got it.
I have added that to version 3.3.5.

http://leewei.com/bigfix/prod/excelconnector/IBM_BigFix_Excel_Connector_Setup_v335.zip

Anyone else needing any missing properties please let me know.

1 Like

I will try it out today and let you know. Thanks for the quick turn around.

Martin

I was finally able to get some time to test it out and it works as stated. Thank you!

Couple questions

  • Would it be possible to get the “Display name of site” instead of just “name of site”

  • I do not recall if this happened in the previous version, but when I use the “Open Query Definition” and run a query for say the Patches for AIX site and then look at the “Execute Query Wizard”, on the “Select one or more Fixlet sites” I see the “Patches for AIX” selected as expected. If I then run the query, then open up a similar query definition for “Patches for Windows”, open the query wizard and go to the “Select one or more Fixlet sites”, it shows “Patches for AIX” as the selected site. If I run the windows query, it will show the AIX patch site.

Hope that makes sense. :slight_smile:

Martin

@TheTick, I think Display Name of Sites make more sense, so I agree with you.

I will put on my list to include that next round.

@TheTick, Martin,
Here is version 3.3.7 with the Display Name of Site for you.
http://leewei.com/bigfix/prod/excelconnector/IBM_BigFix_Excel_Connector_Setup_v337.zip