(( Relevant Fixlets by Computer ) by Group) > Spreadsheet. Possible?

Reporting
1

(imported topic written by ken@gracenote91)

Whats the easiest approach to accomplish the following?

Ideal world: I go to the “Computer Group” tab, right click on a specific group and magically a spreadsheet is created that contains all the computers in that group and the full name of all the relevant fixlets per computer.

For example

Group: Server

Computer A

MS10-019: Vulnerabilities in Windows Could Allow Remote Code Execution - Authenticode Signature …

MS10-020: Vulnerabilities in SMB Client Could Allow Remote Code Execution - Windows Server 2003 SP2

Computer B

MS10-022: Vulnerability in VBScript Scripting Engine Could Allow Remote Code Execution - VBScript 5.7 -

Computer C

Note that this needs to be a spreadsheet as I need to socialize this list among people who do not have accounts in BigFix.

The Usecase is that I need to create change management tickets before fixlets are applied to servers. I need to give the technical/business owner of a specific server a spreadsheet with just the server that he owns and have him test/approve that these patches get applied to his server.

I took a quick look at the excel connector and it was not obvious (to me) how I would build the query that would return that information in a useable format. Something for the web reports. Not saying that It is not possible, just hope someone (Lee Wei ?) could confirm if one way or the other is better… and perhaps suggest the approach to the query/relevance language that would create the appropriate cross section of information… before I invest a bunch of time trying to figure it out on my own through trial and error.

TIA

Ken

2

(imported comment written by Lee Wei)

Hi Ken,

Using some session relevance in the Excel Connector will probably give you very close to what you need.

  1. First work on the Session Relevance that we need. I am suggesting this one as an example.

    (item 0 of it, item 0 of item 1 of it, item 1 of item 1 of it, item 2 of item 1 of it) of (name of it, (name of it, source severity of it, (

    if (exists source release date of it) then (source release date of it as string)

    else (
    “None”))) of relevant fixlets whose (display name of site of it =
    “Patches for Windows (English)” and fixlet flag of it =

    true) of it) of members of bes computer groups whose (name of it =
    “Huff Building”)

  2. Run the relevance in the Session Relevance Editor from within the Excel Connector. There is an option to auto split the results.

  3. The output is now in Excel. If you want it to look nicer, look into creating a Pivot Table within Excel and the output will look like the following image.

To create a Pivot Table in Excel (I am using 2007).

  • Add the column titles such as Server, Fixlet, Severity and Release Date, to the data returned from the Excel Connector
  • Ctrl-A to select the entire data block
  • Excel menu Insert/Pivot table
  • Take all the defaults in the Create Pivot Table dialogue and the table will be created in a new worksheet
  • First select Server, then Fixlet as the fields to add to the report

Please feel free to send me a private message and I am happy to walk you through what I tested.

Lee Wei

3

(imported comment written by ken@gracenote91)

Thanks so much! This is very helpful.

Jimmy called me on another (related) matter and helped me out.

Here is the Session Rel language we used:

(item 0 of it,

item 0 of item 1 of it,

item 1 of item 1 of it,

item 2 of item 1 of it,

item 3 of item 1 of it,

item 4 of item 1 of it)

of

(name of it,

(name of it,

( if(exists message of it)then

(message of it as string )

else (“None”)

),

( (scripts of actions whose (script type of it = “text/x-uri”) of it) as string)

,

source severity of it,

(if (exists source release date of it) then (source release date of it as string) else (“None”)))

of relevant fixlets

whose

(display name of site of it = “Patches for Windows (English)” and

fixlet flag of it = true) of it)

OF

BES Computers whose (Active Directory Path of it contains “OU=Servers”)

And then to clean up the HTML that was returned from the “Description” field, we had to add a “striptags” vbs function to the workbook . Here is the vbs function we used:

Public Function StripTags(html As String) As String

On Error Resume Next

Dim lt As Long

Dim gt As Long

Dim buf As String

Dim tag As String

'Save original string into buffer

buf = html

'replace

with line breaks

buf = Replace(buf, "

", vbCrLf)

'Find and remove all HTML tags

lt = InStr(buf, “<”>")

Do While lt > 0 And gt > 0 And gt > lt

'extract tag

tag = Mid$(buf, lt, gt - lt + 1)

'strip tag from from buffer

buf = Replace(buf, tag, “”)

'look for next tag

lt = InStr(buf, “<”>")

Loop

'Next remove any extranious carriage returns and line feeds

'- from the beginnig

Do While Left(buf, 1) = vbCr Or Left(buf, 1) = vbLf

buf = Mid$(buf, 2)

Loop

'- from the end

Do While Right(buf, 1) = vbCr Or Right(buf, 1) = vbLf

buf = Left$(buf, Len(buf) - 1)

Loop

'Next fix common HTML escape codes

buf = Replace(buf, " ", " ")

buf = Replace(buf, “&”, “&”)

buf = Replace(buf, “”", “”"")

buf = Replace(buf, “&#”, “#”)

buf = Replace(buf, “<”<")

buf = Replace(buf, “>”, “>”)

buf = Replace(buf, “%20”, " ")

'strip off extra leading and trailing spaces

buf = Trim$(buf)

'return the result

StripTags = buf

End Function

Basically, the description was returned in col “C” with the first instance being in C2. So in the first blank col (g) we added in cell G2 =striptags(c2) . Then we pulled down the handle on G2 all the way down the column. Finally, I copied and paste/special/values col G on top of it self… which allowed me to delete Col C (which helped with the Row Autoheight formating)

4

(imported comment written by Lee Wei)

Glad you got what you need, and thanks for letting us know.