private DataTable GetExistingBulletins()
{
string sp_used;
if (procedure_installed() && config.Vulnerable)
{
sp_used = @"exec [ZeroDayPatch_GetVulnerableMachines-" + Constant.ZERODAY_SCHEMA_VERSION + "]";
}
else if (config.Patch_All_Vendors)
{
sp_used = @"exec spPMCoreReport_SoftwareBulletinSummary";
}
else if (config.Custom_Procedure != "")
{
sp_used = "if exists (select 1 from sys.objects where type = 'P' and name = '" + config.Custom_Procedure + "')";
sp_used += "\n\texec [" + config.Custom_Procedure + "]";
}
else
{
sp_used = @"exec spPMCoreReport_AllSoftwareBulletins";
}
Console.WriteLine("# Using \"{0}\" to get bulletin candidates.", sp_used);
return(DatabaseAPI.GetTable(sp_used));
}
public DataTable GetInventory_Line_Stats(string agent_name)
{
string sql = "select cast(cast(_Exec_time as date) as varchar(255)) as 'Date', [Computer #], [Updated in last 4 weeks], [Not updated in last 4 weeks] from TREND_InventoryStatus where [inventory type] = '{0}'";
sql = string.Format(sql, agent_name);
return(DatabaseAPI.GetTable(sql));
}
private DataTable GetExistingBulletins()
{
string sp_all = @"exec spPMCoreReport_SoftwareBulletinSummary";
string sp_msft = @"exec spPMCoreReport_AllSoftwareBulletins";
string sp_used = "";
if (config.Patch_All_Vendors && config.Custom_Procedure == "")
{
sp_used = sp_all;
}
else if (config.Custom_Procedure != "")
{
sp_used = "if exists (select 1 from sys.objects where type = 'P' and name = '" + config.Custom_Procedure + "')";
sp_used += "\n\texec [" + config.Custom_Procedure + "]";
}
else
{
sp_used = sp_msft;
}
EventLog.ReportInfo("Preparing to get bulletin collection from '" + sp_used + "' query.");
return(DatabaseAPI.GetTable(sp_used));
}
public DataTable GetAgent_Line_Stats(string agent_name)
{
string sql = @"select cast(cast(_Exec_time as date) as varchar(255)) as _exec_time, [Agents Installed], cast([Agents Installed] as int) - cast([Agents to upgrade] as int) as 'Agents up-to-date', [Agents to upgrade] from TREND_AgentVersions where [agent name] = '{0}'
";
sql = string.Format(sql, agent_name);
return(DatabaseAPI.GetTable(sql));
}
private DataTable GetExcludedBulletins()
{
DatabaseAPI.ExecuteNonQuery(Constant.PATCH_EXCLUSION_CREATION);
String sql = Constant.PATCH_EXCLUSION_QUERY;
DataTable t = DatabaseAPI.GetTable(sql);
return(t);
}
public static int list()
{
Console.WriteLine("Exclusion list:");
try {
DataTable t = DatabaseAPI.GetTable(Constant.PATCH_EXCLUSION_LIST);
foreach (DataRow r in t.Rows)
{
Console.WriteLine("\t{0} ({1})", r[0].ToString(), r[1].ToString());
}
return(0);
} catch {
Console.WriteLine("Failed to access the exclusion table...");
Console.WriteLine("If you can add or remove entries you may want to use the force init switch to reset the patch exclusion tasble schema as it may be out-of-date.");
return(-1);
}
}
public DataTable GetInventory_CandleOutdated_Stats()
{
string sql = @"
select a1.[inventory type], a1.Lowest /* LOW */, a3.[Not updated in last 4 weeks] as 'Previous' /* OPENING */, a2.[Not updated in last 4 weeks] as 'Current' /* CLOSING */, a1.Highest /* MAX */
from (
select a1.[inventory type], MIN(a1.[Not updated in last 4 weeks]) as 'Lowest', MAX(a1.[Not updated in last 4 weeks]) as 'Highest'
from TREND_InventoryStatus a1
group by [inventory type]
) a1
join TREND_InventoryStatus a2
on a1.[inventory type] = a2.[inventory type]
join TREND_InventoryStatus a3
on a2.[inventory type] = a3.[inventory type]
where a2._Exec_id = (select MAX(_exec_id) from TREND_InventoryStatus)
and a3._Exec_id = (select MAX(_exec_id) - 1 from TREND_InventoryStatus)";
return(DatabaseAPI.GetTable(sql));
}
public DataTable GetAgent_CandlestickOutdated_Stats()
{
string sql = @"
select a1.[Agent Name], a1.Lowest /* LOW */, a3.[Agents to upgrade] as 'Previous' /* OPENING */, a2.[Agents to upgrade] as 'Current' /* CLOSING */, a1.Highest /* MAX */
from (
select a1.[Agent Name], MIN(a1.[Agents to upgrade]) as 'Lowest', MAX(a1.[agents to upgrade]) as 'Highest'
from TREND_AgentVersions a1
group by [agent name]
) a1
join TREND_AgentVersions a2
on a1.[Agent Name] = a2.[Agent Name]
join TREND_AgentVersions a3
on a2.[Agent Name] = a3.[Agent Name]
where a2._Exec_id = (select MAX(_exec_id) from TREND_AgentVersions)
and a3._Exec_id = (select MAX(_exec_id) - 1 from TREND_AgentVersions)
";
return(DatabaseAPI.GetTable(sql));
}
private DataTable GetExcludedBulletins()
{
DatabaseAPI.ExecuteNonQuery(Constant.PATCH_EXCLUSION_CREATION);
return(DatabaseAPI.GetTable(Constant.PATCH_EXCLUSION_QUERY));
}
public DataTable GetInventory_Gauge_Stats()
{
string sql = "select[inventory type], cast([% up-to-date] as varchar(255)) from TREND_InventoryStatus where _Exec_id = (select MAX(_exec_id) from TREND_InventoryStatus) order by [inventory type]";
return(DatabaseAPI.GetTable(sql));
}
// Inventory data handlers
public DataTable GetInventory_List()
{
string sql = "select distinct([inventory type]) from TREND_InventoryStatus";
return(DatabaseAPI.GetTable(sql));
}
public DataTable GetAgent_Gauge_Stats()
{
string sql = "select [Agent Name], cast([% up-to-date] as varchar(255)) from TREND_AgentVersions where _Exec_id = (select MAX(_exec_id) from TREND_AgentVersions) order by [agent name]";
return(DatabaseAPI.GetTable(sql));
}
public string GetAgentVersion(string agent_name)
{
string sql = String.Format("select max([Agent Highest Version]) from TREND_AgentVersions where [Agent name] = '{0}'", agent_name);
return((string)DatabaseAPI.GetTable(sql).Rows[0][0]);
}
// Agent data handlers
public DataTable GetAgent_List()
{
string sql = "select distinct([agent name]) from TREND_AgentVersions";
return(DatabaseAPI.GetTable(sql));
}