Splunk Search

How to copy latitude longitude values from previous record to current record?

suchi01
New Member

Hi,

I have a scenario in which I have to copy latitude longitude values of a credit card, from a previous record having latitude longitude values present in it.

The record has following parameters:- credit card number,Status id, Latiutude, Longitude, Timestamp.
The values should be copied only if "status id" of current record is 10 and "latitude" "longitude" values are not present.
In such case the latitude and longitude values of previous record, having same credit card number, which occurred 1 hour before the current record, should be copied to the latitude longitude values of the current record.

Please guide me on how could this be implemented.

Tags (1)
0 Karma

jpolvino
Builder

This might get you close, using a Run Anywhere example:

| makeresults 
| eval data="2019-12-26 15:35:49,5523111122221111,1,45.11111,122.11111;
2019-12-26 15:36:12,5523111122222222,1,45.22222,123.22222;
2019-12-26 15:36:40,5523111122223333,1,45.33333,123.33333;
2019-12-26 15:37:22,5523111122221111,10,,;
2019-12-26 15:43:03,5523111122225555,1,45.55555,123.55555;
2019-12-26 17:28:13,5523111122225555,10,,"
| makemv data delim=";" | mvexpand data | rex field=data "(\s|\n?)(?<data>.*)" | makemv data delim=","
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"),
     ccNum=mvindex(data,1),
     statusId=mvindex(data,2),
     latitude=mvindex(data,3),
     longitude=mvindex(data,4)
| fields _time ccNum statusId latitude longitude
| eventstats first(latitude) AS firstLat first(longitude) AS firstLong first(_time) AS firstTimeStamp by ccNum
| eval timeDiff=_time-firstTimeStamp
| eval latitude=if(isnull(latitude) AND timeDiff<=3600,firstLat,latitude)
| eval longitude=if(isnull(longitude) AND timeDiff<=3600,firstLong,longitude)

If a CC has a status of 10 and is missing lat and long, and that same CC had been used within the past hour (3600 seconds), then those previous lat and long values are copied in. The last 2 lines show that those are NOT copied in if more than an hour has transpired.

You can clean this up by removing the compute fields by adding this line to the end: | fields - firstLat firstLong firstTimeStamp timeDiff

If you have something existing, just tack on the search starting with eventstats. If you need more refinement, just post here and I'm sure someone can help out.

0 Karma

suchi01
New Member

Hi,
I want to check the latitude and longitude values and if the same is not present then update the event with latitude and longitude values as soon as the event occurs. Then reingest the updated event in the same index. Can you help me with this?

0 Karma

suchi01
New Member

I want to update the event as soon as it has occured

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...