public DtoRawSqlQuery Run(List <DtoCustomComputerQuery> queries)
{
var sqlQuery = new DtoRawSqlQuery();
string select = "SELECT a.asset_id, a.asset_display_name,";
foreach (var query in queries.Where(x => x.Table.Equals("Asset Type")))
{
select += @" b." + query.Field + ",";
}
foreach (var query in queries)
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
var attribute = new ServiceCustomAttribute().GetCustomAttribute(caId);
select += @" ca" + caId + "." + query.Field + " as " + "\"" + attribute.Name + "\"" + ",";
}
}
}
}
var selectTrimmed = select.Trim(',') + " ";
sb.Append(selectTrimmed);
sb.Append(@" FROM assets as a ");
if (queries.Any(x => x.Table == "Asset Type"))
{
sb.Append("LEFT JOIN custom_asset_types b on a.asset_type_id = b.custom_asset_type_id ");
}
var customAttributeIds = new List <int>();
foreach (var query in queries)
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
customAttributeIds.Add(caId);
}
}
}
}
if (customAttributeIds.Count > 0)
{
foreach (var attributeId in customAttributeIds)
{
sb.Append("LEFT JOIN asset_attributes ca" + attributeId + " on a.asset_id = ca" + attributeId + ".asset_id AND ca" + attributeId + ".custom_attribute_id in ");
sb.Append("(" + attributeId + ") ");
}
}
sb.Append("WHERE (");
int counter = 0;
var parameters = new List <string>();
foreach (var query in queries)
{
var tableAs = "";
if (query.Table == "Asset")
{
tableAs = "a";
}
else if (query.Table == "Asset Type")
{
tableAs = "b";
}
else
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
tableAs = "ca" + caId;
}
}
}
}
counter++;
sb.Append(query.AndOr + " " + query.LeftParenthesis + " " + tableAs + "." + query.Field + " ");
sb.Append(query.Operator + " @p" + counter + " " + query.RightParenthesis);
parameters.Add(query.Value);
}
if (!queries.First().IncludeArchived)
{
sb.Append(") AND (a.is_archived = 0)");
}
else
{
sb.Append(")");
}
sb.Append(BuildGroupBy(queries.First().GroupBy));
sqlQuery.Sql = sb.ToString();
sqlQuery.Parameters = parameters;
return(sqlQuery);
}
public CustomAttributeController()
{
_serviceCustomAttribute = new ServiceCustomAttribute();
}
public DtoRawSqlQuery Run(List <DtoCustomComputerQuery> queries)
{
var sqlQuery = new DtoRawSqlQuery();
if (queries.Count == 1 && queries[0].AndOr.Equals("Not"))
{
var query = queries[0];
if (query.Table.Equals("Application"))
{
sb.Append(@"select a.computer_name
from computers as a
where a.computer_id not in
(
SELECT computers.computer_id
FROM computers
LEFT join computer_software on computers.computer_id = computer_software.computer_id
LEFT join software_inventory on (computer_software.software_id = software_inventory.software_inventory_id)
where software_inventory.name " + query.Operator + "'" + query.Value + "'" +
") and a.last_inventory_time_local > '2019-01-01'");
if (queries.First().IncludeArchived&& queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11 OR a.provision_status = 6)");
}
else if (queries.First().IncludeArchived)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11)");
}
else if (queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 6)");
}
else
{
sb.Append(" AND a.provision_status = 8");
}
sb.Append(BuildGroupBy(queries.First().GroupBy));
}
else if (query.Table.Equals("Windows Update"))
{
sb.Append(@"select a.computer_name
from computers as a
where a.computer_id not in
(
SELECT computers.computer_id
FROM computers
LEFT join computer_updates on computers.computer_id = computer_updates.computer_id
LEFT join wu_inventory on (computer_updates.wu_inventory_id = wu_inventory.wu_inventory_id)
where wu_inventory.title " + query.Operator + " " + "'" + query.Value + "'" +
") and a.last_inventory_time_local > '2019-01-01'");
if (queries.First().IncludeArchived&& queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11 OR a.provision_status = 6)");
}
else if (queries.First().IncludeArchived)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11)");
}
else if (queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 6)");
}
else
{
sb.Append(" AND a.provision_status = 8");
}
if (!string.IsNullOrEmpty(queries.First().GroupBy))
{
sb.Append($" GROUP BY {queries.First().GroupBy}");
}
}
else if (query.Table.Equals("Certificates"))
{
sb.Append(@"select a.computer_id,a.computer_name
from computers as a
where a.computer_id not in
(
SELECT computers.computer_id
FROM computers
left join computer_certificates on computers.computer_id = computer_certificates.computer_id
left join certificate_inventory on (computer_certificates.certificate_id = certificate_inventory.certificate_inventory_id)
where certificate_inventory.subject " + query.Operator + " " + "'" + query.Value + "'" +
") and a.last_inventory_time_local > '2019-01-01'");
if (queries.First().IncludeArchived&& queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11 OR a.provision_status = 6)");
}
else if (queries.First().IncludeArchived)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 11)");
}
else if (queries.First().IncludePreProvisioned)
{
sb.Append(" AND (a.provision_status = 8 OR a.provision_status = 6)");
}
else
{
sb.Append(" AND a.provision_status = 8");
}
if (!string.IsNullOrEmpty(queries.First().GroupBy))
{
sb.Append($" GROUP BY {queries.First().GroupBy}");
}
}
sqlQuery = new DtoRawSqlQuery();
sqlQuery.Sql = sb.ToString();
return(sqlQuery);
}
string select = "SELECT a.computer_id, a.computer_name,";
foreach (var query in queries.Where(x => x.Table.Equals("Computer")))
{
if (query.Field.Equals("computer_name"))
{
continue;
}
select += @" a." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Bios")))
{
select += @" b." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("System")))
{
select += @" c." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Hard Drive")))
{
select += @" d." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("OS")))
{
select += @" e." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Printer")))
{
select += @" f." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Processor")))
{
select += @" g." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Application")))
{
select += @" i." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Windows Update") && x.Field.Equals("is_installed")))
{
select += @" j." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Windows Update") && x.Field.Equals("install_date")))
{
select += @" j." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Windows Update") && x.Field.Equals("title")))
{
select += @" k." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Firewall")))
{
select += @" l." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("AntiVirus")))
{
select += @" m." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("BitLocker")))
{
select += @" n." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Logical Volumes")))
{
select += @" o." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Network Adapters")))
{
select += @" p." + query.Field + ",";
}
foreach (var query in queries.Where(x => x.Table.Equals("Certificates")))
{
select += @" r." + query.Field + ",";
}
foreach (var query in queries)
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ci"))
{
int ciId;
if (int.TryParse(cia.Split('_')[1], out ciId))
{
var script = new ServiceScriptModule().GetModule(ciId);
select += @" ci" + ciId + "." + query.Field + " as " + "\"" + script.Name + "\"" + ",";
}
}
else if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
var attribute = new ServiceCustomAttribute().GetCustomAttribute(caId);
select += @" ca" + caId + "." + query.Field + " as " + "\"" + attribute.Name + "\"" + ",";
}
}
}
}
var selectTrimmed = select.Trim(',') + " ";
sb.Append(selectTrimmed);
sb.Append(@" FROM computers as a ");
if (queries.Any(x => x.Table == "Bios"))
{
sb.Append("LEFT JOIN bios_inventory b on a.computer_id = b.computer_id ");
}
if (queries.Any(x => x.Table == "System"))
{
sb.Append("LEFT JOIN computer_system_inventory c on a.computer_id = c.computer_id ");
}
if (queries.Any(x => x.Table == "Hard Drive"))
{
sb.Append("LEFT JOIN hdd_inventory d on a.computer_id = d.computer_id ");
}
if (queries.Any(x => x.Table == "OS"))
{
sb.Append("LEFT JOIN os_inventory e on a.computer_id = e.computer_id ");
}
if (queries.Any(x => x.Table == "Printer"))
{
sb.Append("LEFT JOIN printer_inventory f on a.computer_id = f.computer_id ");
}
if (queries.Any(x => x.Table == "Processor"))
{
sb.Append("LEFT JOIN processor_inventory g on a.computer_id = g.computer_id ");
}
if (queries.Any(x => x.Table == "Application"))
{
sb.Append("LEFT JOIN computer_software h on a.computer_id = h.computer_id ");
sb.Append("LEFT JOIN software_inventory i on h.software_id = i.software_inventory_id ");
}
if (queries.Any(x => x.Table == "Windows Update"))
{
sb.Append("LEFT JOIN computer_updates j on a.computer_id = j.computer_id ");
sb.Append("LEFT JOIN wu_inventory k on j.wu_inventory_id = k.wu_inventory_id ");
}
if (queries.Any(x => x.Table == "Firewall"))
{
sb.Append("LEFT JOIN firewall_inventory l on a.computer_id = l.computer_id ");
}
if (queries.Any(x => x.Table == "AntiVirus"))
{
sb.Append("LEFT JOIN antivirus_inventory m on a.computer_id = m.computer_id ");
}
if (queries.Any(x => x.Table == "BitLocker"))
{
sb.Append("LEFT JOIN bitlocker_inventory n on a.computer_id = n.computer_id ");
}
if (queries.Any(x => x.Table == "Logical Volumes"))
{
sb.Append("LEFT JOIN logical_volume_inventory o on a.computer_id = o.computer_id ");
}
if (queries.Any(x => x.Table == "Network Adapters"))
{
sb.Append("LEFT JOIN nic_inventory p on a.computer_id = p.computer_id ");
}
if (queries.Any(x => x.Table == "Certificates"))
{
sb.Append("LEFT JOIN computer_certificates q on a.computer_id = q.computer_id ");
sb.Append("LEFT JOIN certificate_inventory r on q.certificate_id = r.certificate_inventory_id ");
}
var scriptModuleIds = new List <int>();
var customAttributeIds = new List <int>();
foreach (var query in queries)
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ci"))
{
int ciId;
if (int.TryParse(cia.Split('_')[1], out ciId))
{
scriptModuleIds.Add(ciId);
}
}
else if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
customAttributeIds.Add(caId);
}
}
}
}
if (scriptModuleIds.Count > 0)
{
foreach (var moduleId in scriptModuleIds)
{
sb.Append("LEFT JOIN custom_inventory ci" + moduleId + " on a.computer_id = ci" + moduleId + ".computer_id AND ci" + moduleId + ".script_module_id in ");
sb.Append("(" + moduleId + ") ");
}
}
if (customAttributeIds.Count > 0)
{
foreach (var attributeId in customAttributeIds)
{
sb.Append("LEFT JOIN custom_computer_attributes ca" + attributeId + " on a.computer_id = ca" + attributeId + ".computer_id AND ca" + attributeId + ".custom_attribute_id in ");
sb.Append("(" + attributeId + ") ");
}
}
sb.Append("WHERE (");
int counter = 0;
var parameters = new List <string>();
foreach (var query in queries)
{
var tableAs = "";
if (query.Table == "Computer")
{
tableAs = "a";
}
else if (query.Table == "Bios")
{
tableAs = "b";
}
else if (query.Table == "System")
{
tableAs = "c";
}
else if (query.Table == "Hard Drive")
{
tableAs = "d";
}
else if (query.Table == "OS")
{
tableAs = "e";
}
else if (query.Table == "Printer")
{
tableAs = "f";
}
else if (query.Table == "Processor")
{
tableAs = "g";
}
else if (query.Table == "Application")
{
tableAs = "i";
}
else if (query.Table == "Windows Update" && query.Field == "is_installed")
{
tableAs = "j";
}
else if (query.Table == "Windows Update" && query.Field == "install_date")
{
tableAs = "j";
}
else if (query.Table == "Windows Update")
{
tableAs = "k";
}
else if (query.Table == "Firewall")
{
tableAs = "l";
}
else if (query.Table == "AntiVirus")
{
tableAs = "m";
}
else if (query.Table == "BitLocker")
{
tableAs = "n";
}
else if (query.Table == "Logical Volumes")
{
tableAs = "o";
}
else if (query.Table == "Network Adapters")
{
tableAs = "p";
}
else if (query.Table == "Certificates")
{
tableAs = "r";
}
else
{
if (query.Table.StartsWith("("))
{
var cia = query.Table.Trim('(').Trim(')');
var ciaType = cia.Split('_').First();
if (ciaType.Equals("ci"))
{
int ciId;
if (int.TryParse(cia.Split('_')[1], out ciId))
{
tableAs = "ci" + ciId;
}
}
else if (ciaType.Equals("ca"))
{
int caId;
if (int.TryParse(cia.Split('_')[1], out caId))
{
tableAs = "ca" + caId;
}
}
}
}
counter++;
sb.Append(query.AndOr + " " + query.LeftParenthesis + " " + tableAs + "." + query.Field + " ");
sb.Append(query.Operator + " @p" + counter + " " + query.RightParenthesis);
parameters.Add(query.Value);
}
if (queries.First().IncludeArchived&& queries.First().IncludePreProvisioned)
{
sb.Append(") AND (a.provision_status = 8 OR a.provision_status = 11 OR a.provision_status = 6)");
}
else if (queries.First().IncludeArchived)
{
sb.Append(") AND (a.provision_status = 8 OR a.provision_status = 11)");
}
else if (queries.First().IncludePreProvisioned)
{
sb.Append(") AND (a.provision_status = 8 OR a.provision_status = 6)");
}
else
{
sb.Append(") AND a.provision_status = 8");
}
var gb = BuildGroupBy(queries.First().GroupBy);
if (!string.IsNullOrEmpty(gb))
{
sb.Append(gb);
}
sqlQuery.Sql = sb.ToString();
sqlQuery.Parameters = parameters;
return(sqlQuery);
}
public string Execute(string multicastPort = "")
{
var preScripts = "\"";
var beforeFileScripts = "\"";
var afterFileScripts = "\"";
foreach (var script in _imageProfileServices.GetImageProfileScripts(_imageProfile.Id))
{
if (script.RunWhen == EnumProfileScript.RunWhen.BeforeImaging)
{
preScripts += script.ScriptModuleId + " ";
}
else if (script.RunWhen == EnumProfileScript.RunWhen.BeforeFileCopy)
{
beforeFileScripts += script.ScriptModuleId + " ";
}
else if (script.RunWhen == EnumProfileScript.RunWhen.AfterFileCopy)
{
afterFileScripts += script.ScriptModuleId + " ";
}
}
beforeFileScripts += "\"";
afterFileScripts += "\"";
preScripts += "\"";
var sysprepTags = "\"";
foreach (var sysprepTag in _imageProfileServices.GetImageProfileSysprep(_imageProfile.Id))
{
sysprepTags += sysprepTag.SysprepModuleId + " ";
}
sysprepTags = sysprepTags.Trim();
sysprepTags += "\"";
var areFilesToCopy = _imageProfileServices.GetImageProfileFileCopy(_imageProfile.Id).Any();
//On demand computer may be null if not registered
if (_computer != null)
{
AppendString("computer_name=" + _computer.Name.Split(':').First());
//AppendString("computer_id=" + _computer.Id);
}
AppendString("image_name=" + _imageProfile.Image.Name);
AppendString("profile_id=" + _imageProfile.Id);
AppendString("pre_scripts=" + preScripts);
AppendString("before_file_scripts=" + beforeFileScripts);
AppendString("after_file_scripts=" + afterFileScripts);
AppendString("file_copy=" + areFilesToCopy);
AppendString("sysprep_tags=" + sysprepTags);
if (Convert.ToBoolean(_imageProfile.WebCancel))
{
AppendString("web_cancel=true");
}
AppendString("task_completed_action=" + "\"" + _imageProfile.TaskCompletedAction + "\"");
if (ServiceSetting.GetSettingValue(SettingStrings.ImageDirectSmb).Equals("True"))
{
AppendString("direct_smb=true");
}
if (_direction.Contains("upload"))
{
AppendString("image_type=" + _imageProfile.Image.Type);
if (Convert.ToBoolean(_imageProfile.RemoveGPT))
{
AppendString("remove_gpt_structures=true");
}
if (Convert.ToBoolean(_imageProfile.SkipShrinkVolumes))
{
AppendString("skip_shrink_volumes=true");
}
if (Convert.ToBoolean(_imageProfile.SkipShrinkLvm))
{
AppendString("skip_shrink_lvm=true");
}
AppendString("compression_algorithm=" + _imageProfile.Compression);
AppendString("compression_level=-" + _imageProfile.CompressionLevel);
if (Convert.ToBoolean(_imageProfile.UploadSchemaOnly))
{
AppendString("upload_schema_only=true");
}
if (Convert.ToBoolean(_imageProfile.SimpleUploadSchema))
{
AppendString("simple_upload_schema=true");
}
if (!string.IsNullOrEmpty(_imageProfile.CustomUploadSchema))
{
AppendString("custom_upload_schema=true");
SetCustomSchemaUpload();
}
if (Convert.ToBoolean(_imageProfile.SkipBitlockerCheck))
{
AppendString("skip_bitlocker_check=true");
}
if (Convert.ToBoolean(_imageProfile.SkipHibernationCheck))
{
AppendString("skip_hibernation_check=true");
}
}
else // push or multicast
{
//check for null in case of on demand
if (_computer != null)
{
var computerAttributes = new ServiceComputer().GetCustomAttributes(_computer.Id);
var serviceAttribute = new ServiceCustomAttribute();
foreach (var attribute in computerAttributes)
{
var compAttrib = serviceAttribute.GetCustomAttribute(attribute.CustomAttributeId);
if (!string.IsNullOrEmpty(attribute.Value))
{
if (compAttrib.ClientImagingAvailable)
{
AppendString(compAttrib.Name + "=" + "\"" + attribute.Value + "\"");
}
}
}
}
if (Convert.ToBoolean(_imageProfile.ChangeName))
{
AppendString("change_computer_name=true");
}
if (Convert.ToBoolean(_imageProfile.SkipExpandVolumes))
{
AppendString("skip_expand_volumes=true");
}
if (Convert.ToBoolean(_imageProfile.FixBcd))
{
AppendString("fix_bcd=true");
}
if (Convert.ToBoolean(_imageProfile.RandomizeGuids))
{
AppendString("randomize_guids=true");
}
if (Convert.ToBoolean(_imageProfile.ForceStandardLegacy))
{
AppendString("force_legacy_layout=true");
}
if (Convert.ToBoolean(_imageProfile.ForceStandardEfi))
{
AppendString("force_efi_layout=true");
}
if (Convert.ToBoolean(_imageProfile.SkipNvramUpdate))
{
AppendString("skip_nvram=true");
}
if (Convert.ToBoolean(_imageProfile.FixBootloader))
{
AppendString("fix_bootloader=true");
}
if (Convert.ToBoolean(_imageProfile.ForceDynamicPartitions))
{
AppendString("force_dynamic_partitions=true");
}
AppendString(SetPartitionMethod());
if (!string.IsNullOrEmpty(_imageProfile.CustomSchema))
{
AppendString("custom_deploy_schema=true");
SetCustomSchemaDeploy();
}
if (_direction.Contains("multicast"))
{
var comServer = new ServiceClientComServer().GetServer(_comServerId);
if (comServer.DecompressImageOn == "client")
{
AppendString("decompress_multicast_on_client=true");
}
if (string.IsNullOrEmpty(_imageProfile.ReceiverArguments))
{
AppendString("client_receiver_args=" + "\"" + comServer.MulticastReceiverArguments + "\"");
}
else
{
AppendString("client_receiver_args=" + "\"" + _imageProfile.ReceiverArguments + "\"");
}
AppendString("multicast_port=" + multicastPort);
AppendString("multicast_server_ip=" + comServer.MulticastInterfaceIp);
}
}
return(_activeTaskArguments.ToString());
}
