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

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

jpsolognier — Fri, 20 Oct 2017 09:11:45 GMT

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)

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

gjanders — Fri, 20 Oct 2017 11:05:43 GMT

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...

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

jpsolognier — Fri, 20 Oct 2017 11:11:58 GMT

Hi,

Yes they do.

Selected Fields

Archief 100+

DatumWachtkamer 100+

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

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

gjanders — Fri, 20 Oct 2017 11:25:16 GMT

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)

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

gcusello — Fri, 20 Oct 2017 11:28:52 GMT

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

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

jpsolognier — Fri, 20 Oct 2017 11:44:31 GMT

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).

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

jpsolognier — Fri, 20 Oct 2017 11:46:05 GMT

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.

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

gcusello — Fri, 20 Oct 2017 11:54:54 GMT

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

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

jpsolognier — Fri, 20 Oct 2017 12:05:37 GMT

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.

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

gcusello — Fri, 20 Oct 2017 12:32:29 GMT

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