Unprotected Count for Most Recent Search

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

One of the reports that our units find useful shows the unprotected count for the most recent search of each endpoint. These reports have been exported from our console and attached to this post. In this post, I'll explain the construction and function of the reports. 

There are four reports; Step 1, Step 2, Step 3, and the Main report. The "step" reports feed data to the main report. For each report, the check box titled "This report will either be joined to another report or used for secondary analytics" on the Report tab is selected so that certain columns are made available in the report. This is required so that one report can be related to another report properly.

Step 1:

This report contains two columns from the Search category: Endpoint Identifier and Search Date/Time. The Aggregate property of the Search Date/Time column is set to Max since we want to see data for the most recent search performed.

Step 2:

This report contains three columns: Endpoint Identifier from the Locations category, Search Date/Time from the Searches category, and Unprotected Quantity from the Matches category. The Aggregate property of the Unprotected Quantity column is set to Sum since we want to see the total number of unprotected matches for each search.

Step 3:

This report contains five columns: the two columns from Step 1 and the three columns from Step 2. The Endpoint Identifier and Search Date/Time columns from Step 2 are hidden in this report (this is optional and cuts down on redundant data). Now, since this report is comprised of columns from other reports, relationships must be created so that the reports can be joined properly. Two relationships link the Endpoint Identifier and the Search Date/Time from Step 2 to Step 1. These are left joins since we want all records from Step 1 and any matching records from Step 2 that may be present.

Main:

This report contains six columns: Tag Name from the Tags column, Endpoint Name and Endpoint Identifier from the Endpoints category, and Endpoint Identifier, Search Date/Time, and Unprotected Quantity from Step 3. The Endpoint Identifier columns are hidden since it isn't a column we're interested in seeing in the final report. Similar to Step 3, a relationship must be created. This time, we relate the Endpoint Identifier column from Step 3 to the Endpoint Identifier from the Endpoints category. Again, this is a left join since we want to see all endpoints, even if a search hasn't been performed.

Hope this report is useful to others. If anything isn't clear, please ask and I will try to offer an explanation.

-Dan

Files:
uploads/14/step1.xml
uploads/14/step2.xml
uploads/14/step3.xml
uploads/14/main.xml

Edited by danlehman - 06 Dec 2010 at 1:20pm

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
danlehman Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List Newbie Joined: 24 Nov 2010 Location: Penn State Status: Offline Points: 20	Post Options Post Reply Quote danlehman Report Post Thanks(0) Quote Reply Topic: Unprotected Count for Most Recent Search Posted: 06 Dec 2010 at 1:19pm
	One of the reports that our units find useful shows the unprotected count for the most recent search of each endpoint. These reports have been exported from our console and attached to this post. In this post, I'll explain the construction and function of the reports. There are four reports; Step 1, Step 2, Step 3, and the Main report. The "step" reports feed data to the main report. For each report, the check box titled "This report will either be joined to another report or used for secondary analytics" on the Report tab is selected so that certain columns are made available in the report. This is required so that one report can be related to another report properly. Step 1: This report contains two columns from the Search category: Endpoint Identifier and Search Date/Time. The Aggregate property of the Search Date/Time column is set to Max since we want to see data for the most recent search performed. Step 2: This report contains three columns: Endpoint Identifier from the Locations category, Search Date/Time from the Searches category, and Unprotected Quantity from the Matches category. The Aggregate property of the Unprotected Quantity column is set to Sum since we want to see the total number of unprotected matches for each search. Step 3: This report contains five columns: the two columns from Step 1 and the three columns from Step 2. The Endpoint Identifier and Search Date/Time columns from Step 2 are hidden in this report (this is optional and cuts down on redundant data). Now, since this report is comprised of columns from other reports, relationships must be created so that the reports can be joined properly. Two relationships link the Endpoint Identifier and the Search Date/Time from Step 2 to Step 1. These are left joins since we want all records from Step 1 and any matching records from Step 2 that may be present. Main: This report contains six columns: Tag Name from the Tags column, Endpoint Name and Endpoint Identifier from the Endpoints category, and Endpoint Identifier, Search Date/Time, and Unprotected Quantity from Step 3. The Endpoint Identifier columns are hidden since it isn't a column we're interested in seeing in the final report. Similar to Step 3, a relationship must be created. This time, we relate the Endpoint Identifier column from Step 3 to the Endpoint Identifier from the Endpoints category. Again, this is a left join since we want to see all endpoints, even if a search hasn't been performed. Hope this report is useful to others. If anything isn't clear, please ask and I will try to offer an explanation. -Dan Files: uploads/14/step1.xml uploads/14/step2.xml uploads/14/step3.xml uploads/14/main.xml Edited by danlehman - 06 Dec 2010 at 1:20pm