Improve querying of Extended Events target file

As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I’d like to periodically (Every couple of minutes) query the event file to collect the data. I have been using the below to query data using xQuery, but it seems to be quite slow. I am aware of using a fileoffset as an option to optimise, but beyond that, are there any ways in which I can better improve predicates as been below?

    SELECT           event_data         ,n.value('@timestamp', 'DATETIME2') DtTimeStamp         ,n.value('(action[@name="collect_cpu_cycle_time"]/value)[1]', 'bigINT') CollectCpuCycleTime         ,n.value('(action[@name="collect_system_time"]/value)[1]', 'DATETIME2') CollectSystemTime         ,n.value('(action[@name="last_error"]/value)[1]', 'varchar(255)') LastError         ,n.value('(action[@name="collect_system_time"]/value)[1]', 'datetime2')             CollectSystemTime         ,n.value('(action[@name="task_time"]/value)[1]', 'bigint')                          TaskTime         ,n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)')              ClientAppName         ,n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(255)')              ClientHostName         ,n.value('(action[@name="database_name"]/value)[1]', 'varchar(255)')                DatabaseName         ,n.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)')                  NtUserName         ,n.value('(action[@name="server_instance_name"]/value)[1]', 'varchar(255)')         InstanceName         ,n.value('(action[@name="session_id"]/value)[1]', 'INT')                            SessionID         ,n.value('(action[@name="client_pid"]/value)[1]', 'INT')                            ClientPID         ,n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)')                         SQLText     FROM          (             SELECT                  CAST(event_data as XML) event_data             FROM                  sys.fn_xe_file_target_read_file('C:\Temp\EE_QueryWarnings*.xel', null, null, null)         ) ed     OUTER APPLY         ed.event_data.nodes('event') (n)     WHERE         n.value('@name', 'varchar(MAX)')    = 'missing_column_statistics'     AND         n.value('@timestamp', 'DATETIME2')   >= DATEADD(MINUTE,-10,GETUTCDATE()); 

Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow

We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type.

There are few other columns. When we query the table with WHERE clause on the clustered index columns, the result is received immediately, even when the result set is tens of thousands of rows.

However, if we add one more predicate on the where clause, the query is super slow. After ten seconds I just stop it, because that is not useful to us.

The table has 200+ million rows.

This is the query that is super fast:

SELECT * FROM Table WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' 

This is the query that is super slow:

SELECT * FROM Table WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' AND BooleanColumn = 1 

In my mind, the second query should use the clustered index to search for rows and then simply scan the result set to filter out what’s needed.

Is it possible to (somehow) make the second query work fast without creating additional non clustered index that would include other columns that we need to filter on?

Querying in Advanced REST client in Chrome [closed]

Please tell me how you can form a request in the Advanced REST client in Chrome

for example, when you go to this address https://news.s7.ru/news, a news page opens; how can I see which request is sent when I enter text in the search bar?

And please tell me how, through the Advanced REST client, make a request that searches for news on the phrase “flight”, while giving 20 news? through json request to do? Which json to enter?

Chain of responsibility or simple dependency injection for Querying API

I am working on refactoring a project code that is wired up in a tangled way. It was started with decent dependency injection and over time with all custom requirements it looks it got tangled up. This is the outline of the service functionality

1. Get the request from the user 2. Infer some business logic based on the user and request information - Determine user access privileges, get data from external services to help with building the query, etc 3. build a complex query to send to backend  4. execute the query by sending it to backend and get the results  5. Any postprocessing that needs to be done on the results (do some last mile filtering of results, decorate it with more data, etc) 6. Build a response using the results we got from line 5 and return it back 

I have two slight variants in mind now and i would like to get some suggestions on which option is better.

Option 1

Break down some objects by following single responsibility principle and then wire up the objects in a simple way  RequestObjectBuilder UserAccessInformation     extractAccessInformation BusinessLogic1Extractor     extract business logic by calling external services BusinessLogic2Extractor     extract business logic by calling external services  QueryBuilder - use all dependencies and input and build the query (Query Building would be broken down into pieces and dependencies would be injected where needed)     UserAccessInformation     BusinessLogic1Extractor     BusinessLogic2Extractor      String buildQuery(RequestInput request)           ResponseBuilder     PostProcessingBusinessLogic1     buildResponse()  

