What is the best way to update an index in Splunk to reflect a change in data pulled via a script from a database?

[venkat_d]

What is the best possible way to update an index in Splunk?

Here is my usecase:
Two lines are getting forwarded to Splunk
timestamp1 userid=foo status=active dept=hr
timestamp2 userid=bar status=active state=fin
...

Above values are coming from a script that reads the above data from mysql.
Now, my dashboard says user foo is in dept hr.
Now, I update the database to say user foo is in engineering dept.
I send this data to splunk again like this
timestamp3 userid=foo status=active dept=engineering

This causes issues in the dashboard that it lists user foo in both departments and department hr has an extra entry.

The question is how to update the indexes in splunk?
If it is not possible, how to solve the above problem - that i updated the database but have old+new entries in splunk.

Any suggestions?

[somesoni2]

You can't update indexed data in splunk. Based on your description of the problem, you want to track OR rather show current status/info of a User (and disregard old status/info)

Options: If you have very less data/number of users, they you can try following:

1) Configure a lookup table file, say user_status_lookup, with required fields like timestamp, userid, state, dept.
2) Configure a scheduled search , that will run at an interval (say 30 min or 1 hr) which take the latest data from the Splunk instance for a User and add (if new user)/update (if existing) in the lookup table. The search could be like this

index=yourIndex sourcetype=yourSourcetype  earliest=-1h@h latest=@d | dedup userid | table timestamp,userid,state,dept | append [|inputlookup user_status_lookup  | table timestamp,userid,state,dept ] | stats first(*) as * by userid | table timestamp,userid,state,dept | outputlookup user_status_lookup

This will take the latest status for user from the raw data, merge it with existing data entries from lookup and update the lookup with latest entries.

3) Update your dashboard to use this lookup data instead of indexed data.