Splunk Search
Highlighted

Need to make a new field with values of new made fields from two indexes

New Member

Hi,

Maybe a simple question, but im struggling with it.
I would like to make a new field with eval which consist of two previously made fields with contents out of two indexes.
Here is my search query. The new field Dagen does not appear as a new field.
Can someone tell what im doing wrong?

index=1 OR index=2
| dedup ID

| eval Archief=strptime(field1, "%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2, "%Y-%m-%d %H:%M:%S.%N")

| eval Dagen=floor((Archief-DatumWachtkamer)/86400)

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

SplunkTrust
SplunkTrust

The syntax looks valid, do the fields Archief and DatumWachtkamer both exist in the output?

The documentation for date and time functions advises that the strptime will convert into a timestamp format (epoch) so I'd expect your query to work...

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

New Member

Hi,

Yes they do.

Selected Fields

Archief 100+

DatumWachtkamer 100+

But the last step it doesnt work seems like, so im lost.

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

SplunkTrust
SplunkTrust

Try:

index=1 OR index=2 Archief=* DatumWachtkamer=*
| dedup ID 
| eval Archief=strptime(field1, "%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2, "%Y-%m-%d %H:%M:%S.%N") 
| eval Dagen=floor((Archief-DatumWachtkamer)/86400)
0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

New Member

Thanks for your suggestion, but it wil not work because of the following:

Archief and DatumWachtkamer are also fields made with eval with time contents from two existing fields that are in two indexes.

index=1 OR index=2
| dedup ID
| eval Archief=strptime(field1(existing field in index1),"%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2(existing field in index1),"%Y-%m-%d %H:%M:%S.%N")

This gives me two fields with epoch time content new fields Archief and DatumWachtkamer.

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

Legend

Hi jpsolognier,
debug your results:

index=1 OR index=2
| dedup ID 
| eval Archief=strptime(field1,"%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2,"%Y-%m-%d %H:%M:%S.%N") 
| eval Dagen=floor((Archief-DatumWachtkamer)/86400)
| table ID Archief DatumWachtkamer Dagen

In addition I don't understand a think: you said that you have Archief and DatumWachtkamer in different indexes (index=1 and index=2) and that you can put in relation them using ID, but how can you be sure to have all the values if you dedup by ID? probably you discard some events in this way.
Probably it could be better something like this:

index=1 OR index=2
| stats earliest(strptime(field1,"%Y-%m-%d %H:%M:%S.%N")) AS Archief earliest(strptime(field2,"%Y-%m-%d %H:%M:%S.%N")) AS DatumWachtkamer BY ID 
| eval Dagen=floor((Archief-DatumWachtkamer)/86400)
| table ID Archief DatumWachtkamer Dagen

I used earliest but you could also use latest.

Bye.
Giuseppe

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

New Member

Hi Giuseppe,

Archief and DatumWachtkamer are also fields made with eval with time contents from two existing fields that are in two indexes.

index=1 OR index=2
| dedup ID
| eval Archief=strptime(field1(existing field in index1),"%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2(existing field in index1),"%Y-%m-%d %H:%M:%S.%N")

This gives me two fields with epoch time content new fields Archief and DatumWachtkamer.

| dedup ID is used to remove de doubles.

This is the last piece that is not working: | eval Dagen=floor((Archief-DatumWachtkamer)/86400).

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

Legend

if you run

 index=1 OR index=2
 | dedup ID 
 | eval Archief=strptime(field1,"%Y-%m-%d %H:%M:%S.%N"), DatumWachtkamer=strptime(field2,"%Y-%m-%d %H:%M:%S.%N") 
 | eval Dagen=floor((Archief-DatumWachtkamer)/86400), test=Archief-DatumWachtkamer, Test1=(Archief-DatumWachtkamer)/86400)
 | table ID Archief DatumWachtkamer Dagen Test Test1

do you have all the fields in all the rows?
debug it showing all fields to understand where is the problem.

Bye.
Giuseppe

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

New Member

The Archief field is empty, after putting the results in the table. Thats the problem it seems like.
This is why the field Dagen is not present with values.
Dont understand why.

But in this step | eval Archief=strptime(field1,"%Y-%m-%d %H:%M:%S.%N") i do see data:
Selected Fields

Archief 100+

And with this step | eval Dagen=floor((Archief-DatumWachtkamer)/86400) the values are not there.

Weird.

0 Karma
Highlighted

Re: Need to make a new field with values of new made fields from two indexes

Legend

Hi jpsolognier,
probably the problem is the one I described in my first answer: using dedup you delete some events that contain the missed fields.

Try with the search I suggested

 index=1 OR index=2
 | stats earliest(strptime(field1,"%Y-%m-%d %H:%M:%S.%N")) AS Archief earliest(strptime(field2,"%Y-%m-%d %H:%M:%S.%N")) AS DatumWachtkamer BY ID 
 | eval Dagen=floor((Archief-DatumWachtkamer)/86400)
 | table ID Archief DatumWachtkamer Dagen

In this way you should have all fields, if something is missing means that you haven't this value.

Bye.
Giuseppe

0 Karma