Option 2

Break down some objects by following single responsibility principle and chain them together using chain of responsibility pattern       PreprocessorChain   - order of preprocessor matters a lot when one is dependent on some fields populated in context by other         void run(List<Preprocessor> processes)      Preprocessor  - each preprocessor will read input and context and update the context back         void preprocess(RequestContext context, RequestInput request)         QueryBuilder - read the context and input and build the query         String buildQuery((RequestContext context, RequestInput request)                         PostProcessorChain         void postprocess(ResponseContext context, ResponseOutput response)  

I feel that option 2 would give me flexibility but I dont like the idea of passing around a context object and use that as both input and output. THe problem with option 1 is, it doenst abstract out preprocessors and postprocessors very well. Should i go for a hybrid approach ? Any suggestions from your experience?

External users’ limitations inside sharepoint online sites, regarding cross-site querying and search

I was reading this article about External users in SharePoint online:-

https://blogs.technet.microsoft.com/lystavlen/2013/04/14/understanding-external-users-in-sharepoint-online/

and the author mentioned the following limitation which external users will face inside sharepoint online:-

By default, external users cannot access the Search Center and will not be able to execute searches against “everything” (cross site collection search)

so i am not sure what is this limitation exactly, what is "Search Center and will not be able to execute searches against “everything"? For example let say i as the site admin, create a dash board page using Content Search web parts, where inside the Content Search web-part i will be querying items/documents from multiple site collections, so will the external user be able to view and access those dashboards and their results?

also let say I create a page and i added a Script Editor web part which contain some JavaScript code, to query some lists and libraries using the REST API. so will the external user be able to view the results of those API calls ? which might query cross-site data??

How to prevent querying each user each time when having per microservice authorization? [on hold]

I am trying to find a good approach for authentication and authorization of a user in a microservice-application.

Currently I have the following idea:

authentication and authorization using JWT

In regards to the sequence diagram I have the following questions:

  1. [step 12] and [step 16] check for a permission, which is necessary on the server-side. Where are those permissions supposed to be stored?
  2. What about the note (after [step 20])? Possibilities:
    1. Issue an additional signed Token, that contains permissions / roles for the message service. This token can be acquired during [step 13].
    2. Re-issue existing JWT, include permissions / roles for the message service. This token can be acquired during [step 13].
    3. Issue a server-side request, that returns information about whether a certain GUI-area should be displayed.

If in 2.1 or 2.2 the client-token is modified, the user is able to access GUI-areas, which should not be available to him. However: any subsequent service calls will not succeed, because the modified (nor the original) token permits access to corresponding server-calls to the user.

In 2.3 the microservice is queried each time the GUI has to decide whether to display a GUI-area or not.

Restful API design for querying Batch Jobs

I am creating a restful web service for querying the state of BatchJobs. BatchJobs are periodic jobs (to execute some command or set of commands) that run daily or multiple times in a day, each run of a batchjob is identified by the batchjob name and the key associated with it.

For example, different runs of a batchjob with name ‘crawl_amazon’ (that runs every hour a day) will be identified as

crawl_amazon 05292019 RUN_1  crawl_amazon 05292019 RUN_2  .... .... crawl_amazon 05292019 RUN_24 crawl_amazon 05302019 RUN_1  ... ... ... 

I want to create a pure restful interface for querying the batchjobs (something similar to stackexchange https://api.stackexchange.com/docs) but I am not sure how the endpoints should look like. Any suggestion here is really appreciated.

Note that batchjob has many properties associated with it. For example: name, key, run_host, commands, user, start_time, end_time …

Apache 2: querying not existing subdomain is opening the first subdomain

I’ve configured several subdomains in apache within sites-available:

#first.domain.me <VirtualHost first.domain.me> DocumentRoot /var/www/first.domain.me <Directory "/var/www/first.domain.me"> allow from all Options None Require all granted </Directory> ServerName first.domain.me </VirtualHost> 

and

#second.domain.me <VirtualHost second.domain.me> DocumentRoot /var/www/second.domain.me <Directory "/var/www/second.domain.me"> allow from all Options None Require all granted </Directory> ServerName second.domain.me </VirtualHost> 

When I query the non-existing-domain notexisting.domain.me from the browser the URL is still notexisting.domain.me but the content is the one of first.domain.me

I can’t understand why. I would want to redirect to www.domain.me or give a 404 error but everytime the first domain content is opened. As you see there are no mod-rewrite directives and I’ve nothing in 000-default.conf

This is the content of 000-default:

<VirtualHost *:*>     Redirect permanent / http://www.domain.me </VirtualHost> 

And this is the content of www.domain.me.conf:

<VirtualHost www.domain.me> DocumentRoot /var/www/www <Directory "/var/www/www"> allow from all Options None Require all granted </Directory> ServerName www.domain.me </VirtualHost> 

Can you explain me why the first.domain.me is matching all the other domains like dhiashdiuahidhau.domain.me ? How can i fix it?

Oauth2/OIDC: I know the address of a resource server, is there a standard for querying which authorization servers it accepts?

I have a bunch of what are essentially IOT devices that will authenticate connections using oauth 2 tokens and/or openid connect (depending on use-case). Each IOT device is individually configurable with one or more authorization servers (ie. each has a client-id/client-secret with one or more auth servers).

My problem is that, given the URI/address of the IOT device, is there an RFC or standard for determining the address(es) of authorization servers that IOT device trusts?

I am going to build a console-like application that will need to be able to connect to many (thousands) of these devices. I control the code on the IOT device and can build something proprietary in, but I’m trying to follow relevant RFCs, where applicable. I have thus far found no specification for querying a resource server to determine the configured authorization servers.

Querying based on condition

I am using Entity Framework Core as ORM in my ASP.NET Core 2.1 project. I want to perform server-side pagination in Career Controller for getting list of careers. I have a model which looks like:

 public class Career     {         private string _title, _description;          [Key]         public long career_id { get; set; }         [Required]         [MaxLength(50)]         public string title         {             get => _title;             set             {                 if (string.IsNullOrWhiteSpace(value))                 {                     throw new NonEmptyValueException("Title is required.");                 }                 _title = value;             }         }          [Required]         public DateTime opening_date { get; set; } = DateTime.Now;         public DateTime? closing_date { get; set; }          [Required]         [MaxLength(2000)]         public string description         {             get => _description;             set             {                 if (string.IsNullOrWhiteSpace(value))                 {                     throw new NonEmptyValueException("Description is required.");                 }                 _description = value;             }         }          [MaxLength(70)]         public string image_name { get; set; }          public bool is_closed { get; set; } = false;          public void markClosed()         {             is_closed = true;             closing_date = DateTime.Now;         }          public void markUnclosed()         {             is_closed = false;             closing_date = null;         }     } 

This is how my DbContext looks like:

 public class AppDbContext : DbContext     {         public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)         {          }             public DbSet<Career> careers { get; set; }     } 

Currently, I am doing this way to perform server side pagination in Controller:

public IActionResult Index(CareerFilter filter = null)         {             try             {                 var careers = _careerRepo.getQueryable();                  if (!string.IsNullOrWhiteSpace(filter.title))                 {                     careers = careers.Where(a => a.title.Contains(filter.title));                 }                  careers = careers.Where(a => a.opening_date >= filter.from_date && a.opening_date <= filter.to_date);                  ViewBag.pagerInfo = _paginatedMetaService.GetMetaData(careers.Count(), filter.page, filter.number_of_rows);                   careers = careers.Skip(filter.number_of_rows * (filter.page - 1)).Take(filter.number_of_rows);                  return View(careers.ToList());             }             catch (Exception ex)             {                 AlertHelper.setMessage(this, ex.Message, messageType.error);                 return Redirect("/admin");             }         } 

This is how my getQueryable() method in repository looks like:

public IQueryable<Career> getQueryable() {  return _appDbContext.Set<Career>(); } 

I am personally not satisfied with the way I am exposing queryable and performing all conditional query concatenation in Controller. Controllers looked messy with increase in number of conditions. Please suggest me a better way to perform same action.