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()); }