topic Re: Build a query by last date in Splunk Search

How to build a query by last date

Maik11 — Wed, 23 Feb 2022 16:39:15 GMT

Hello All,

I need some help please.

I would like to query for the last upddate. However, the field belegtyp and pdid can also change.

I need the last upddate for them all ( last upddate when belegtyp for pdid change).

Thats my query:

| eval crdate=strptime(crdate,"%Y-%m-%d") | eval crdate=strftime(crdate,"%Y-%m-%d") | eval upddate=strptime(upddate,"%Y-%m-%d") | eval upddate=strftime(upddate,"%Y-%m-%d") | search belegnummer=177287 | stats last(upddate) by upddate crdate belegnummer belegtyp pdid

It hasn´t work so far with

| sort -upddate | stats last (upddate) by ... | stats first (upddate) by...

I don't know why it doesn't work.

Hope to get some help on this, thanks in advance.

Re: Build a query by last date

gcusello — Wed, 23 Feb 2022 09:24:30 GMT

Hi @Maik11,

let me understand:

you extracted two fields (crdate and upddate) that are the creation and update dates of an object called "belegnummer".

You want to know if there are more than one "update" for each belegnummer, is it correct?

if this is your need, you have to run something like this:

index=your_index belegnummer=177287 | eval crdate_epoch=strptime(crdate,"%Y-%m-%d") | eval upddate_epoch=strptime(upddate,"%Y-%m-%d") | stats dc(update_epoch) AS dc_update_epoch last(upddate) by belegnummer | where dc_update_epoch>1

See my approach and adapt it to you need.

Ciao.

Giuseppe

Re: Build a query by last date

ITWhisperer — Wed, 23 Feb 2022 09:30:34 GMT

Does this get you what you want?

| stats latest(belegtyp) as belegtyp latest(pdid) as pdid by belegnummer

Re: Build a query by last date

Maik11 — Wed, 23 Feb 2022 09:44:03 GMT

Hi,

thanks for your help. But your suggestions unfortunately don't work too.

I have several updates where the belegtyp of one or more pdid can change.

In my example it is just one belegnummer. Usually these are thousands belegnummer.

I need the last update on which the belegtyp from pdid of the belegnummer changes.

Because the belegtyp can change between 1 to 10 .

Thanks!

Re: Build a query by last date

gcusello — Wed, 23 Feb 2022 09:48:46 GMT

Hi @Maik11,

you have to enlarge your stats command:

index=your_index belegnummer=177287 | eval crdate_epoch=strptime(crdate,"%Y-%m-%d") | eval upddate_epoch=strptime(upddate,"%Y-%m-%d") | stats dc(belegtyp) AS dc_belegtyp last(upddate) by pid belegnummer | where dc_belegtyp>1

Anyway, try to apply my approach to your Use Case, it's the best way to learn!

Ciao.

Giuseppe

Re: Build a query by last date

Maik11 — Wed, 23 Feb 2022 16:38:16 GMT

Hi Guiseppe,

I understand your suggestions. But it only counts me which pdid and belegnummer has more than one belegtyp.

In this example I get this back.

But I need back, from ZX5165 = belegtyp 6 (second) and from ZX5166, ZX5167 and ZX5168 = belegtyp=7.

It is the last change of the update in my query.

Thanks!