Query no Longer Returning Correct Results

[Rak]

Hello,

We have a query for an alert that was working prior, but is no longer returning the correct results. We haven't changed anything on our instance, so I'm not sure as to what would be the cause. Query is below (I blanked out the index names, etc of course). I tested it with an different query which is returning the expected results, but I'd like to figure out what's going on with this one.

index=testindex OR index=testindex2 source="insertpath" ErrorCodesResponse=PlanInvalid
| search TraceId=*
| stats values(TraceId) as TraceId
| mvexpand TraceId
| join type=inner TraceId
[search index=test ("Test SKU")
| fields TraceId,@t,@mt,RequestPath]
| eval date=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%Y-%m-%d"), time=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%H:%M")
| table time, date, TraceId, @MT,RequestPath

[gcusello]

Hi @Rak,

probably the issue is that the subsearch used in the join command has more than 50,000 results and this limit gives you incomplete results.

In addition there's an error in your search: it isn't useful to have a main search and then a seach command.

At least the jin command is to avoid or to use when there isn't any other result, because it'0s very slow and resource consuming.

So please to rethink your search following my approach:

(index=testindex OR index=testindex2 source="insertpath" ErrorCodesResponse=PlanInvalid TraceId=*) OR (index=test ("Test SKU"))
| eval type=if(index="test","2","1")
| stats 
     earliest('@t') AS '@t'
     values('@m') AS '@m'
     values(RequestPath) AS RequestPath
     dc(type) AS type_count
     BY TraceId
| where type_count=2
| eval date=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%Y-%m-%d"), time=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%H:%M")
| table time, date, TraceId, @MT,RequestPath

I'm not sure if the check on the number of types is relevant or not.

Ciao.

Giuseppe

[Rak]

@gcusello 

Thanks for replying!

That pull results, but it's doesn't populate anything in the statistic tab, which is our main issue (old query shows 20, alternate one that we don't really want to use shows 1700).

The total results are around 38k as well so we're not going over, but definitely I think correcting the search issue is a good idea.

Any ideas about the statistics piece?

[gcusello]

Hi @Rak ,

at first, check the condition of the presence in both the main searches.

Then, if you have the stats command you should have statistics, it's strange if you haven't, did you copied all my search, with also the stats command?

Otherwise, please try this:

(index=testindex OR index=testindex2 source="insertpath" ErrorCodesResponse=PlanInvalid TraceId=*) OR (index=test ("Test SKU"))
| eval type=if(index="test","2","1")
| stats 
     earliest('@t') AS '@t'
     values('@m') AS '@m'
     values(RequestPath) AS RequestPath
     dc(type) AS type_count
     BY TraceId
| where type_count=2
| eval date=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%Y-%m-%d"), time=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%H:%M")
| fields - '@t'

Ciao.

Giuseppe

[Rak]

@gcusello 

Yeah it's odd. Neither of those 2 return any stats results (I checked to make sure I copied the whole query, updated as appropriate for indexes etc.)

The original query is only giving 20 entries under stats (and far less results) which used to work so that's also weird. What we've been doing is something along the lines of this:

index=test OR index=test2 source="insertpath" ErrorCodesResponse=TestError TraceId=*
| fields TraceId
| append
[ search index=test "Test SKU" AND @mt !="TestAsync: Request(Test SKU: )*"
| fields TraceId, @t, @mt, RequestPath | where isnotnull('@t') AND isnotnull('@mt') AND match('@mt', "Test SKU: *") ]
| eval date=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%Y-%m-%d"), time=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%H:%M")
| stats values(date) as date values(time) as time values(@mt) as message values(RequestPath) as Path by TraceId
| where isnotnull(date) AND isnotnull(time) AND isnotnull(message)
| table date, time, TraceId, message, Path

This seems to work better than our old search but I'd prefer to try and figure out yours as it's not using those appended searches.

[bowesmana]

Wondering if this will work for you. It puts both datasets in the outer query. The first stats will pull all fields together by TraceID, then the where will remove those without data.

The @t will contain multivalue dates which will get converted and then your next stats will collapse any duplicates.

(index=test OR index=test2 source="insertpath" ErrorCodesResponse=TestError TraceId=*) OR
    (index=test "Test SKU" AND @MT !="TestAsync: Request(Test SKU: )*") 

| fields TraceId, @t, @MT, RequestPath 
| stats values(*) as * by TraceId 
| where isnotnull('@t') AND isnotnull('@mt') AND match('@mt', "Test SKU: *") 

| eval date=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%Y-%m-%d"), time=strftime(strptime('@t', "%Y-%m-%dT%H:%M:%S.%6N%Z"), "%H:%M") 
| stats values(date) as date values(time) as time values(@mt) as message values(RequestPath) as Path by TraceId 
| where isnotnull(date) AND isnotnull(time) AND isnotnull(message) 
| table date, time, TraceId, message, Path

 There may be more optimisations depending on your data.

[gcusello]

Hi @Rak ,

surely append and stats is better than join,

but anyway, I hint to analyze my approach and try to use it because is faster and it hasn't the limit of 50,000 results in the subsearch.

Ciao.

Giuseppe

[Rak]

@gcusello 

I'm not entirely sure what you're referring to to be honest. Our subsearch is well under 50k results so that shouldn't be the issue. But I appreciate you trying to assist. I'll see if I can puzzle it out.

[gcusello]

Hi @Rak ,

good for you, see next time!

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