topic Re: Convert this query to a data model in Knowledge Management

Convert this query to a data model

DEAD_BEEF — Wed, 14 Mar 2018 20:22:58 GMT

Hello all! I am having problems understanding how to structure my query to a data model with the ultimate goal being for it to be an accelerated data model as the existing search takes much too long to run. The problem I am having is breaking out my query into child queries from the root event and not understanding where to put the constraints (on the parent above or on the child).

I ask that you take a look at this query and provide any input so I can better understand how to build this to an accelerated data model so that I can get the results of this query faster. Maybe this isn't possible or doesn't benefit since I am searching across all indexes and just better to look at at scheduled search? I use this query to find systems that have incorrect time zone settings. Perhaps am I just not understanding what data models are for and attempting to use it in a way it was not designed.

index=* OR index=_*
| dedup host,source
| eval lag=_time-_indextime
| search lag>1000 OR lag<-1000
| convert ctime(_indextime) AS "Index Time"
| convert ctime(_time) AS "Log Time"
|table "Log Time","Index Time",lag,index,host,source
| rename range AS TZ

Re: Convert this query to a data model

tiagofbmm — Wed, 14 Mar 2018 22:24:57 GMT

Creating a datamodel for a single query seems too much. Why don't you save it as a scheduled search and accelerate it?

Re: Convert this query to a data model

DEAD_BEEF — Thu, 15 Mar 2018 12:34:16 GMT

Hmm, looks like it can't be accelerated because it doesn't have a transforming command. Trying to rewrite the query to include one to check the box.

Re: Convert this query to a data model

tiagofbmm — Tue, 29 Sep 2020 18:32:53 GMT

You query seems to not be very accurate. dedup host and source will give you one specific value for that, and if you are trying to understand the lag time of a (source,host) pair, you;d do some stats first.

index=* OR index=_*
| eval lag=_time-_indextime
| stats avg(lag) as lag by source,host
| search lag>1000 OR lag<-1000

Re: Convert this query to a data model

DEAD_BEEF — Thu, 15 Mar 2018 17:07:06 GMT

I see what you mean. I changed it around based on your suggestions but now the index and log time columns do not populate. Any idea why?

index=* OR index=_
| convert ctime(_indextime) AS "Index Time"
| convert ctime(_time) AS "Log Time"
| eval lag=_time-_indextime
| stats avg(lag) as lag by source,host
| search lag>1000 OR lag<-1000
| table "Log Time","Index Time",lag,index,host,source

Re: Convert this query to a data model

tiagofbmm — Tue, 29 Sep 2020 18:33:04 GMT

Sorry maybe I was no clear enough.

The stats I put there was to help you get some more information than just what you had with dedup (which gave you information only from one event per (source,host) pair).

I think you have two paths here. Either you want to see the Log_Time, Index_Time for all the events, and you can do the following (which is messy as it shows you all the events:

 index=* OR index=_
 | convert ctime(_indextime) AS "Index Time"
 | convert ctime(_time) AS "Log Time"
 | eval lag=_time-_indextime
 | search lag>1000 OR lag<-1000
 | table "Log Time","Index Time",lag,index,host,source

What I would do is make some stats on top of that lag,

 index=* OR index=_
 | convert ctime(_indextime) AS "Index Time"
 | convert ctime(_time) AS "Log Time"
 | eval lag=_time-_indextime
 | bucket _time
 | stats avg(lag) as lag by source,host, "Index Time"
 | search lag>1000 OR lag<-1000
 | table  "Index Time",lag,index,host,source

I believe this last one gives you the information you actually are looking for.

Let me know

Re: Convert this query to a data model

DEAD_BEEF — Mon, 19 Mar 2018 19:43:32 GMT

Yup, the second query was what I was looking for. Much appreciated!